Cc: SQL Postgresql List <pgsql-sql@postgresql.org>
Message-Id: <E2DCA422-9214-40AB-8354-CC4FA6050601@myemma.com>
From: Erik Jones <erik@myemma.com>
To: Erik Jones <erik@myemma.com>
In-Reply-To: <EB0D5597-D86F-4528-B615-074B5A802132@myemma.com>
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: SOLVED - Re: Dynamic sql and variable record types
Date: Fri, 21 Mar 2008 11:21:12 -0500
References: <EB0D5597-D86F-4528-B615-074B5A802132@myemma.com>


On Mar 20, 2008, at 5:28 PM, Erik Jones wrote:

> 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 =20
> something.  The basic idea of what I'm doing is some userland =20
> scripts that will accept a table name argument along with the name =20
> of a date/timestamp attribute to partition on and create partitions =20=

> for that table along with the appropriate trigger and trigger =20
> function.  The part I'm 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, =20
> then run the results through the db to create the functions.  The =20
> problem is that for the function to be generic it needs to be able =20
> to work with different record/row types.  Here's the template for =20
> function (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 =20
> %s escapes where appropriate.  What the function actually does is to =20=

> us the parent table's name to find all of the child partitions which =20=

> are name like some_table_20080101_20080201, split out the dates from =20=

> those to determine which table the insert needs to be redirected =20
> to.  That works fine.  The problem is that since I have to =20
> dynamically generate the destination table name I have to use =20
> EXECUTE for the INSERT statement.  But, I can't see how to use a =20
> record in query passed to EXECUTE.  Am I right in thinking (now) =20
> that this can't be done?

I solved this by doing a lookup of the table's attributes and putting =20=

them directly into the function during the templating step.

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