Storing sequence numbers for later use - Mailing list pgsql-general

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);