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

[Lummis, Patrick J]

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: bdmyt...@gmail.com [mailto:bdmyt...@gmail.com] On Behalf Of
Bartosz Dmytrak
Sent: Tuesday, February 28, 2012 12:24 PM
To: Lummis, Patrick J
Cc: pgsql-general@postgresql.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 <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