Thread: CREATE TABLE problem in plpgsql trigger

CREATE TABLE problem in plpgsql trigger

From

James Croft

Date:

19 May 2005, 13:35:33

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

Re: CREATE TABLE problem in plpgsql trigger

From

Stephan Szabo

Date:

19 May 2005, 14:02:18

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.

Re: CREATE TABLE problem in plpgsql trigger

From

James Croft

Date:

19 May 2005, 17:03:53

Stephan Szabo wrote:

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

Thanks Stephanm, that fixed it.

--
James Croft
Lumison
t: 0845 1199 911
f: 0845 1199 901
d: 0131 5144 022

Attachment

james.croft.vcf