Обсуждение: Storing sequence numbers for later use

Hi all,

i'm trying to translate a small MySQL script to Postgresql.
Unfortunatly my DB-Schema contains some Tables that contain more than
one Reference (Foreign Key , see below) to another table.
Therefore it is not possible to use currval('table_idcol_seq') function
call as a direct parameter of an INSERT statement.
It is possible to assign the result of an function call to a script
local variable in psql ?


thanks in advance

Markus



CREATE TABLE Address(       id SERIAL,
                            city VARCHAR(255),
                            PRIMARY KEY (id)
                        );

CREATE TABLE Invoice(   id SERIAL,
                        payeeAddress_id INT,
                        invoiceeAddress_id INT,
                        grossTotal NUMERIC(15,4),
                        FOREIGN KEY (payeeAddress_id) REFERENCES
Address(id),
                        FOREIGN KEY (invoiceeAddress_id) REFERENCES
Address(id),
                        PRIMARY KEY (id)
                    );

INSERT INTO Address (city) values ('Berlin');
pa_id := currval('address_id_seq');
INSERT INTO Address (city) values ('Paris');
ia_id := currval('address_id_seq');
INSERT INTO Invoice (payeeAdress, invoiceeAdress, grossTotal) values
(pa_id, ia_id, 100.0);

On Fri, 18 Apr 2003, Markus Heinz wrote:

> Hi all,
>
> i'm trying to translate a small MySQL script to Postgresql.
> Unfortunatly my DB-Schema contains some Tables that contain more than
> one Reference (Foreign Key , see below) to another table.
> Therefore it is not possible to use currval('table_idcol_seq') function
> call as a direct parameter of an INSERT statement.
> It is possible to assign the result of an function call to a script
> local variable in psql ?

Quick answer: no.
Longer answer: no, I don't think so.

Can you perhaps your script to use a function that does the inserts for
you? You'd also be able to do more useful stuff like checking you're not
inserting duplicates in to Address before mearily using the currval().

>
>
> thanks in advance
>
> Markus
>
>
>
> CREATE TABLE Address(       id SERIAL,
>                             city VARCHAR(255),
>                             PRIMARY KEY (id)
>                         );
>
> CREATE TABLE Invoice(   id SERIAL,
>                         payeeAddress_id INT,
>                         invoiceeAddress_id INT,
>                         grossTotal NUMERIC(15,4),
>                         FOREIGN KEY (payeeAddress_id) REFERENCES
> Address(id),
>                         FOREIGN KEY (invoiceeAddress_id) REFERENCES
> Address(id),
>                         PRIMARY KEY (id)
>                     );
>
> INSERT INTO Address (city) values ('Berlin');
> pa_id := currval('address_id_seq');
> INSERT INTO Address (city) values ('Paris');
> ia_id := currval('address_id_seq');
> INSERT INTO Invoice (payeeAdress, invoiceeAdress, grossTotal) values
> (pa_id, ia_id, 100.0);
>


--
Nigel J. Andrews

Of course you can do this.  currval is a function, though,
and so it needs to be executed in a selection.

create table foo ( one serial PRIMARY KEY , foo text );
create table bar ( one integer references foo (one), bar text );
create table boo ( one integer references foo (one), boo text );

drop trigger foobarboo on foo;
create or replace function footrig ()
returns TRIGGER as
'
DECLARE
   myseq integer;
BEGIN
   select into myseq currval( ''foo_one_seq''::text );
   insert into bar (one, bar) values (myseq, NEW.foo || ''bar'' );
   insert into boo (one, boo) values (myseq, NEW.foo || ''boo'' );
   RETURN NEW;
END;

' language 'plpgsql';

create trigger foobarboo after insert on foo for each row
execute procedure footrig();

elein@varlena.com



On Friday 18 April 2003 03:05, Markus Heinz wrote:
> Hi all,
>
> i'm trying to translate a small MySQL script to Postgresql.
> Unfortunatly my DB-Schema contains some Tables that contain more than
> one Reference (Foreign Key , see below) to another table.
> Therefore it is not possible to use currval('table_idcol_seq') function
> call as a direct parameter of an INSERT statement.
> It is possible to assign the result of an function call to a script
> local variable in psql ?
>
>
> thanks in advance
>
> Markus
>
>
>
> CREATE TABLE Address(       id SERIAL,
>                             city VARCHAR(255),
>                             PRIMARY KEY (id)
>                         );
>
> CREATE TABLE Invoice(   id SERIAL,
>                         payeeAddress_id INT,
>                         invoiceeAddress_id INT,
>                         grossTotal NUMERIC(15,4),
>                         FOREIGN KEY (payeeAddress_id) REFERENCES
> Address(id),
>                         FOREIGN KEY (invoiceeAddress_id) REFERENCES
> Address(id),
>                         PRIMARY KEY (id)
>                     );
>
> INSERT INTO Address (city) values ('Berlin');
> pa_id := currval('address_id_seq');
> INSERT INTO Address (city) values ('Paris');
> ia_id := currval('address_id_seq');
> INSERT INTO Invoice (payeeAdress, invoiceeAdress, grossTotal) values
> (pa_id, ia_id, 100.0);
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
----------------------------------------------------------------------------------------
elein@varlena.com     Database Consulting     www.varlena.com
              I have always depended on the [QA] of strangers.