Обсуждение: Stored Procedure Record Updates using For Loops - Postgres 8.1

Поиск
Список
Период
Сортировка

Stored Procedure Record Updates using For Loops - Postgres 8.1

От
"Lummis, Patrick J"
Дата:

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

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

От
Bartosz Dmytrak
Дата:
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@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


Re: 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: bdmytrak@gmail.com [mailto:bdmytrak@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 <PJL@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


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

От
Bartosz Dmytrak
Дата:
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@dolby.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@gmail.com [mailto:bdmytrak@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 <PJL@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



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

От
Adrian Klaver
Дата:
On Tuesday, February 28, 2012 12:35:58 pm Lummis, Patrick J wrote:
> Hi Bartek,
>
> Thanks for the quick response.
>
> Syntax error cleared up and loads fine but executing the stored
> procedure fails to update the row.

From the usage I guessing this function is not being used in a trigger.  As such
the ROW variable exists outside the table. If you want to UPDATE the table you
are going to have to do a hybrid of what you have:

update workorder set wfstatus='failed' where id=workorderRecord.id

assuming there is an 'id' field of some sort.


>
> Regards,
>
> Patrick

--
Adrian Klaver
adrian.klaver@gmail.com

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

От
"Lummis, Patrick J"
Дата:
Indeed there is an id field. That's the ticket!

And thanks much.

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Tuesday, February 28, 2012 12:48 PM
To: pgsql-general@postgresql.org
Cc: Lummis, Patrick J; Bartosz Dmytrak
Subject: Re: [GENERAL] Stored Procedure Record Updates using For Loops -
Postgres 8.1

On Tuesday, February 28, 2012 12:35:58 pm Lummis, Patrick J wrote:
> Hi Bartek,
>
> Thanks for the quick response.
>
> Syntax error cleared up and loads fine but executing the stored
> procedure fails to update the row.

From the usage I guessing this function is not being used in a trigger.
As such the ROW variable exists outside the table. If you want to UPDATE
the table you are going to have to do a hybrid of what you have:

update workorder set wfstatus='failed' where id=workorderRecord.id

assuming there is an 'id' field of some sort.


>
> Regards,
>
> Patrick

--
Adrian Klaver
adrian.klaver@gmail.com