Thread: nextval
Hello all, I've read the different solutions about nextval in the digest but haven't tried any for now. Could someone explain me why the following doesn't work: CREATE TABLE slogans (numero_slogan INT4 PRIMARY KEY NOT NULL, date_slogan DATE NOT NULL, points_slogans INT4); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index slogans_pkey for table slogans CREATE 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 if I replace the "nextval(seq_slogans)" with a constant in the rule it works. I thought it was an elegant solution. If someone has got an idea .... Thanks. 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
Jerome ALET wrote:
> 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
CREATE RULE rule_i_slogans AS ON INSERT TO slogans DO UPDATE NEW SET
numero_slogan = nextval('seq_slogans') , points_slogan = 10;
^ ^
have fun, Tony
--
----------C-Y-B-E-R-S-O-L-U-T-I-O-N-S----------------
Anton Stöckl mailto:tony@cys.de
CyberSolutions GmbH http://www.cys.de
Frankfurter Ring 193A Phone +49 89 32369223
80807 Muenchen Fax +49 89 32369220
------W-E----M-A-K-E----I-T----P-O-S-S-I-B-L-E-------
> 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
>
> if I replace the "nextval(seq_slogans)" with a constant in the rule it
> works.
Maybe I've overlooked something, but you seem to have forgotten to put
quotes around the sequence name as :
SELECT nextval('seq_slogans');
Hope this helps.
--
Patrice HÉDÉ --------------------------------- patrice@idf.net -----
... Looking for a job in Iceland or in Norway !
Ingénieur informaticien - Computer engineer - Tölvufræðingur
----- http://www.idf.net/patrice/ ----------------------------------
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
On Thu, 28 May 1998, Jerome ALET wrote:
> 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
Maybe you should consider doing it with a default value as :
CREATE TABLE auteurs (numero_auteur int4 default nextval('seq_auteurs'),
nom_auteur text,
prenom_auteur text,
email_auteur text
);
and then, you don't need to create a rule.
then insert like this :
INSERT INTO auteurs( nom_auteur ) VALUES ( 'Jerome' );
should give you what you want, if what you really need is a unique ID.
Hope it helps :)
Patrice
--
Patrice HÉDÉ --------------------------------- patrice@idf.net -----
... Looking for a job in Iceland or in Norway !
Ingénieur informaticien - Computer engineer - Tölvufræðingur
----- http://www.idf.net/patrice/ ----------------------------------
Patrice Hédé wrote:
> Maybe you should consider doing it with a default value as :
>
> CREATE TABLE auteurs (numero_auteur int4 default nextval('seq_auteurs'),
> nom_auteur text,
> prenom_auteur text,
> email_auteur text
> );
>
> and then, you don't need to create a rule.
>
Yes, I already know that solution, but I wondered why mine was bad (the
forgotten '') and finally I think I've found a (maybe already known) bug
in CREATE RULE (look at the results in my previous message).
Thanks.
bye,
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
On Thu, 28 May 1998, Jerome ALET wrote:
>
> Yes, I already know that solution, but I wondered why mine was bad (the
> forgotten '') and finally I think I've found a (maybe already known) bug
> in CREATE RULE (look at the results in my previous message).
>
Who hasn't already found one? :)
the man page for create_rule clearly says
DESCRIPTION
The current rule system implementation is very brittle and
is unstable. Users are discouraged from using rules at
this time.
Best regards
Marin
-= Why do we need gates in a world without fences? =-