pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PL/pgSQL: recursion?
2+ messages / 2 participants
[nested] [flat]

* PL/pgSQL: recursion?
@ 2001-01-02 21:31  Albert REINER <areiner@tph.tuwien.ac.at>
  0 siblings, 1 reply; 2+ messages in thread

From: Albert REINER @ 2001-01-02 21:31 UTC (permalink / raw)
  To: pgsql-sql

Saluton,

is there any possibility to do recursion in plpgsql-functions? Here is
what I tried (I know that I can do it with a simple loop, of course; I
am just curious about what one can or cannot do in plpgsql):

,----[ test-recursion ]
| drop table ttt;
| create table ttt
|   ( id int4,
|     pid int4 );
| 
| insert into ttt values (1, NULL);
| insert into ttt values (2, NULL);
| insert into ttt values (3, 1);
| insert into ttt values (4, 1);
| insert into ttt values (5, 1);
| insert into ttt values (6, 2);
| insert into ttt values (7, 2);
| 
| drop function idPath(int4);
| create function idPath(int4) returns text as '
| declare
|   str text;
|   str2 text;
|   r record;
| begin
|   select id, pid into r from ttt where id = $1;
|   str := r.id::text;
|   if r.pid IS NOT NULL then
|     str2 := idPath(r.id);
|     str := str || '':'' || str2;
|   end if;
|   return str;
| end;' language 'plpgsql';
| 
`----

And when I tried to use it, I got:

,----
| select idPath(5);
| pqReadData() -- backend closed the channel unexpectedly.
| 	  This probably means the backend terminated abnormally
| 	  before or while processing the request.
| The connection to the server was lost. Attempting reset: Failed.
`----

I'm running Postgres 7.0.2.

Thanks in advance,

Albert.


-- 

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: PL/pgSQL: recursion?
@ 2001-01-03 18:09  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Albert REINER <areiner@tph.tuwien.ac.at>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-01-03 18:09 UTC (permalink / raw)
  To: Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: pgsql-sql

"Albert REINER" <areiner@tph.tuwien.ac.at> writes:
> is there any possibility to do recursion in plpgsql-functions?

Recursion works fine ... but an infinite recursion, such as you have
here, will quickly overflow the available stack space and cause the
backend to crash.  You're invoking idPath with the same argument it
was passed, no?

I changed
> |     str2 := idPath(r.id);
to
> |     str2 := idPath(r.id-1);
and got

regression=# select idPath(5);
 idpath
---------
 5:4:3:2
(1 row)

which may or may not be the answer you wanted, but it does demonstrate
that a plpgsql function can recurse.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-01-03 18:09 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-02 21:31 PL/pgSQL: recursion? Albert REINER <areiner@tph.tuwien.ac.at>
2001-01-03 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>

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