From: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
---|---|
To: | Markus Heinz <Markus(dot)Heinz(at)web(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing sequence numbers for later use |
Date: | 2003-04-18 10:46:42 |
Message-ID: | Pine.LNX.4.21.0304181143280.2396-100000@ponder.fairway2k.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Welche | 2003-04-18 13:19:05 | explain ? |
Previous Message | Markus Heinz | 2003-04-18 10:05:18 | Storing sequence numbers for later use |