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