Thread: simple problem

simple problem

From

"Daniel Ordobas Bortolas"

Date:

06 March 2002, 13:32:33

Hello!
I am having problems creating a table and i'd like some help.

-->create table loco( id_loco int4 not null, primary key (id_loco), check(
(select count(*) from loco ) < 3 ) );

I guess the statment is right, but when:
->insert into loco values(1);

the result is: ERROR:  ExecEvalExpr: unknown expression type 108

How can I fix this problem?
Thanks.
Daniel Bortolas.

Re: simple problem

From

Stephan Szabo

Date:

06 March 2002, 17:10:40

On Wed, 6 Mar 2002, Daniel Ordobas Bortolas wrote:

>
> Hello!
> I am having problems creating a table and i'd like some help.
>
> -->create table loco( id_loco int4 not null, primary key (id_loco), check(
> (select count(*) from loco ) < 3 ) );

IIRC, check constraints with subselects are not currently supported.
You can make triggers that do the check for you.

Re: simple problem

From

Tom Lane

Date:

06 March 2002, 18:10:09

"Daniel Ordobas Bortolas" <bortolas@inf.UFSM.br> writes:
> -->create table loco( id_loco int4 not null, primary key (id_loco), check(
> (select count(*) from loco ) < 3 ) );
> ->insert into loco values(1);
> the result is: ERROR:  ExecEvalExpr: unknown expression type 108

Subselects aren't supported in check constraints (and if you were
running a version newer than 7.0.*, it would've told you so ...)

Possibly you could do what you want with a trigger function.
        regards, tom lane

Re: simple problem

From

"PG Explorer"

Date:

06 March 2002, 18:14:47

try without the "  on ''autor''

where m_name = autor;

http://www.pgexplorer.com
PostgreSQL GUI Tool

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daniel Ordobas Bortolas" <bortolas@inf.UFSM.br>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 06, 2002 8:20 PM
Subject: Re: [SQL] simple problem


> On Wed, 6 Mar 2002, Daniel Ordobas Bortolas wrote:
>
> >
> > Hello!
> > I am having problems creating a table and i'd like some help.
> >
> > -->create table loco( id_loco int4 not null, primary key (id_loco),
check(
> > (select count(*) from loco ) < 3 ) );
>
> IIRC, check constraints with subselects are not currently supported.
> You can make triggers that do the check for you.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: simple problem

From

"Hano de la Rouviere"

Date:

13 March 2002, 11:24:11

try without the "  on ''autor''

where m_name = autor;

http://www.pgexplorer.com
PostgreSQL GUI Tool

----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daniel Ordobas Bortolas" <bortolas@inf.UFSM.br>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 06, 2002 8:20 PM
Subject: Re: [SQL] simple problem


> On Wed, 6 Mar 2002, Daniel Ordobas Bortolas wrote:
>
> >
> > Hello!
> > I am having problems creating a table and i'd like some help.
> >
> > -->create table loco( id_loco int4 not null, primary key (id_loco),
check(
> > (select count(*) from loco ) < 3 ) );
>
> IIRC, check constraints with subselects are not currently supported.
> You can make triggers that do the check for you.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: simple problem

From

Masaru Sugawara

Date:

16 March 2002, 07:22:09

On Wed, 06 Mar 2002 15:08:45 GMT
"Daniel Ordobas Bortolas" <bortolas@inf.UFSM.br> wrote:

> Hello!
> I am having problems creating a table and i'd like some help.
> 
> -->create table loco( id_loco int4 not null, primary key (id_loco), check(
> (select count(*) from loco ) < 3 ) );
> 
> I guess the statment is right, but when:
> ->insert into loco values(1);
> 
> the result is: ERROR:  ExecEvalExpr: unknown expression type 108
> 


By taking advantage of the fact that COUNT() aggregation can be used 
in RULEs(at least in v7.2) and that NULL can't be inserted into columns
defined with NOT NULL , you'll be able to set the limit on  the number of
the rows.  you, however, probably need to take care not to misunderstand the
meaning in the returned error message --  whether it shows the original
meaning or the limit: COUNT() < n.  But I would think that  using TRIGGERs
seems common at the moment. 


create table loco( id_loco int4 not null, primary key (id_loco));
create view loco_view as select id_loco from loco;

create rule rule_loco_view as     on insert to loco_view      do instead insert into loco(id_loco)
selectcase when  (select count(*) from loco) < 3                           then new.id_loco
elsenull                        end ;
 


renew=> insert into loco_view values(1);
INSERT 1712397 1
renew=> insert into loco_view values(1);
ERROR:  Cannot insert a duplicate key into unique index loco_pkey
renew=> insert into loco_view values(2);
INSERT 1712399 1
renew=> insert into loco_view values(null);
ERROR:  ExecAppend: Fail to add null value in not null attribute id_loco
renew=> insert into loco_view values(3);
INSERT 1712400 1
renew=> insert into loco_view values(3);
ERROR:  ExecAppend: Fail to add null value in not null attribute id_loco
renew=>
renew=> select * from loco;id_loco
---------      1      2      3
(3 rows)


Regards,
Masaru Sugawara