Stored Procedure Record Updates using For Loops - Postgres 8.1

From: "Lummis, Patrick J" <PJL(at)dolby(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Stored Procedure Record Updates using For Loops - Postgres 8.1
Date: 2012-02-28 20:08:38
Message-ID: 05BB196AB3DA6C4BBE11AB6C957581FE438C9E24@sfo-exch-01.dolby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dave Vitek 2012-02-28 20:09:43 Re: strategies for dealing with frequently updated tables
Previous Message Adrian Klaver 2012-02-28 19:59:54 Re: Having a problem with RoR-3.1.1 and Pg-9.1