Skip site navigation (1) Skip section navigation (2)

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

From: "Lummis, Patrick J" <PJL(at)dolby(dot)com>
To: "Bartosz Dmytrak" <bdmytrak(at)eranet(dot)pl>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Stored Procedure Record Updates using For Loops - Postgres 8.1
Date: 2012-02-28 20:35:58
Message-ID: 05BB196AB3DA6C4BBE11AB6C957581FE438C9F70@sfo-exch-01.dolby.net (view raw or flat)
Thread:
Lists: pgsql-general
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 




In response to

Responses

pgsql-general by date

Next:From: hamann.wDate: 2012-02-28 20:36:23
Subject: Re: what Linux to run
Previous:From: Bartosz DmytrakDate: 2012-02-28 20:33:25
Subject: Re: how to create data on the fly?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group