I noticed that "array()" is used in the query that implements the "\du" psql meta-command. It has some similarity with "array_agg()" thus:
create temporary view x(v) as (values (17), (42));
select array_agg(v) from x;
select array(select v from x);
But there are differences. The "array()" function requires that its argument is a subquery that returns a single column. (Its data type can be composite.) But the "array_agg()" function's argument must be the select list in the larger context of a select statement—and in general together with "group by".
It seems that the functionality of "array()" can always be achieved by using "array_agg()"—but the overall construct might be less compact. I don't know if the converse is true. (I didn't think it through.)
Anyway, I can't find where "array()" is documented. Google does find me a hit on
stackexchange.com. But it doesn't x-ref to the PG doc.
Finally, my new realization that even the humble "length()" is just a function in the pg_catalog schema that comes with any newly-created database led me to think that I'd find "array()" there. But while "\df length" and "\df array_agg" give me useful information, "\df array" gives me nothing. More carefully stated, this:
select proname from pg_proc
where proname in ('length', 'array_agg', 'array');
gets rows for "length" and "array_agg" but not for "array". What's going on here? I wondered if "array()" might be part of SQL syntax, like (loosely) "as(…)" is. But the account of the "select" statement doesn't mention it.
