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.