From: Jerome ALET <alet@unice.fr>
To: pgsql-sql@postgresql.org
Cc: patrice@idf.net
Cc: tony@cys.de
Subject: Re: [SQL] nextval
Date: Thu, 28 May 1998 08:39:44 +0000
Message-ID: <356D22D0.7EE91817@unice.fr> (raw)
References: <Pine.LNX.3.96.980527173525.28094A-100000@paris.ivo.fr>
Patrice Hédé wrote:
>
> > CREATE SEQUENCE seq_slogans INCREMENT 1 MINVALUE 1 START 1;
> > CREATE
> >
> > CREATE RULE rule_i_slogans AS ON INSERT TO slogans DO UPDATE NEW SET
> > numero_slogan = nextval(seq_slogans) , points_slogan = 10;
> > ERROR: attribute 'seq_slogans' not found
>
> Maybe I've overlooked something, but you seem to have forgotten to put
> quotes around the sequence name as :
>
> SELECT nextval('seq_slogans');
OK, thanks to all.
I was wrong because I had forgotten the '' but it still doesn't work
correctly:
First I had to remove PRIMARY KEY and NOT NULL because when I did the
INSERT it complained about inserting a null value. This is because the
NOT NULL check is automatically done before the RULE is executed. Is it
a bug or a feature ? Maybe we should be able to choose the order but
this should not exist in ANSI SQL...
Second, look at the results of a similar RULE:
CREATE TABLE auteurs (numero_auteur INT4, nom_auteur TEXT, prenom_auteur
TEXT, email_auteur TEXT);
CREATE SEQUENCE seq_auteurs INCREMENT 1 MINVALUE 1 START 1;
CREATE
CREATE RULE rule_i_auteurs AS ON INSERT TO auteurs DO UPDATE NEW SET
numero_auteur = nextval('seq_auteurs');
CREATE
then:
slogbase=> insert into auteurs (nom_auteur) values ('Jerome');
UPDATE 1
slogbase=> select * from auteurs;
numero_auteur|nom_auteur|prenom_auteur|email_auteur
-------------+----------+-------------+------------
1|Jerome | |
(1 row)
this result is correct but when I continue:
slogbase=> insert into auteurs (nom_auteur) values ('Alfred');
UPDATE 2
I think, but I'm not sure, that now the inserted line has a
numero_auteur which value is 2 ! Let's verify:
slogbase=> select * from auteurs;
numero_auteur|nom_auteur|prenom_auteur|email_auteur
-------------+----------+-------------+------------
2|Jerome | |
3|Alfred | |
(2 rows)
another try:
slogbase=> insert into auteurs (nom_auteur) values ('Albert');
UPDATE 3
slogbase=> select * from auteurs;
numero_auteur|nom_auteur|prenom_auteur|email_auteur
-------------+----------+-------------+------------
4|Jerome | |
5|Alfred | |
6|Albert | |
(3 rows)
Interesting, isn't it ?
The 'AS ON INSERT' section of my rule seems to be interpreted like:
'AS ON (SELECT OR INSERT)'
Someone has got any idea ?
Jerome ALET - alet@unice.fr - http://cortex.unice.fr/~jerome
Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30
28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE
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: alet@unice.fr, patrice@idf.net, tony@cys.de
Subject: Re: [SQL] nextval
In-Reply-To: <356D22D0.7EE91817@unice.fr>
* 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