Re: PL/Java (was: stored procedures)

Petre Scheie wrote:
> My DBA and I looked at the link on using SQL for stored procedures
> (thanks Neil!) and he raised a couple concerns:
>
> 1. In the example given, the query is directed at just one table; he
> says he needs to join multiple tables, and have it return a set. Can PG
> do this?

Pretty much any valid SQL statement will work. E.g.

CREATE TYPE ex1_tup AS (relname name, colname name);

CREATE OR REPLACE FUNCTION ex1(text) returns setof ex1_tup AS '
SELECT c.relname, a.attname
FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid
WHERE relname = $1
' LANGUAGE 'sql';

regression=# SELECT * FROM ex1('pg_group');
relname | colname
----------+----------
pg_group | tableoid
pg_group | cmax
pg_group | xmax
pg_group | cmin
pg_group | xmin
pg_group | ctid
pg_group | groname
pg_group | grosysid
pg_group | grolist
(9 rows)

> 2. The docs say this ability to return a set is deprecated and may be
> removed in a future release. Why is that? Has this functionality been
> replaced by something else (better?)

That is referring to the older style of returning a set in the target
list. It would look like:

SELECT ex1('pg_group');

But that style has semantic problems, and in any case never really did
what you wanted (a single column could be returned, but a composite
return type just gave you back numeric pointers)

HTH,

Joe