some problems
Ahoy!
Hi!
I have the following table:
create table address
(
id int4 primary key,
owner int4 not null,
content varchar(80) not null
CHECK (owner in (select id from id where t_name in ('org',
'person',
'router', 'dns')))
);
create trigger delete_id before delete
on address for each row execute procedure delete_id();
create trigger set_id before insert
on address for each row execute procedure set_id();
Everything seems OK, but when I type this:
ripv=# insert into address values(0, 18, 'Szeged');
I get an error:
ERROR: ExecEvalExpr: unknown expression type 108
But these queries work:
ripv=# select 18 in (select id from id where t_name in ('org', 'person',
'router', 'dns'));
?column?
----------
t
(1 row)
ripv=# select id from id where t_name in ('org', 'person', 'router',
'dns');
id
----
5
18
(2 rows)
ripv=#
Any comment?
--
Roodie ICQ: 53623985
Linux, C++, VB, SQL, PhotoShop, Lightwave
Ars Magica, AD&D, Mutant Chronicles
Roodie <roodie@morahalom.hu> writes:
ERROR: ExecEvalExpr: unknown expression type 108
Sub-selects in constraint expressions don't work at the moment :-(.
An easy workaround is to put the sub-select in a function and call
the function from your constraint.
regards, tom lane
Yes, you cannot place subqueries into check constraints
at this time. It's probably possible to fix the immediate
problem (the unknown type stuff), but that doesn't actually
make the constraints work the way the SQL spec requires*,
so even if it gets fixed I'd suggest not using it until it
works completely. I'd suggest doing a before insert/update
trigger instead.
* - Constraints must always be satisfied, and so a constraint
with a subquery is actually constraining all tables mentioned
as well as the one that the constraint is named on. So,
in your case, you would be unable to delete a row in id
such that the constraint doesn't hold. Fortunately
constraints with subqueries appears to be a Full SQL
feature.
Stephan Szabo
sszabo@bigpanda.com
On Wed, 23 Aug 2000, Roodie wrote:
Show quoted text
Ahoy!
Hi!
I have the following table:create table address
(
id int4 primary key,
owner int4 not null,
content varchar(80) not null
CHECK (owner in (select id from id where t_name in ('org',
'person',
'router', 'dns')))
);
create trigger delete_id before delete
on address for each row execute procedure delete_id();
create trigger set_id before insert
on address for each row execute procedure set_id();Everything seems OK, but when I type this:
ripv=# insert into address values(0, 18, 'Szeged');
I get an error:
ERROR: ExecEvalExpr: unknown expression type 108
But these queries work:
ripv=# select 18 in (select id from id where t_name in ('org', 'person',
'router', 'dns'));
?column?
----------
t
(1 row)ripv=# select id from id where t_name in ('org', 'person', 'router',
'dns');
id
----
5
18
(2 rows)ripv=#
Any comment?
--
Roodie ICQ: 53623985
Linux, C++, VB, SQL, PhotoShop, Lightwave
Ars Magica, AD&D, Mutant Chronicles