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

[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

Post by Lummis, Patrick J
**
Hi,
I'm trying to update a record within a for loop and at the point of
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
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

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

[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

Post by 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
------------------------------
Dmytrak
*Sent:* Tuesday, February 28, 2012 12:24 PM
*To:* Lummis, Patrick J
*Subject:* Re: [GENERAL] Stored Procedure Record Updates using For Loops
- Postgres 8.1
Hi,
instead of
*update workorderRecord set wfstatus='failed'; *
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

Post by Lummis, Patrick J
**
Hi,
I'm trying to update a record within a for loop and at the point of
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
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

[Adrian Klaver]

Post by 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.
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.

Post by Lummis, Patrick J
Regards,
Patrick

[Lummis, Patrick J]

Indeed there is an id field. That's the ticket!

And thanks much.

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

Post by 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.

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.

Post by Lummis, Patrick J
Regards,
Patrick

--
Adrian Klaver
***@gmail.com