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