Thread: Syntax error in spi_prepare usage

I am using SPI_Prepare from trigger like below. I have some minor issue with $query syntax. I have tried almost all combination but I am not able to resolve. Could anyone please help me out.
 
CREATE OR REPLACE FUNCTION insert_history_info()
RETURNS VOID AS
$BODY$
 my $query = (<<ENDQUERY);
  INSERT INTO changelogtest(id, txid, txtime)
  SELECT  (\$1, \$2, \$3) --> there is some issue here.
  EXCEPT
  SELECT (id, txid, txtime)
  FROM changelogtest
  WHERE id = \$1
  AND txid = \$2
  AND txtime = \$3;
 ENDQUERY
 
 # Always use the prepared query if available
 if (not exists($_SHARED{$query})) {
  $_SHARED{$query} = spi_prepare($query, 'INTEGER', 'INTEGER', 'TIMESTAMP');
 }
 
 spi_exec_prepared($_SHARED{$query}, 5, 123, now());
 
 return;
$BODY$
  LANGUAGE 'plperl' VOLATILE SECURITY DEFINER

techdb=# SELECT insert_history_info();
ERROR:  error from Perl function "insert_history_info": INSERT has more target columns than expressions at line 15.

If I remove the braces from (\$1, \$2, \$3) then it gives below error.

Thanks,
Dipti

> If I remove the braces from (\$1, \$2, \$3) then it gives below error.
>
>  my $query = (<<ENDQUERY);
>   INSERT INTO changelogtest(id, txid, txtime)
>   SELECT  \$1, \$2, \$3
>   EXCEPT
>   SELECT (id, txid, txtime)
>   FROM changelogtest
>   WHERE id = \$1
>   AND txid = \$2
>   AND txtime = \$3;
>  ENDQUERY
>
> techdb=# SELECT insert_history_info();
> ERROR:  error from Perl function "insert_history_info": each EXCEPT query must have the same number of columns at
line15. 

Yes of course, your select lists are different. Try:
>  my $query = (<<ENDQUERY);
>   INSERT INTO changelogtest(id, txid, txtime)
>   SELECT  \$1, \$2, \$3
>   EXCEPT
>   SELECT id, txid, txtime
>   FROM changelogtest
>   WHERE id = \$1
>   AND txid = \$2
>   AND txtime = \$3;
>  ENDQUERY

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bd3201310412109115467!