Message-Id: <EB0D5597-D86F-4528-B615-074B5A802132@myemma.com>
From: Erik Jones <erik@myemma.com>
To: SQL Postgresql List <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Apple Message framework v919.2)
Subject: Dynamic sql and variable record types
Date: Thu, 20 Mar 2008 17:28:18 -0500

Hi, I've been working on a generic date partitioning system and I =20
think I've reached something that can't be done, but I thought I'd =20
post a question to the masses in the hope that I'm missing something.  =20=

The basic idea of what I'm doing is some userland scripts that will =20
accept a table name argument along with the name of a date/timestamp =20
attribute to partition on and create partitions for that table along =20
with the appropriate trigger and trigger function.  The part I'm =20
having trouble with is the trigger function.

What I'm done for that is to create a template file that my scripts =20
read in and substitute the table column names wherever necessary, then =20=

run the results through the db to create the functions.  The problem =20
is that for the function to be generic it needs to be able to work =20
with different record/row types.  Here's the template for function =20
(not working, which I'll discuss below):

CREATE OR REPLACE FUNCTION %s_ins_func(op text, rec %s)
     RETURNS boolean AS $$
DECLARE
     partition varchar;
     name_parts varchar[];
     upper_dim integer;
BEGIN
     FOR partition IN
         SELECT relname
         FROM pg_class
         WHERE relname ~ ('^%s_[0-9]{8}_[0-9]{8}$')
     LOOP
         name_parts :=3D string_to_array(partition, '_');
         upper_dim :=3D array_upper(name_parts, 1);
         IF rec.%s >=3D name_parts[upper_dim-1]::timestamp AND rec.%s < =20=

name_parts[upper_dim] THEN
             IF op =3D 'INSERT' THEN
                 EXECUTE 'INSERT INTO %s_' || name_parts[upper_dim-1] =20=

|| '_' ||
                             name_parts[upper_dim] || ' VALUES ' || =20
rec || ';'; -- the problem is here with rec
                 RETURN TRUE;
             END IF;
         END IF;
     END LOOP;
     RETURN FALSE;
END;
$$ language plpgsql;

The userland scripts substitute the table and column names for the %s =20=

escapes where appropriate.  What the function actually does is to us =20
the parent table's name to find all of the child partitions which are =20=

name like some_table_20080101_20080201, split out the dates from those =20=

to determine which table the insert needs to be redirected to.  That =20
works fine.  The problem is that since I have to dynamically generate =20=

the destination table name I have to use EXECUTE for the INSERT =20
statement.  But, I can't see how to use a record in query passed to =20
EXECUTE.  Am I right in thinking (now) that this can't be done?

Erik Jones

DBA | Emma=AE
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com