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=#\dList of relations Schema | Name | Type | Owner --------+------+-------+---------- public | emp | table | postgres(1 row)Any help would be greatly appreciated!
--
Sincerely,
Daniel