remembering multiple currval() inside a RULE

From: Nabil Sayegh <postgresql(at)e-trolley(dot)de>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: remembering multiple currval() inside a RULE
Date: 2005-09-01 18:29:59
Message-ID: 200509012029.59291.postgresql@e-trolley.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi list,

I need to setup a RULE for INSERTing into a VIEW.
The rule has to insert several times into the same table, and I need to
remember the currval()s for another INSERT.

Are there any variables or some other state that I could abuse to safe the
values?
I already tried to CREATE TEMP TABLE only for inserting the values there, but
that doesn't seem to be allowed inside RULEs.
If there is no other

pseudocode:

CREATE TABLE bar (id_bar SERIAL PRIMARY KEY, bar int);
CREATE TABLE bar_bar (id1_bar int, id2_bar int);
CREATE VIEW
foo
AS
SELECT
bar1.bar AS bar1,
bar2.bar AS bar2
FROM
bar_bar,
bar bar1,
bar bar2
WHERE
bar_bar.id1_bar=bar1.id_bar AND
bar_bar.id2_bar=bar2.id_bar;

CREATE RULE
foo_ins
AS ON INSERT TO
foo
DO INSTEAD (
INSERT INTO bar (bar) VALUES (NEW.bar1);
INSERT INTO bar (bar) VALUES (NEW.bar2);
INSERT INTO bar_bar (id1_bar, id2_bar) VALUES (
currval_of_1st_insert('public.bar_id_bar_seq'::text),
currval_of_2nd_insert('public.bar_id_bar_seq'::text)
)
);

TIA
--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : www.e-trolley.de

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message operationsengineer1 2005-09-01 22:31:11 Linux File Systems and Postgres (or dbs in general)
Previous Message Michael Fuhr 2005-09-01 18:00:41 Re: reference to 'as' field