Re: CREATE TABLE problem in plpgsql trigger

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: James Croft <james(dot)croft(at)lumison(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: CREATE TABLE problem in plpgsql trigger
Date: 2005-05-19 14:02:09
Message-ID: 20050519065820.V52904@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Franco Bruno Borghesi 2005-05-19 14:28:54 Re: Ayuda con postgresql
Previous Message Maribel Pérez Engroñatt 2005-05-19 12:57:14 Ayuda con postgresql