From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Daniel Ordobas Bortolas <bortolas@inf.UFSM.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: simple problem
Date: Sat, 16 Mar 2002 18:23:33 +0900
Message-ID: <20020316141353.69D4.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <20020306150845.39034.qmail@inf.ufsm.br>
References: <20020306150845.39034.qmail@inf.ufsm.br>
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)
select case when (select count(*) from loco) < 3
then new.id_loco
else null
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rk73@sea.plala.or.jp, bortolas@inf.UFSM.br
Subject: Re: simple problem
In-Reply-To: <20020316141353.69D4.RK73@sea.plala.or.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox