From: | dipti shah <shahdipti1980(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Syntax error in spi_prepare usage |
Date: | 2010-04-24 12:01:19 |
Message-ID: | k2sd5b05a951004240501ha2a8a66oaca219e650e5f6ba@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
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 line 15.
My table Definitation:
techdb=# \d changelogtest
...
Table "techdb.changelogtest"
Column | Type | Modifiers
----------------+-----------------------------+------------------------------------------
id | integer | not null
txid | integer | not null default
txid_current()
txtime | timestamp without time zone | not null default
transaction_timestamp()
Foreign-key constraints:
"changelogtest_id_fkey" FOREIGN KEY (id) REFERENCES logtable(id)
Thanks,
Dipti
From | Date | Subject | |
---|---|---|---|
Next Message | dario.ber@libero.it | 2010-04-24 12:48:33 | Catch exception from plpy |
Previous Message | sunpeng | 2010-04-24 03:04:23 | how to set CACHEDEBUG ? |