pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCursors in plpgsql
8+ messages / 7 participants
[nested] [flat]
* Cursors in plpgsql
@ 2001-04-16 13:36 Preeti Kamble <preeti.kamble@wipro.com>
2001-04-17 06:34 ` Re: Cursors in plpgsql Anand Raman <araman@india-today.com>
2001-04-17 14:56 ` Re: Cursors in plpgsql Josh Berkus <josh@agliodbs.com>
2001-04-19 23:29 ` Re: Cursors in plpgsql Hans-Jürgen Schönig <hs@cybertec.at>
0 siblings, 3 replies; 8+ messages in thread
From: Preeti Kamble @ 2001-04-16 13:36 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
Hi
Is there a command in plpgsql similar to %NOTFOUND of oracle?
i want to write a load script which takes each row from the temporary
table, do some processing and insert into actual tables. Any idea how i
can accomplish this???
thank you
regards
Preeti
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cursors in plpgsql
2001-04-16 13:36 Cursors in plpgsql Preeti Kamble <preeti.kamble@wipro.com>
@ 2001-04-17 06:34 ` Anand Raman <araman@india-today.com>
2 siblings, 0 replies; 8+ messages in thread
From: Anand Raman @ 2001-04-17 06:34 UTC (permalink / raw)
To: pgsql-general@postgresql.org
there is the FOUND variable which can be negated to have the desired
effect ..
Try out
IF NOT FOUND ....
Hope this helps
Anand
On Mon, Apr 16, 2001 at 07:06:59PM +0530, Preeti Kamble wrote:
>Hi
>Is there a command in plpgsql similar to %NOTFOUND of oracle?
>
>i want to write a load script which takes each row from the temporary
>table, do some processing and insert into actual tables. Any idea how i
>can accomplish this???
>
>thank you
>regards
>Preeti
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cursors in plpgsql
2001-04-16 13:36 Cursors in plpgsql Preeti Kamble <preeti.kamble@wipro.com>
@ 2001-04-17 14:56 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2001-04-17 14:56 UTC (permalink / raw)
To: Preeti Kamble <preeti.kamble@wipro.com>; pgsql-sql
Preeti,
> Is there a command in plpgsql similar to %NOTFOUND of oracle?
Cursors are not currently supported for PL/pgSQL. There are plans to
include them for and upcoming version of Postgres. But dont' wait your
application on it.
> i want to write a load script which takes each row from the temporary
> table, do some processing and insert into actual tables. Any idea how
> i
> can accomplish this???
Yes. Please see my post from last week called: "Re: [SQL] Calling plSQL
functions".
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Cursors in plpgsql
2001-04-16 13:36 Cursors in plpgsql Preeti Kamble <preeti.kamble@wipro.com>
@ 2001-04-19 23:29 ` Hans-Jürgen Schönig <hs@cybertec.at>
2001-04-23 12:03 ` Re: Re: Cursors in plpgsql Richard Huxton <dev@archonet.com>
2 siblings, 1 reply; 8+ messages in thread
From: Hans-Jürgen Schönig @ 2001-04-19 23:29 UTC (permalink / raw)
To: pgsql-sql
Currently no real cursors are supported - you can build a workaround using
a loop - this works in most cases.
I have found a doc (a very good one) that describes porting from Oracle to
Postgres - accidentally I have lost the link but I remeber that I have
found it on altavista with "+plpgsql +plsql +postgres +oracle" or something
like that.
Hans
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Re: Cursors in plpgsql
2001-04-16 13:36 Cursors in plpgsql Preeti Kamble <preeti.kamble@wipro.com>
2001-04-19 23:29 ` Re: Cursors in plpgsql Hans-Jürgen Schönig <hs@cybertec.at>
@ 2001-04-23 12:03 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2001-04-23 12:03 UTC (permalink / raw)
To: Hans-Jürgen Schönig <hs@cybertec.at>; pgsql-sql
From: "Hans-Jürgen Schönig" <hs@cybertec.at>
> Currently no real cursors are supported - you can build a workaround using
> a loop - this works in most cases.
> I have found a doc (a very good one) that describes porting from Oracle to
> Postgres - accidentally I have lost the link but I remeber that I have
You can get to it from http://techdocs.postgresql.org/ - one of Roberto
Mello's contributions IIRC
- Richard Huxton
^ permalink raw reply [nested|flat] 8+ messages in thread
* cursors in plpgsql
@ 2003-09-17 20:46 Tomasz Myrta <jasiek@klaster.net>
2003-09-17 21:13 ` Re: cursors in plpgsql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Tomasz Myrta @ 2003-09-17 20:46 UTC (permalink / raw)
To: pgsql-sql
Hi
I wanted to use some select result several times in pl/pgsql function.
DECLARE
test refcursor;
x record;
BEGIN
open test for select...
fetch test into x;
while found loop
...work...
fetch test into x;
end loop;
...rewind cursor using move...
fetch test...
Neither move backward 10000 in test;
nor
execute ''move backward 10000 in test'';
doesn't work.
In first case I get:
ERROR: parser: parse error at or near "$1"...
In secod:
ERROR: unexpected error -5 in EXECUTE of query...
I tried also with "perform" - I got no error, but also no valid result.
What should I do with it?
Or maybe is it possible somehow to use cursor in FOR..IN loop?
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: cursors in plpgsql
2003-09-17 20:46 cursors in plpgsql Tomasz Myrta <jasiek@klaster.net>
@ 2003-09-17 21:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-18 06:04 ` Re: cursors in plpgsql Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-09-17 21:13 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Tomasz Myrta <jasiek@klaster.net> writes:
> I wanted to use some select result several times in pl/pgsql function.
> Neither move backward 10000 in test;
> nor
> execute ''move backward 10000 in test'';
> doesn't work.
Releases before 7.4 are spotty about supporting backwards scan of
complex queries --- if you have a join or aggregate in the query,
it likely won't work, yielding either strange errors or wrong answers.
It will work if the top plan node in the query is a SORT, though, so
a possible workaround is to add an explicit ORDER BY to the cursor's
query. (You will need to do some investigation with EXPLAIN to make
sure you are getting a suitable plan for the cursor.)
Or try 7.4 beta ...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: cursors in plpgsql
2003-09-17 20:46 cursors in plpgsql Tomasz Myrta <jasiek@klaster.net>
2003-09-17 21:13 ` Re: cursors in plpgsql Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-09-18 06:04 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 8+ messages in thread
From: Tomasz Myrta @ 2003-09-18 06:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> Releases before 7.4 are spotty about supporting backwards scan of
> complex queries --- if you have a join or aggregate in the query,
> it likely won't work, yielding either strange errors or wrong answers.
>
> It will work if the top plan node in the query is a SORT, though, so
> a possible workaround is to add an explicit ORDER BY to the cursor's
> query. (You will need to do some investigation with EXPLAIN to make
> sure you are getting a suitable plan for the cursor.)
I rewrote my query to have sort in top of plan:
Sort (cost=151.24..151.25 rows=1 width=36)
Sort Key: czas
-> Aggregate (cost=151.22..151.23 rows=1 width=36)
-> Group (cost=151.22..151.23 rows=1 width=36)
-> Sort (cost=151.22..151.22 rows=1 width=36)
I'm not sure if it is what you were talking about, but it didn't help.
Anyway the best choice for this function would be a C function, but SPI
scares me...
And one more question - which syntax is valid?
move backward..
or
execute ''move backward...
> Or try 7.4 beta ...
Currently stable branches are better for me...
>
> regards, tom lane
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-09-18 06:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-16 13:36 Cursors in plpgsql Preeti Kamble <preeti.kamble@wipro.com>
2001-04-17 06:34 ` Anand Raman <araman@india-today.com>
2001-04-17 14:56 ` Josh Berkus <josh@agliodbs.com>
2001-04-19 23:29 ` Hans-Jürgen Schönig <hs@cybertec.at>
2001-04-23 12:03 ` Richard Huxton <dev@archonet.com>
2003-09-17 20:46 cursors in plpgsql Tomasz Myrta <jasiek@klaster.net>
2003-09-17 21:13 ` Re: cursors in plpgsql Tom Lane <tgl@sss.pgh.pa.us>
2003-09-18 06:04 ` Re: cursors in plpgsql Tomasz Myrta <jasiek@klaster.net>
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