Hi,
instead of
update workorderRecord set wfstatus='failed';
try:
workorderRecord.wfstatus := 'failed';
I haven't tested, but workorderRecord is ROWTYPE, so shouldn't be updated like a table.
Regards,
Bartek
2012/2/28 Lummis, Patrick J
<PJL@dolby.com>Hi,
I'm trying to update a record within a for loop and at the point of updating I get the following syntax error:
ERROR: syntax error at or near "$1"
LINE 1: update $1 set wfstatus='failed'
^
QUERY: update $1 set wfstatus='failed'
CONTEXT: SQL statement in PL/PgSQL function "workorder_status_integrity_check" near line 13
********** Error **********
ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "workorder_status_integrity_check" near line 13
Below is the procedure in question using Postgres 8.1:
CREATE OR REPLACE FUNCTION workorder_status_integrity_check() RETURNS integer AS $$
DECLARE
workorderRecord workorder%ROWTYPE;
declare counter int DEFAULT 0;
BEGIN
FOR workorderRecord IN SELECT * from workorder LOOP
IF workorderRecord.wfstatus = 'canceled' THEN
counter = counter +1;
ELSEIF workorderRecord.wfstatus = 'finished' THEN
counter = counter +1;
ELSE
update workorderRecord set wfstatus='failed';
END IF;
END LOOP;
RETURN counter;
END;
$$ LANGUAGE plpgsql;
Thanks, Patrick