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
