From: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Getting number of affected row after performing update |
Date: | 2010-02-19 05:42:18 |
Message-ID: | 57097.18732.qm@web65716.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
By referring to article at :
http://blogs.msdn.com/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx
I try to implement as follow :
CREATE OR REPLACE FUNCTION update_or_insert_statistic(int, text, text, double precision)
RETURNS void AS
$BODY$DECLARE
_lotID ALIAS FOR $1;
_measurementType ALIAS FOR $2;
_statisticType ALIAS FOR $3;
_value ALIAS FOR $4;
BEGIN
EXECUTE 'UPDATE statistic SET value = $1 WHERE fk_lot_id = $2 AND measurement_type = $3 AND statistic_type = $4'
USING _value, _lotID, _measurementType, _statisticType;
-- HOW?!?!
--ERROR: column "rowcount" does not exist
--LINE 1: SELECT @@ROWCOUNT=0
IF @@ROWCOUNT=0 THEN
EXECUTE 'INSERT INTO statistic(fk_lot_id, "value", measurement_type, statistic_type) VALUES ($1, $2, $3, $4)'
USING _lotID, _value, _measurementType, _statisticType;
END IF;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION delete_tables(int) OWNER TO postgres;
Of course, I get an error at line :
IF @@ROWCOUNT=0 THEN
May I know what is the correct PostgreSQL syntax for @@ROWCOUNT after update?
Thanks!
Thanks and Regards
Yan Cheng CHEOK
From | Date | Subject | |
---|---|---|---|
Next Message | dipti shah | 2010-02-19 07:21:47 | Not able to access schema functions and table... |
Previous Message | Tom Lane | 2010-02-19 05:17:29 | Re: System Log is logging row number -1 is out of range 0..-1 |