Hi Bartek,
Thanks for the quick response.
Syntax error cleared up and loads fine but executing the stored procedure fails to update the row.
Regards,
Patrick
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