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=20
> think I've reached something that can't be done, but I thought I'd=20=20
> post a question to the m***** in the hope that I'm missing=20=20
> something. The basic idea of what I'm doing is some userland=20=20
> scripts that will accept a table name argument along with the name=20=20
> of a date/timestamp attribute to partition on and create
partitions=20=20
> for that table along with the appropriate trigger and trigger=20=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=20
> read in and substitute the table column names wherever necessary,=20=20
> then run the results through the db to create the functions. The=20=20
> problem is that for the function to be generic it needs to be able=20=20
> to work with different record/row types. Here's the template for=20=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=
=20
> name_parts[upper_dim] THEN
> IF op =3D 'INSERT' THEN
> EXECUTE 'INSERT INTO %s_' ||
name_parts[upper_dim-1]=20=20
> || '_' ||
> name_parts[upper_dim] || ' VALUES ' ||=20=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=20
> %s escapes where appropriate. What the function actually does is to=20=
=20
> us the parent table's name to find all of the child partitions which=20=
=20
> are name like some_table_20080101_20080201, split out the dates from=20=
=20
> those to determine which table the insert needs to be redirected=20=20
> to. That works fine. The problem is that since I have to=20=20
> dynamically generate the destination table name I have to use=20=20
> EXECUTE for the INSERT statement. But, I can't see how to use a=20=20
> record in query passed to EXECUTE. Am I right in thinking (now)=20=20
> that this can't be done?
I solved this by doing a lookup of the table's attributes and
putting=20=20
them directly into the function during the templating step.
Erik Jones
DBA | Emma=AE
erik@[EMAIL PROTECTED]
or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--=20
Sent via pgsql-sql mailing list (pgsql-sql@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
|
