pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql syntax question
3+ messages / 2 participants
[nested] [flat]
* plpgsql syntax question
@ 2010-08-30 20:36 Mark Rostron <mrostron@ql2.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Rostron @ 2010-08-30 20:36 UTC (permalink / raw)
To: 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=>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql syntax question
@ 2010-08-30 23:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Rostron <mrostron@ql2.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2010-08-30 23:25 UTC (permalink / raw)
To: Mark Rostron <mrostron@ql2.com>; +Cc: pgsql-admin
Mark Rostron <mrostron@ql2.com> writes:
> 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.
That syntax is fine ... in 8.4 and up. I think you are trying to use
some older server version, and not reading the right version of the
manual.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql syntax question
@ 2010-08-31 01:44 Mark Rostron <mrostron@ql2.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Rostron @ 2010-08-31 01:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> That syntax is fine ... in 8.4 and up. I think you are trying to use some older server version, and not reading the right version of the manual.
Thanks Mr Lane
Yes - I was testing it on 8.3.10, but the manual was 8.4.2 :(
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-08-31 01:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-30 20:36 plpgsql syntax question Mark Rostron <mrostron@ql2.com>
2010-08-30 23:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-31 01:44 ` Mark Rostron <mrostron@ql2.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox