From: | elein <elein(at)sbcglobal(dot)net> |
---|---|
To: | Markus Heinz <Markus(dot)Heinz(at)web(dot)de>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Storing sequence numbers for later use |
Date: | 2003-04-25 01:48:47 |
Message-ID: | 200304250154.h3P1sGFZ085442@pimout4-ext.prodigy.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)varlena(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
----------------------------------------------------------------------------------------
elein(at)varlena(dot)com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Fitzpatrick | 2003-04-25 01:58:07 | Revoke missing user |
Previous Message | elein | 2003-04-25 00:28:28 | Re: [SQL] rewriting values with before trigger |