Re: proposal: array utility functions phase 1 - Mailing list pgsql-hackers

Joe Conway <mail@joeconway.com> writes:
> [ much snipped ]
> The first function borrows from an idea Nigel Andrews had -- i.e. expand an 
> array into rows (and possibly columns). It currently works like this:

> -- 1D array
> test=# select * from array_values('{101,102,103,104}'::int[]) as (a int, b1 int);

> Now on to the TODO item. Given the array_values() function, here's what I was
> thinking of to implement listing members of a group:

> CREATE OR REPLACE FUNCTION pg_get_grolist(text) RETURNS INT[] AS 'SELECT 
> grolist FROM pg_group WHERE groname = $1' LANGUAGE 'sql';

> CREATE TYPE pg_grolist_rec AS (array_index int, member_name text);

> CREATE OR REPLACE FUNCTION group_list(text) RETURNS SETOF pg_grolist_rec AS 
> 'SELECT g.id, pg_get_userbyid(g.usesysid)::text AS member_name FROM 
> array_values(pg_get_grolist($1)) AS g(id int, usesysid int)' LANGUAGE 'sql';


This crystallizes something that has been bothering me for awhile: the
table function syntax is severely hobbled (not to say crippled :-() by
the fact that the function arguments have to be constants.  You really
don't want to have to invent intermediate functions every time you want
a slightly different query --- yet this technique seems to require *two*
bespoke functions for every query, one on each end of the array_values()
function.

The original Berkeley syntax, messy as it was, at least avoided this
problem.  For example, I believe this same problem could be solved
(approximately) with
select array_values(grolist) from pg_group where groname = 'g2'

--- getting the users shown as names instead of numbers would take an
extra level of SELECT, but I leave the details to the reader.

I think we ought to try to find a way that table functions could be used
with inputs that are taken from tables.  In a narrow sense you can do
this already, with a sub-SELECT:
select * from my_table_func((select x from ...));

but (a) the sub-select can only return a single value, and (b) you can't
get at any of the other columns in the row the sub-select is selecting.
For instance it won't help me much to do
select * fromarray_values((select grolist from pg_group where groname = 'g2'))

if I want to show the group's grosysid as well.

I know I'm not explaining this very well (I'm only firing on one
cylinder today :-(), but basically I think we need to step back and take
another look at the mechanism before we start inventing tons of helper
functions to make up for the lack of adequate mechanism.


As for array_values() itself, it seems fairly inelegant to rely on the
user to get the input and output types to match up.  Now that we have
an "anyarray" pseudotype, I think it wouldn't be unreasonable to hack up
some kluge in the parser to allow reference to the element type of such
an argument --- that is, you'd say something like
create function array_values(anyarray) returns setof anyarray_element

and the parser would automatically understand what return type to assign
to any particular use of array_values.  (Since type resolution is done
bottom-up, I see no logical difficulty here, though the implementation
might be a bit of a wart...)
        regards, tom lane