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 <
[email protected]> 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!
>
>
>
