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