Re: CREATE TABLE problem in plpgsql trigger

On Thu, 19 May 2005, James Croft wrote:

> 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.

Yes, I don't think support statements like CREATE TABLE currently work
with variables directly. You probably can use EXECUTE however by
generating a string containing the command you want to run first.

Something like:
EXECUTE ''CREATE TABLE '' || snapshottable || '' (LIKE ''
|| originaltable || '')'';

excepting that you'd need to be more careful with quoting.

>
> <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.