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