Great! Thanks Alban, Alexander, and Thomas.
That solved the issue but could you tell me what is the issue when I give brackets in second query?
techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now())
except select id, txid, txtime
from changelogtest
where id=5;
INSERT 0 1
techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now())
except select (id, txid, txtime)
from changelogtest
where id=5;
ERROR: each EXCEPT query must have the same number of columns
LINE 2: except select (id, txid, txtime)
I need brackets because this query actually I am using from trigger like below and it gives the same error: Could you please help me with it.
CREATE OR REPLACE FUNCTION insert_history_info()
RETURNS VOID AS
$BODY$
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
# 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.
Thanks,
Dipti
On Fri, Apr 23, 2010 at 5:24 PM, Alban Hertroys
<dalroi@solfertje.student.utwente.nl> wrote:
On 23 Apr 2010, at 13:17, dipti shah wrote:
For this case you're using 3 values in the first half of the expression and only 1 in the second:
> techdb=# INSERT INTO changelogtest (id, txid, txtime)
> values (5, 123, 'now')
^^ ^^^ ^^^^--- 3 columns, namely int, int & text.
> except
> select (id, txid, txtime)
^^^^^^^^^^^^^^^^^^^--- 1 column, a row-type containing (int, int, timestamp)
> from changelogtest
> where id=5;
> ERROR: each EXCEPT query must have the same number of columns
> LINE 2: except select (id, txid, txtime)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:1050,4bd18a8610411242712669!