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

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 (view raw or flat)
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

pgsql-admin by date

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

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