pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpl/pgsql capabilities?
4+ messages / 2 participants
[nested] [flat]
* pl/pgsql capabilities?
@ 2002-06-30 23:43 Ferenc Engard <engard@all.hu>
0 siblings, 1 reply; 4+ messages in thread
From: Ferenc Engard @ 2002-06-30 23:43 UTC (permalink / raw)
To: pgsql-sql
Hi all,
A long time ago (at version 6.3.2 or what :) I have asked, but maybe
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?
Thank you:
Circum
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pl/pgsql capabilities?
@ 2002-07-01 14:40 Roberto Mello <rmello@cc.usu.edu>
parent: Ferenc Engard <engard@all.hu>
0 siblings, 1 reply; 4+ messages in thread
From: Roberto Mello @ 2002-07-01 14:40 UTC (permalink / raw)
To: Ferenc Engard <engard@all.hu>; +Cc: pgsql-sql
On Mon, Jul 01, 2002 at 01:43:29AM +0200, Ferenc Engard wrote:
> Hi all,
>
> A long time ago (at version 6.3.2 or what :) I have asked, but maybe
> 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
-Roberto
--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
I wonder if Singapore has any computer pirateers or hackers...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pl/pgsql capabilities?
@ 2002-07-01 23:37 Ferenc Engard <engard@all.hu>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Ferenc Engard @ 2002-07-01 23:37 UTC (permalink / raw)
To: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pl/pgsql capabilities?
@ 2002-07-02 05:23 Roberto Mello <rmello@cc.usu.edu>
parent: Ferenc Engard <engard@all.hu>
0 siblings, 0 replies; 4+ messages in thread
From: Roberto Mello @ 2002-07-02 05:23 UTC (permalink / raw)
To: Ferenc Engard <engard@all.hu>; +Cc: pgsql-sql
On Tue, Jul 02, 2002 at 01:37:08AM +0200, Ferenc Engard wrote:
>
> 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.
Maybe not for the solution you have right now, but you most likely can
rework things a bit to make it work.
> 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
> )
...
> 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?
Not in the way you currently have, as far as I could understand it. I didn't
read the procedure very attentively because it's horribly written in all caps,
and my eyes started scrambling the letters half way through.
-Roberto
--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
"Data! I thought you were dead!" "No, Sir. I rebooted."
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-02 05:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-30 23:43 pl/pgsql capabilities? Ferenc Engard <engard@all.hu>
2002-07-01 14:40 ` Roberto Mello <rmello@cc.usu.edu>
2002-07-01 23:37 ` Ferenc Engard <engard@all.hu>
2002-07-02 05:23 ` Roberto Mello <rmello@cc.usu.edu>
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