Syntax error in trigger

[Jignesh Shah <jignesh.shah1980@xxxxxxxxx>]

Hi All,

 

I have written below trigger and applied on the table but it is giving syntax error when it gets invoked. Could you please help me what is wrong? I have given complete details here:

 

my trigger function:

 

CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
  RETURNS trigger AS
$BODY$

my $id    = $_TD->{new}{'id'};
my $query = "
   INSERT INTO log_table(id)
   SELECT $1
   EXCEPT SELECT id   --> throwing error for here: syntax error at or near "EXCEPT"
   FROM loggingtable
   WHERE id = $1;";

 

if (exists($_SHARED{$query})) {
 $plan  = $_SHARED{$query};
} else {
 $plan  = spi_prepare($query, 'INTEGER');
 $_SHARED{$query} = $plan; 
}

spi_exec_prepared($plan, '$id');

$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER

 

Tables and trigger definitions:

 

CREATE TABLE techdb_table( id integer NOT NULL,  info varchar NOT NULL );

 

CREATE TABLE log_table(  id integer NOT NULL,  txid integer NOT NULL DEFAULT txid_current(),  txtime timestamp NOT NULL DEFAULT transaction_timestamp());

 

CREATE TRIGGER techdb_trigger
BEFORE INSERT ON techdb_table
FOR EACH ROW EXECUTE PROCEDURE techdb_logtable_trigger();

 

Error message:

 

Executing "insert into techdb_table values(1, 'test');" command gives below error.

 

ERROR:  error from Perl function "techdb_logtable_trigger": syntax error at or near "EXCEPT" at line 15.

********** Error **********

ERROR: error from Perl function "techdb_logtable_trigger": syntax error at or near "EXCEPT" at line 15.
SQL state: XX000

 

Thanks,

Jignesh