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=>