On Fri, 13 Jul 2001, Alexey V. Neyman wrote:
> Hello there!
> [Please Cc: me in followups.]
> I tried the following:
> CREATE TABLE a (
> int4 id
> CREATE TABLE b (
> int4 id
> CHECK (id = ANY(SELECT a.id FROM a))
> Tables are created ok, checking with '\d table' confirms it. But when I
> try to insert into table b, e.g.:
> INSERT INTO b (id)
> VALUES (0);
> I get:
> ERROR: ExecEvalExpr: unknown expression type 108
> Of course, the tuple is not inserted.
> As quick dig of code showed, type 108 is T_SubLink which is created for
> ANY() subselect, and ExecEvalExpr() function does not handle this type of
> node. Is it intentional or a bug?
It's unimplemented, and really should fail at create time (I'm not
sure if it does in 7.1). IIRC, it's only required at FULL SQL92 level
(intermediate level has a no subqueries limitation). The reason is
that the constraint you are making as part of b also constrains
table a and it's not entirely trivial to support complicated subquery
constraints within the current system.
As a workaround for now, you'll probably have to use triggers on a and
b to do the check. (before insert trigger on b and a delete/update
trigger on a).
In response to
pgsql-bugs by date
|Next:||From: Peter Eisentraut||Date: 2001-07-13 18:29:39|
|Subject: Re: bug report: tuple is too big|
|Previous:||From: Lamar Owen||Date: 2001-07-13 18:19:58|
|Subject: Re: Upgrade to 7.1.2 problem|
pgsql-sql by date
|Next:||From: Joe Conway||Date: 2001-07-13 18:57:19|
|Subject: Re: How can we match a condition among 2 diff. tables?|
|Previous:||From: Josh Berkus||Date: 2001-07-13 17:44:39|
|Subject: Re: Date Validation?|