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

> It is possible to assign the result of an function call to a script
> local variable in psql ?

It is kind of ugly, but try this:

> INSERT INTO Address (city) values ('Berlin');

\set ADD1 = :LASTOID

> INSERT INTO Address (city) values ('Paris');

\set ADD2 = :LASTOID

>Insert into Address select
> INSERT INTO Invoice (payeeAdress, invoiceeAdress, grossTotal) values
> (pa_id, ia_id, 100.0);

Now you can write your insert as follows:

   INSERT INTO Invoice (payeeAdress, invoiceeAdress, grossTotal)
   SELECT A.ID, B.ID, 100.0 from Address AS A, Address AS B
   where A.OID = :ADD1 and B.OID = :ADD2;

For performance reasons, you will probably need to build this index:

   CREATE INDEX add_oids on Address(OID);
--
Mike Nolan