Getting number of affected row after performing update

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

Responses

Browse pgsql-general by date

  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