Thread
-
nextval
Jerome Alet <alet@unice.fr> — 1998-05-27T14:22:31Z
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
-
Re: [SQL] nextval
Anton Stckl <tony@cys.de> — 1998-05-27T15:33:46Z
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------- -
Re: [SQL] nextval
Patrice Hédé <patrice@idf.net> — 1998-05-27T15:37:32Z
> 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/ ---------------------------------- -
Re: [SQL] nextval
Jerome Alet <alet@unice.fr> — 1998-05-28T08:39:44Z
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 -
Re: [SQL] nextval
Patrice Hédé <patrice@idf.net> — 1998-05-28T09:48:08Z
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/ ---------------------------------- -
Re: [SQL] nextval
Jerome Alet <alet@unice.fr> — 1998-05-28T10:01:07Z
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 -
Re: [SQL] nextval
Marin D <marin@cybernet.bg> — 1998-05-28T10:38:26Z
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? =-