Re: Storing sequence numbers for later use

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