Thread: Storing sequence numbers for later use
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);
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
Of course you can do this. currval is a function, though, and so it needs to be executed in a selection. create table foo ( one serial PRIMARY KEY , foo text ); create table bar ( one integer references foo (one), bar text ); create table boo ( one integer references foo (one), boo text ); drop trigger foobarboo on foo; create or replace function footrig () returns TRIGGER as ' DECLARE myseq integer; BEGIN select into myseq currval( ''foo_one_seq''::text ); insert into bar (one, bar) values (myseq, NEW.foo || ''bar'' ); insert into boo (one, boo) values (myseq, NEW.foo || ''boo'' ); RETURN NEW; END; ' language 'plpgsql'; create trigger foobarboo after insert on foo for each row execute procedure footrig(); elein@varlena.com On Friday 18 April 2003 03:05, 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 ? > > > 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); > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- ---------------------------------------------------------------------------------------- elein@varlena.com Database Consulting www.varlena.com I have always depended on the [QA] of strangers.