From: Erik Jones <erik@myemma.com>
To: SQL Postgresql List <pgsql-sql@postgresql.org>
Subject: Dynamic sql and variable record types
Date: Thu, 20 Mar 2008 17:28:18 -0500
Message-ID: <EB0D5597-D86F-4528-B615-074B5A802132@myemma.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com
Subject: Re: Dynamic sql and variable record types
In-Reply-To: <EB0D5597-D86F-4528-B615-074B5A802132@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox