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

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(at)dolby(dot)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
>
> ------------------------------
> *From:* bdmytrak(at)gmail(dot)com [mailto:bdmytrak(at)gmail(dot)com] *On Behalf Of *Bartosz
> Dmytrak
> *Sent:* Tuesday, February 28, 2012 12:24 PM
> *To:* Lummis, Patrick J
> *Cc:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] 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
>>
>>
>>
>