creating functions with variable argument lists

From: Marc Evans <Marc(at)SoftwareHackery(dot)Com>
To: pgsql-general(at)postgresql(dot)org
Subject: creating functions with variable argument lists
Date: 2006-12-08 15:16:34
Message-ID: 20061208100411.S77630@me.softwarehackery.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi -

I am trying to make use of table partitions. In doing so I would like to
use a rule to call a functioning which inserts the data into the proper
partition. To do so, I believe that I need to find a way to opaquely pass
NEW from the rule to a function which then passes it to INSERT. (Well, I
could spell out all of the columns in the table as arguments to the
function, but that is not as maintainable, e.g. every time the table
columns change, so to the function and rule change.)

I am not finding any way to do this in the proceedural languages. That
said, I would happily believe that I am just missing something, and am
hoping that someone on this list has already figured out an answer.

For consideration, here is an example:

create table foobars (
id bigserial,
created_at timestamp not null,
name text
);

create table foobars_200612 (
check (created_at >= timestamp '2006-12-01 00:00:00' and created_at < timestamp '2007-01-01 00:00:00')
) inherits (foobars);

create table foobars_200701 (
check (created_at >= timestamp '2007-01-01 00:00:00' and created_at < timestamp '2007-02-01 00:00:00')
) inherits (foobars);

-- Warning, pseudo code follows (e.g. NEW):
create or replace function foo_insert(NEW) returns void as $$
begin
execute 'insert into foobars_' ||
(select extract(year from $1) || extract(month from $1)) ||
' values (' || NEW || ')';
end;
$$ language plpgsql;

create rule foobars_insert as on insert to foobars
do instead select foo_insert(NEW);

The key to my success for the above is to find a way for NEW to be used
something like the pseudo code shown. Suggestions?

- Marc

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-12-08 15:21:30 Re: Excluding schema from backup
Previous Message Edoardo Panfili 2006-12-08 15:16:03 strange (maybe) behaviour of table lock