CREATE TABLE problem in plpgsql trigger

[James Croft <james.croft@xxxxxxxxxxx>]

Hi all,

I'm trying to create a trigger function for a few tables that will store 
old versions of rows prior to any update on them. Part of the function 
needs to creates other tables (the table to store these snapshots in).

When this trigger runs I get the and error of 'syntax error at or near 
"$1" at character 15' which is the CREATE TABLE line.

<snip>
DECLARE
    rec RECORD;
    snapshottable TEXT;
    originaltable TEXT;
BEGIN
SELECT INTO rec count(*) AS num FROM pg_tables WHERE schemaname = 
''table_snapshots'' AND tablename = TG_RELNAME;
    IF rec.num < 1 THEN
        snapshottable := ''table_snapshots.'' || TG_RELNAME;
        originaltable := TG_RELNAME;
        CREATE TABLE snapshottable (LIKE originaltable);
        ALTER TABLE snapshottable ADD COLUMN snapshottime date;
        ALTER TABLE snapshottable ALTER COLUMN snapshottime SET DEFAULT 
CURRENT_TIMESTAMP;
    END IF;
</snip>


The problems seems to be with the table_name arg being a variable and 
not a literal but can't see how to fix this.

If anyone knows what's going on here or has any pointers it would be 
appreciated.

Thanks,
James

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match