Thread: remembering multiple currval() inside a RULE

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

hi all, is there an optimal linux file system choice
for a system that will be used as a db server?

i seem to recall having read somewhere that there were
at least two choices (one being journaling) and one
set up was best for file management (desktop) and
another was best for database management.

tia...

ps - yeah, i know.  i recently joined
linuxquestions.org so they will be working me through
most of the linux issues going forward.  ;-)  i think
this is question is relevant to novice pgsql users, though.



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

On Thu, 1 Sep 2005, operationsengineer1@yahoo.com wrote:

> hi all, is there an optimal linux file system choice
> for a system that will be used as a db server?
>
> i seem to recall having read somewhere that there were
> at least two choices (one being journaling) and one
> set up was best for file management (desktop) and
> another was best for database management.

I am generally biased towards SGI's XFS. Otherwise JFS and Reiserfs may
also be good candidates.


--
Aly S.P Dharshi
aly.dharshi@telus.net

      "A good speech is like a good dress
       that's short enough to be interesting
       and long enough to cover the subject"

OE,

> hi all, is there an optimal linux file system choice
> for a system that will be used as a db server?

Well, XFS and JFS offer some advantages.  However, I think support for them
under Linux is still flaky.

For Reiser and Ext3, it's about neck-and-neck; Reiser does marginally better
with lots of small tables, otherwise worse.   In either case, the important
thing is to remember to set the lowest level of journalling
( data=writeback ) otherwise you take a huge performance hit for the
journalling.


--
Josh Berkus
Aglio Database Solutions
San Francisco

Hello All,

> Well, XFS and JFS offer some advantages.  However, I think support for them
> under Linux is still flaky.

    Under OSes from RedHat yes. SuSE, Ubuntu and friends have way better support
for it.

    Aly.
--
Aly S.P Dharshi
aly.dharshi@telus.net

     "A good speech is like a good dress
      that's short enough to be interesting
      and long enough to cover the subject"