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®
[EMAIL PROTECTED]
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
