Hi,
I suppose the the workorderRecord IS updated, but You expect "workorder" table row to be updated :)
if so, function snipped should be like this:
...
ELSE
UPDATE workorder
SET wfstatus = 'failed'
WHERE workorder.primary_key = workorderRecord.primary_key;
...
this will update workorder table row which corresponds to workorderRecord. workorderRecord is not exactly the reference to workorder table row (like in JAVA), but it is rather a separate copy.
regards,
Bartek
2012/2/28 Lummis, Patrick J
<PJL@dolby.com>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