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 <p...@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 > > >
