Where's the doc for "array()" — as in "select array(values (17), (42))"

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Where's the doc for "array()" — as in "select array(values (17), (42))"
Date: 2022-09-18 20:25:03
Message-ID: F2DA0774-0B4D-4E72-8944-116154A57D98@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 <http://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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2022-09-18 20:29:31 Re: Where's the doc for "array()" — as in "select array(values (17), (42))"
Previous Message celati Laurent 2022-09-18 19:26:22 Fwd: Postgresql/Postgis: Trigger for historization/versioning