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