Thread: Dynamic sql and variable record types

Hi, I've been working on a generic date partitioning system and I
think I've reached something that can't be done, but I thought I'd
post a question to the masses in the hope that I'm missing something.
The basic idea of what I'm doing is some userland scripts that will
accept a table name argument along with the name of a date/timestamp
attribute to partition on and create partitions for that table along
with the appropriate trigger and trigger 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
read in and substitute the table column names wherever necessary, then
run the results through the db to create the functions.  The problem
is that for the function to be generic it needs to be able to work
with different record/row types.  Here's the template for 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 := string_to_array(partition, '_');        upper_dim := array_upper(name_parts, 1);        IF
rec.%s>= name_parts[upper_dim-1]::timestamp AND rec.%s <   
name_parts[upper_dim] THEN            IF op = 'INSERT' THEN                EXECUTE 'INSERT INTO %s_' ||
name_parts[upper_dim-1]  
|| '_' ||                            name_parts[upper_dim] || ' VALUES ' ||
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
escapes where appropriate.  What the function actually does is to us
the parent table's name to find all of the child partitions which are
name like some_table_20080101_20080201, split out the dates from those
to determine which table the insert needs to be redirected to.  That
works fine.  The problem is that since I have to dynamically generate
the destination table name I have to use EXECUTE for the INSERT
statement.  But, I can't see how to use a record in query passed to
EXECUTE.  Am I right in thinking (now) that this can't be done?

Erik Jones

DBA | Emma®
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

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

> Hi, I've been working on a generic date partitioning system and I
> think I've reached something that can't be done, but I thought I'd
> post a question to the masses in the hope that I'm missing
> something.  The basic idea of what I'm doing is some userland
> scripts that will accept a table name argument along with the name
> of a date/timestamp attribute to partition on and create partitions
> for that table along with the appropriate trigger and trigger
> 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
> read in and substitute the table column names wherever necessary,
> then run the results through the db to create the functions.  The
> problem is that for the function to be generic it needs to be able
> to work with different record/row types.  Here's the template for
> 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 := string_to_array(partition, '_');
>        upper_dim := array_upper(name_parts, 1);
>        IF rec.%s >= name_parts[upper_dim-1]::timestamp AND rec.%s <
> name_parts[upper_dim] THEN
>            IF op = 'INSERT' THEN
>                EXECUTE 'INSERT INTO %s_' || name_parts[upper_dim-1]
> || '_' ||
>                            name_parts[upper_dim] || ' VALUES ' ||
> 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 escapes where appropriate.  What the function actually does is to
> us the parent table's name to find all of the child partitions which
> are name like some_table_20080101_20080201, split out the dates from
> those to determine which table the insert needs to be redirected
> to.  That works fine.  The problem is that since I have to
> dynamically generate the destination table name I have to use
> EXECUTE for the INSERT statement.  But, I can't see how to use a
> record in query passed to EXECUTE.  Am I right in thinking (now)
> that this can't be done?

I solved this by doing a lookup of the table's attributes and putting
them directly into the function during the templating step.

Erik Jones

DBA | Emma®
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