Mailbox-Line: From engard@all.hu  Mon Jul  1 19:37:09 2002
Message-ID: <3D20E7A4.436ED3FD@all.hu>
Date: Tue, 02 Jul 2002 01:37:08 +0200
From: Ferenc Engard <engard@all.hu>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: pl/pgsql capabilities?
References: <3D1F97A1.B4B25B32@all.hu> <20020701144047.GB23653@cc.usu.edu>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> > since it changed: can I use the procedural languages (any of them) to
> > return a table- (or view-) like output, just like in Interbase, for
> > example? E.g., if I have a metamodel and I want to write functions what
> > perform complex computations and queries, can they return the result
> > (which can be of many rows) to the client?
> 
> As of PG 7.2 you can, by returning a cursor:
> 
> http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html

If I understand well, I can create a cursor for a SELECT statement, and
return that cursor. Well, it is better than nothing, but I fear it is
not flexible enogh for me.

Here is an example from an interbase app. I have to declare that this is
not my program, and I do not know IB, so forgive me if I say silly
things...

CREATE PROCEDURE VIEW_1_1 
(
  SZEMPONTID INTEGER,
  PARENT CHAR(10) CHARACTER SET WIN1250,
  ELNEVEZESTIPUSID INTEGER,
  RENDSZERKOD CHAR(16) CHARACTER SET WIN1250
)
RETURNS
(
  CHILD VARCHAR(10) CHARACTER SET WIN1250,
  GYERMEKDB INTEGER,
  OSZTALYTIPUSID INTEGER,
  NORMATIVNEV VARCHAR(30) CHARACTER SET WIN1250,
  TIPUSNEV VARCHAR(30) CHARACTER SET WIN1250,
  NORMATIVKOD VARCHAR(30) CHARACTER SET WIN1250,
  TIPUSKOD VARCHAR(30) CHARACTER SET WIN1250
)
AS
BEGIN
  FOR
    SELECT D1.CHILD, D2.TYPEID
    FROM HIERARCHIA D1, OSZTALY D2
    WHERE
      D1.PARENT = :PARENT AND
      D1.SZEMPONTID=:SZEMPONTID AND
      D1.CHILD = D2.OSZTALYKOD
    ORDER BY D1.SORREND
    INTO :CHILD, :OSZTALYTIPUSID
  DO
  BEGIN
    SELECT COUNT(CHILD) FROM HIERARCHIA WHERE PARENT = :CHILD AND
SZEMPONTID = :SZEMPONTID INTO :GYERMEKDB;
    EXECUTE PROCEDURE VIEW_ELNEVEZES(:ELNEVEZESTIPUSID, :RENDSZERKOD,
NULL, NULL, NULL, NULL, NULL, :CHILD, NULL)
    RETURNING_VALUES :NORMATIVNEV, :TIPUSNEV, :NORMATIVKOD,
:RENDSZERKOD;
    SUSPEND;
  END
END

The point is not what this proc does (in summary, it gets all the childs
with their properties of a parent in a tree structure), but it cannot
gather all the information with just one select, e.g. it gets some data
from another (nontrivial) stored procedure.

As I see, that 'suspend' command gives back the actual row (and the
control) to the caller until it fetches the next row from this proc's
return value. Do I interpret correct that this stored proc returns a
cursor with structure described in the 'RETURNS' part, and which is not
linked to a SELECT statement?

Can I do something similar in PG? If not now, maybe in the (near)
future?

Thanks:
Circum