plpgsql syntax question

From: Mark Rostron <mrostron(at)ql2(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: plpgsql syntax question
Date: 2010-08-30 20:36:18
Message-ID: FD020D3E50E7FA479567872E5F5F31E30459EC5C1C@ex01.corp.ql2.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Ok guys - there is something here that I am not seeing .... Can someone please set me straight.
I am trying to create a cursor loop with an "update where current of " statement, but my syntax is wrong.
Would one of you be able to tell me what I'm doing.
Also: adding "for update" to the curs1 cursor declaration didn't fix it.
Thanks in advance.
mr

Table "public.tmp_hotel_load"
Column | Type | Modifiers
-------------+---------+-----------
site | text |
property_id | text |
hotel_name | text |
addrs1 | text |
city | text |
state | text |
zip | text |
country | text |
latitude | text |
longitude | text |
phone | text |
room_count | text |
batch_name | text |
hotel_id | integer |
id | integer |
Indexes:
"thl_u" UNIQUE, btree (site, property_id)

--first try it with the cursor opened - this fails

mrostron=> \i f4.sql
create or replace function tmp_htl() returns void as
$$
declare
--
r record;
i integer;
curs1 cursor is select id from tmp_hotel_load;
--
begin
--
--
i := 0;
for r in curs1
loop
i := i + 1;
update tmp_hotel_load set id = i where current of curs1;
end loop;
--
--
return;
--
--
end
$$ language 'plpgsql';
psql:f4.sql:25: ERROR: syntax error at or near "$1"
LINE 1: $1
^
QUERY: $1
CONTEXT: SQL statement in PL/PgSQL function "tmp_htl" near line 11

--now comment out the 'for' line, and it works..... ??

mrostron=> \i f4.sql
create or replace function tmp_htl() returns void as
$$
declare
--
r record;
i integer;
curs1 cursor is select id from tmp_hotel_load;
--
begin
--
--
i := 0;
-- for r in curs1
loop
i := i + 1;
update tmp_hotel_load set id = i where current of curs1;
end loop;
--
--
return;
--
--
end
$$ language 'plpgsql';
CREATE FUNCTION
mrostron=>

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2010-08-30 23:25:04 Re: plpgsql syntax question
Previous Message Rosser Schwarz 2010-08-29 17:21:11 Re: Use hardware snapshots to restore postgres