Storing sequence numbers for later use

From: Markus Heinz <Markus(dot)Heinz(at)web(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Storing sequence numbers for later use
Date: 2003-04-18 10:05:18
Message-ID: 3E9FCDDE.3080802@web.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nigel J. Andrews 2003-04-18 10:46:42 Re: Storing sequence numbers for later use
Previous Message Adam Witney 2003-04-18 09:24:44 Re: Where I can find PL/pgSQL and Trigger Examples?