Re: Stored Procedure Record Updates using For Loops - Postgres 8.1

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.
I'm sticked to 9.1, hope the same is for 8.1
http://www.postgresql.org/docs/9.1/static/plpgsql-declarations.html

According to doc for 8.3 it looks the same (
http://www.postgresql.org/docs/8.3/static/plpgsql-declarations.html) so
should work.

Regards,
Bartek

2012/2/28 Lummis, Patrick J <PJL(at)dolby(dot)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
>
>
>