SOLVED - Re: Dynamic sql and variable record types - Mailing list pgsql-sql
From | Erik Jones |
---|---|
Subject | SOLVED - Re: Dynamic sql and variable record types |
Date | |
Msg-id | E2DCA422-9214-40AB-8354-CC4FA6050601@myemma.com Whole thread Raw |
In response to | Dynamic sql and variable record types (Erik Jones <erik@myemma.com>) |
List | pgsql-sql |
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