Dynamic sql and variable record types - Mailing list pgsql-sql
From | Erik Jones |
---|---|
Subject | Dynamic sql and variable record types |
Date | |
Msg-id | EB0D5597-D86F-4528-B615-074B5A802132@myemma.com Whole thread Raw |
Responses |
SOLVED - Re: Dynamic sql and variable record types
(Erik Jones <erik@myemma.com>)
|
List | pgsql-sql |
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