pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednextval
9+ messages / 5 participants
[nested] [flat]
* nextval
@ 1998-05-27 14:22 Jerome ALET <alet@unice.fr>
0 siblings, 1 reply; 9+ messages in thread
From: Jerome ALET @ 1998-05-27 14:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] nextval
@ 1998-05-27 15:37 Patrice Hédé <patrice@idf.net>
parent: Jerome ALET <alet@unice.fr>
0 siblings, 1 reply; 9+ messages in thread
From: Patrice Hédé @ 1998-05-27 15:37 UTC (permalink / raw)
To: Jerome ALET <alet@unice.fr>; +Cc: pgsql-sql
> 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/ ----------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] nextval
@ 1998-05-28 08:39 Jerome ALET <alet@unice.fr>
parent: Patrice Hédé <patrice@idf.net>
0 siblings, 1 reply; 9+ messages in thread
From: Jerome ALET @ 1998-05-28 08:39 UTC (permalink / raw)
To: pgsql-sql; +Cc: patrice@idf.net; tony@cys.de
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] nextval
@ 1998-05-28 09:48 Patrice Hédé <patrice@idf.net>
parent: Jerome ALET <alet@unice.fr>
0 siblings, 1 reply; 9+ messages in thread
From: Patrice Hédé @ 1998-05-28 09:48 UTC (permalink / raw)
To: Jerome ALET <alet@unice.fr>; +Cc: pgsql-sql; tony@cys.de
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/ ----------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] nextval
@ 1998-05-28 10:01 Jerome ALET <alet@unice.fr>
parent: Patrice Hédé <patrice@idf.net>
0 siblings, 1 reply; 9+ messages in thread
From: Jerome ALET @ 1998-05-28 10:01 UTC (permalink / raw)
To: patrice@idf.net; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] nextval
@ 1998-05-28 10:38 Marin D <marin@cybernet.bg>
parent: Jerome ALET <alet@unice.fr>
0 siblings, 0 replies; 9+ messages in thread
From: Marin D @ 1998-05-28 10:38 UTC (permalink / raw)
To: Jerome ALET <alet@unice.fr>; +Cc: patrice@idf.net; pgsql-sql
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? =-
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: [SQL] nextval
@ 1998-05-28 14:49 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jackson, DeJuan @ 1998-05-28 14:49 UTC (permalink / raw)
To: Marin D <marin@cybernet.bg>; Jerome ALET <alet@unice.fr>; +Cc: patrice@idf.net; pgsql-sql
> 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.
>
I wouldn't consider the results that Jerome is getting to be a bug.
What is happening is that the RULE is inserting the new value into every
row each time he inserts a new row. A test to verify that would be to
insert two rows then a select and see if the behavior is consistent with
your previous assumptions.
Now with that said the solution would be to somehow limit your insert
rule to the currently inserted row. I haven't played with rules enough
(read: at all), so I can't give you the sql, but I bet it looks like a
WHERE clause.
Hope this helps,
-DEJ
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] nextval
@ 1998-05-28 14:55 Jerome ALET <alet@unice.fr>
parent: Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 9+ messages in thread
From: Jerome ALET @ 1998-05-28 14:55 UTC (permalink / raw)
To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: pgsql-sql
Jackson, DeJuan wrote:
> I wouldn't consider the results that Jerome is getting to be a bug.
> What is happening is that the RULE is inserting the new value into every
> row each time he inserts a new row. A test to verify that would be to
> insert two rows then a select and see if the behavior is consistent with
> your previous assumptions.
OK, I'll try this tomorrow. I haven't tested this TEST :)
but when I read the doc I understand (I'm not English nor American)
that in the manual page:
create rule rule_name
as on event
to object [where clause]
do [instead]
[action | nothing | [actions...]]
the WHERE is optional and that in:
new or current can appear instead of an instance
variable whenever an instance variable is permissi-
ble in SQL.
The semantics of a rule is that at the time an individual
instance is accessed, updated, inserted or deleted, there
is a current instance (for retrieves, updates and deletes)
and a new instance (for updates and appends). If the
event specified in the on clause and the condition speci-
fied in the where clause are true for the current
instance, then the action part of the rule is executed.
First, however, values from fields in the current instance
and/or the new instance are substituted for:
current.attribute-name
new.attribute-name
the rule should apply one tuple at a time.
maybe I was misunderstanding the manual page.
thanks all
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* nextval...
@ 2002-08-28 16:51 friedrich nietzsche <nietzsche_psql@yahoo.it>
0 siblings, 0 replies; 9+ messages in thread
From: friedrich nietzsche @ 2002-08-28 16:51 UTC (permalink / raw)
To: pgsql-sql
Ok , now I believe it :)
thanks to all.
danilo
______________________________________________________________________
Yahoo! Musica: notizie, recensioni, classifiche, speciali multimediali
http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2002-08-28 16:51 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-05-27 14:22 nextval Jerome ALET <alet@unice.fr>
1998-05-27 15:37 ` Patrice Hédé <patrice@idf.net>
1998-05-28 08:39 ` Jerome ALET <alet@unice.fr>
1998-05-28 09:48 ` Patrice Hédé <patrice@idf.net>
1998-05-28 10:01 ` Jerome ALET <alet@unice.fr>
1998-05-28 10:38 ` Marin D <marin@cybernet.bg>
1998-05-28 14:49 RE: [SQL] nextval Jackson, DeJuan <djackson@cpsgroup.com>
1998-05-28 14:55 ` Jerome ALET <alet@unice.fr>
2002-08-28 16:51 nextval... friedrich nietzsche <nietzsche_psql@yahoo.it>
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