Обсуждение: Where's the doc for "array()" — as in "select array(values (17), (42))"

On 9/18/22 13:25, Bryn Llewellyn wrote:
> 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);
> 

> 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.

https://www.postgresql.org/docs/current/sql-expressions.html

4.2.12. Array Constructors

"It is also possible to construct an array from the results of a 
subquery. In this form, the array constructor is written with the key 
word ARRAY followed by a parenthesized (not bracketed) subquery. For 
example:

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
..."

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/18/22 13:25, Bryn Llewellyn wrote:
> 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".
> 

Lack of an explicit GROUP BY falls through to an implied one:

https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY

"If there are aggregate functions but no GROUP BY clause, the query is 
treated as having a single group comprising all the selected rows."


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/18/22 13:25, Bryn Llewellyn wrote:

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);

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.

https://www.postgresql.org/docs/current/sql-expressions.html

adrian.klaver@aklaver.com wrote:

bryn@yugabyte.com wrote:

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);

Anyway, I can't find where "array()" is documented. Google does find me a hit on stackexchange-dot-com. But it doesn't x-ref to the PG doc.

www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

4.2.12. Array Constructors

It is also possible to construct an array from the results of a subquery. In this form, the array constructor is written with the key word ARRAY followed by a parenthesized (not bracketed) subquery. For example:

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');

Why is the "array()" constructor not found in "pg_proc"?

 After all, section 4.2.12 refers to "array_agg()" as a constructor. And that *is* found in "pg_proc".

Bryn Llewellyn <bryn@yugabyte.com> writes:
> Why is the "array()" constructor not found in "pg_proc"?

Because it isn't a function.  Yeah, it kind of looks like one,
but its argument is a subquery.  If SQL had first-class functions
and closures, maybe ARRAY() could be implemented as an ordinary
function.  But I don't see any plausible way to do that as things
stand.

There are a bunch of other things that look like functions but
aren't in pg_proc, too :-(.  Most of them are just catering to
the SQL committee's weird allergy to writing functions with
plain function syntax.  But ARRAY()'s problem is semantic not
syntactic.

            regards, tom lane

On 9/18/22 21:20, Bryn Llewellyn wrote:

> 
> Thanks. I can't, even now, formulate a search that finds this—unless I 
> know that it's a kind of array constructor. (I'll concede, with the 
> benefit of hindsight, that I should have thought of that.) I hope that 
> I'll be able to learn to navigate the PG docs better over time.

The way I found it:

1) Use documentation search box.

2) Enter array(

3) It was in the fifth item PostgreSQL: Documentation: 14: 4.2. Value 
Expressions. The first four dealt with functions or the index and I knew 
they where not relevant.

> 
> Why is the "array()" constructor not found in "pg_proc"? After all, 
> section 4.2.12 refers to "array_agg()" as a constructor. And that *is* 
> found in "pg_proc".


The only place I see array_agg in 4.2.x is 4.2.7. Aggregate Expressions.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

> adrian.klaver@aklaver.com wrote:
>
> bryn@yugabyte.com wrote:
>
>> Thanks. I can't, even now, formulate a search that finds this—unless I know that it's a kind of array constructor.
(I'llconcede, with the benefit of hindsight, that I should have thought of that.) I hope that I'll be able to learn to
navigatethe PG docs better over time. 
>
> The way I found it:
>
> 1) Use documentation search box.
>
> 2) Enter array(
>
> 3) It was in the fifth item PostgreSQL: Documentation: 14: 4.2. Value Expressions. The first four dealt with
functionsor the index and I knew they where not relevant. 

I just repeated exactly those five search steps. I copied the first ten results at the end. Yes, the fifth hit is the
linkto where the account of the array() constructor is found. But you can't tell this from the hitlist itself. And
searchingin the page of the first twenty hits for "array(" finds nothing there. I do see that if you know the answer in
advance,then you could realize that hit #5 is what you want. But the wording "a specific element of the array value can
beextracted" didn't help me. 

Never mind, I trust that I'll remember where to look now.

--------------------------------------------------------------------------------

Result pages: 1 2 3 4 5 6 7 8 9 Next

1. PostgreSQL: Documentation: 14: 8.15. Arrays [8.21]
...Arrays Prev Up Chapter 8. Data Types Home Next 8.15. Arrays 8.15.1. Declaration of Array...
https://www.postgresql.org/docs/14/arrays.html

2. PostgreSQL: Documentation: 14: 9.19. Array Functions and Operators [5.04]
...Array Functions and Operators Prev Up Chapter 9. Functions and Operators Home Next 9.19. Array...
https://www.postgresql.org/docs/14/functions-array.html

3. PostgreSQL: Documentation: 14: 9.16. JSON Functions and Operators [4.25]
...array (array elements are indexed from zero, but negative integers count from the end).
'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json...
https://www.postgresql.org/docs/14/functions-json.html

4. PostgreSQL: Documentation: 14: Index [2.63]
...array, Arrays accessing, Accessing Arrays constant, Array Value Input constructor, Array Constructors declaration,
Declarationof Array... 
https://www.postgresql.org/docs/14/bookindex.html

5. PostgreSQL: Documentation: 14: 4.2. Value Expressions [2.27]
...array type, then a specific element of the array value can be extracted by writing...
https://www.postgresql.org/docs/14/sql-expressions.html

6. PostgreSQL: Documentation: 14: 8.14. JSON Types [1.76]
...array on the right side is not considered contained within the -- array on the left...
https://www.postgresql.org/docs/14/datatype-json.html

7. PostgreSQL: Documentation: 14: 66.3. Extensibility [1.33]
...array of operators and comparison values */ ScanKey orderbys; /* array of ordering operators and comparison *
values...
https://www.postgresql.org/docs/14/spgist-extensibility.html

8. PostgreSQL: Documentation: 14: F.18. intarray [1.33]
...arrays. Although they will accept input arrays of more dimensions, the data is treated as though...
https://www.postgresql.org/docs/14/intarray.html

9. PostgreSQL: Documentation: 14: CREATE TYPE [1.33]
...array type; for example, it will not be a candidate for the result type of ARRAY...
https://www.postgresql.org/docs/14/sql-createtype.html

10. PostgreSQL: Documentation: 14: 9.24. Row and Array Comparisons [1.15]
...Array Comparisons Prev Up Chapter 9. Functions and Operators Home Next 9.24. Row and Array...
https://www.postgresql.org/docs/14/functions-comparisons.html

tgl@sss.pgh.pa.us wrote:

bryn@yugabyte.com wrote:

Why is the "array()" constructor not found in "pg_proc"?

Because it isn't a function. Yeah, it kind of looks like one, but its argument is a subquery. If SQL had first-class functions and closures, maybe ARRAY() could be implemented as an ordinary function. But I don't see any plausible way to do that as things stand.

There are a bunch of other things that look like functions but aren't in pg_proc, too :-(. Most of them are just catering to the SQL committee's weird allergy to writing functions with plain function syntax. But ARRAY()'s problem is semantic not syntactic.

Bryn Llewellyn <bryn@yugabyte.com> writes:
> This works fine:
>   select length( (select 'dog') )
> But without the doubled parentheses, it causes a syntax error.

The parens are required for it to be a valid scalar subquery, as per
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES

The reason ARRAY() is different is exactly that its argument is
directly a subquery, not an expression that chances to be a
scalar subquery.

> On the other hand, an extra pair of surrounding parentheses here
>   select array( (values (17), (42)) )
> while not necessary, *is* tolerated.

You can pretty much always add *extra* parens in any expression
context.

            regards, tom lane