Thread: 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

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:

> 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