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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Takahiro Noda | 2011-12-22 06:11:09 | Re: The exact timing at which CHECK constraints are checked |
Previous Message | Josh Kupershmidt | 2011-12-20 14:38:27 | Re: getting auto increment id value |