> t...@sss.pgh.pa.us wrote: > >> b...@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.
There must be a significant difference between this: select 'dog' and this: (select 'dog') This works fine: select length( (select 'dog') ) But without the doubled parentheses, it causes a syntax error. On the other hand, an extra pair of surrounding parentheses here select array( (values (17), (42)) ) while not necessary, *is* tolerated. All this started because I had wrongly assumed that "pg_terminate_backend()" would have the same character as "array()" by not being subject to the "execute" privilege — just as is the case for all SQL built-ins in Oracle database, like "length()". I have a better mental model now. Anyway, I know what to do in future. I'll simply look in pg_proc on a case-by-case basis.
