Skip site navigation (1) Skip section navigation (2)

TRIGGER FUNCTION - TO CREATE TABLE name AS SELECT

From: Daniel Northam <dnortham99(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: TRIGGER FUNCTION - TO CREATE TABLE name AS SELECT
Date: 2011-12-21 18:50:04
Message-ID: CANGHHxm66vQiEO+T5tcsELhca1UF9rKo4bsXf14tXdwsb-SE2w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
I am trying to get some assistance on a plperl function that I am trying to
create for a DELETE/UPDATE  Trigger.   Basically I am trying to create a
recyclebin with the following logic:

#==#

IF TRIGGERED for $TBNAME
THEN
IF NOT EXIST  recyclebin.$TBNAME
DO
CREATE  TABLE recyclebin.$TBNAME AS SELECT * FROM $TBNAME LIMIT 0;
DONE

COPY "old row data" TO recyclebin.$TBNAME
FI

#==#

The problem that i am having is that when I try to "CREATE  TABLE
recyclebin.$TBNAME AS SELECT * FROM $TBNAME LIMIT 0"  from the function I
get a ERROR:  relation "$TBNAME" already exists at line 14.

Here is my actual plperl function that I have created that I am trying to
get working:

#==#
CREATE OR REPLACE FUNCTION copy_to_recyclebin() RETURNS trigger AS $$
%mydata = ();
$TBNAME = "$_TD->{table_name}";
$TBNAME_RECYCLEBIN = 'recyclebin.';
$TBNAME_RECYCLEBIN .= "$_TD->{table_name}";

foreach $key (keys %{$_TD->{old}})
{
   $mydata{column} = $key;
   $mydata{value} = "${$_TD->{old}}{$key}";
}

spi_exec_query("CREATE TABLE $TBNAME_RECYCLEBIN AS SELECT * FROM $TBNAME
LIMIT 0");

while ( ( $mycolum, $myvalu ) = each (%mydata) )
{
        elog(INFO, "$mycolum => $myvalu");
}

undef(%mydata);
SKIP;
$$ LANGUAGE plperl;

CREATE TRIGGER myrecyclebin BEFORE DELETE or UPDATE ON emp FOR EACH ROW
EXECUTE PROCEDURE copy_to_recyclebin();

#==#

And yes the table does not exist when I run this function, and it does not
exist after running the function.

DB1=#\d
List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | emp  | table | postgres
(1 row)


Any help would be greatly appreciated!


--
Sincerely,
Daniel

pgsql-novice by date

Next:From: Takahiro NodaDate: 2011-12-22 06:11:09
Subject: Re: The exact timing at which CHECK constraints are checked
Previous:From: Josh KupershmidtDate: 2011-12-20 14:38:27
Subject: Re: getting auto increment id value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group