LATERAL, UNNEST and spec compliance
Started by David Fetteralmost 13 years ago9 messages
Folks,
Andrew Gierth asked me to send this out as his email is in a parlous
state at the moment. My comments will follow in replies. Without
further ado:
SQL2008 says, for 7.6 <table reference>
6)
a) If TR is contained in a <from clause> FC with no intervening <query
expression>, then the scope clause SC of TR is the <select statement:
single row> or innermost <query specification> that contains FC. The
scope of a range variable of TR is the <select list>, <where clause>,
<group by clause>, <having clause>, and <window clause> of SC, together
with every <lateral derived table> that is simply contained in FC and
is preceded by TR, and every <collection derived table> that is simply
contained in FC and is preceded by TR, and the <join condition> of all
<joined table>s contained in SC that contain TR. If SC is the <query
specification> that is the <query expression body> of a simple table
query STQ, then the scope of a range variable of TR also includes the
<order by clause> of STQ.
This is the clause that defines the scope effect of LATERAL, and as can be
seen, it defines <collection derived table>, i.e. UNNEST(), as having the
same behaviour as <lateral derived table>.
It is also worth noting at this point that pg's "FROM func()" syntax is not
in the spec (the nearest is "FROM TABLE(<collection value expression>)").
Our implementation of UNNEST currently deviates from the spec by not being
implicitly LATERAL; given the (sub)query
SELECT * FROM sometable, UNNEST(somearray);
then "somearray" is required to be a parameter or outer reference rather
than a column of "sometable". To get the spec's behaviour for this, we
currently have to do:
SELECT * FROM sometable, LATERAL UNNEST(somearray);
which is non-standard syntax. (In the spec, only <table subquery> can
follow LATERAL.)
(We also don't accept the (optional) syntax of S301, allowing multiple
parameters to UNNEST().)
As I see it, the current options are:
1. Do nothing, and insist on non-standard use of the LATERAL keyword.
2. Add UNNEST to the grammar (or parse analysis) as a special case, making
it implicitly LATERAL.
(This would make implementing S301 easier, but special cases are ugly.)
3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
(As far as I can tell, those cases whose behaviour would be changed by
this actually produce errors in versions prior to 9.3, so no working
code should be affected.)
Since LATERAL is new in 9.3, I think the pros and cons of these choices
should be considered now, rather than being allowed to slide by unexamined.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: LATERAL, UNNEST and spec compliance
On Thu, Jan 24, 2013 at 09:51:46AM -0800, David Fetter wrote:
Folks,
Andrew Gierth asked me to send this out as his email is in a parlous
state at the moment. My comments will follow in replies. Without
further ado:
[snip]As I see it, the current options are:
1. Do nothing, and insist on non-standard use of the LATERAL keyword.
2. Add UNNEST to the grammar (or parse analysis) as a special case, making
it implicitly LATERAL.(This would make implementing S301 easier, but special cases are ugly.)
3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
(As far as I can tell, those cases whose behaviour would be changed by
this actually produce errors in versions prior to 9.3, so no working
code should be affected.)Since LATERAL is new in 9.3, I think the pros and cons of these choices
should be considered now, rather than being allowed to slide by unexamined.
Please find attached a patch which implements approach 3. The vast
majority of it is changes to the regression tests. The removed
regression tests in join.{sql,out} are no longer errors, although some
of them are pretty standard DoS attacks, hence they're all removed.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
lateral_srf_in_from_001.patchtext/plain; charset=us-asciiDownload
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 9391,9397 **** table_ref: relation_expr opt_alias_clause
| func_table func_alias_clause
{
RangeFunction *n = makeNode(RangeFunction);
! n->lateral = false;
n->funccallnode = $1;
n->alias = linitial($2);
n->coldeflist = lsecond($2);
--- 9391,9397 ----
| func_table func_alias_clause
{
RangeFunction *n = makeNode(RangeFunction);
! n->lateral = true;
n->funccallnode = $1;
n->alias = linitial($2);
n->coldeflist = lsecond($2);
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 7928,7934 **** get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
deparse_columns *colinfo = deparse_columns_fetch(varno, dpns);
bool printalias;
! if (rte->lateral)
appendStringInfoString(buf, "LATERAL ");
/* Print the FROM item proper */
--- 7928,7934 ----
deparse_columns *colinfo = deparse_columns_fetch(varno, dpns);
bool printalias;
! if (rte->lateral && rte->relkind != RTE_FUNCTION)
appendStringInfoString(buf, "LATERAL ");
/* Print the FROM item proper */
*** a/src/test/regress/expected/aggregates.out
--- b/src/test/regress/expected/aggregates.out
***************
*** 959,967 **** select * from agg_view1;
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
(1 row)
create or replace view agg_view1 as
--- 959,967 ----
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), LATERAL generate_series(1, 3) i(i);
(1 row)
create or replace view agg_view1 as
***************
*** 975,983 **** select * from agg_view1;
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 3) i(i);
(1 row)
create or replace view agg_view1 as
--- 975,983 ----
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), LATERAL generate_series(1, 3) i(i);
(1 row)
create or replace view agg_view1 as
***************
*** 1036,1044 **** select * from agg_view1;
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), generate_series(1, 2) i(i);
(1 row)
drop view agg_view1;
--- 1036,1044 ----
(1 row)
select pg_get_viewdef('agg_view1'::regclass);
! pg_get_viewdef
! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! SELECT aggfns(DISTINCT v.a, v.b, v.c ORDER BY v.a, v.c USING ~<~ NULLS LAST, v.b) AS aggfns FROM (VALUES (1,3,'foo'::text), (0,NULL::integer,NULL::text), (2,2,'bar'::text), (3,1,'baz'::text)) v(a, b, c), LATERAL generate_series(1, 2) i(i);
(1 row)
drop view agg_view1;
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
***************
*** 3577,3616 **** select * from
Output: (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2))
(26 rows)
- -- test some error cases where LATERAL should have been used but wasn't
- select f1,g from int4_tbl a, generate_series(0, f1) g;
- ERROR: column "f1" does not exist
- LINE 1: select f1,g from int4_tbl a, generate_series(0, f1) g;
- ^
- HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
- select f1,g from int4_tbl a, generate_series(0, a.f1) g;
- ERROR: invalid reference to FROM-clause entry for table "a"
- LINE 1: select f1,g from int4_tbl a, generate_series(0, a.f1) g;
- ^
- HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
- select f1,g from int4_tbl a cross join generate_series(0, f1) g;
- ERROR: column "f1" does not exist
- LINE 1: ...ct f1,g from int4_tbl a cross join generate_series(0, f1) g;
- ^
- HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
- select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
- ERROR: invalid reference to FROM-clause entry for table "a"
- LINE 1: ... f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
- ^
- HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
- -- SQL:2008 says the left table is in scope but illegal to access here
- select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
- ERROR: invalid reference to FROM-clause entry for table "a"
- LINE 1: ... int4_tbl a right join lateral generate_series(0, a.f1) g on...
- ^
- DETAIL: The combining JOIN type must be INNER or LEFT for a LATERAL reference.
- select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
- ERROR: invalid reference to FROM-clause entry for table "a"
- LINE 1: ...m int4_tbl a full join lateral generate_series(0, a.f1) g on...
- ^
- DETAIL: The combining JOIN type must be INNER or LEFT for a LATERAL reference.
- -- LATERAL can be used to put an aggregate into the FROM clause of its query
- select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
- ERROR: aggregate functions are not allowed in FROM clause of their own query level
- LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
- ^
--- 3577,3579 ----
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
***************
*** 21,30 **** INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ERROR: invalid reference to FROM-clause entry for table "foo2"
! LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ^
! HINT: There is an entry for table "foo2", but it cannot be referenced from this part of the query.
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
fooid | f2
--- 21,33 ----
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! fooid | f2 | fooid | f2
! -------+-----+-------+-----
! 1 | 11 | 1 | 11
! 2 | 22 | 2 | 22
! 1 | 111 | 1 | 111
! (3 rows)
!
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
fooid | f2
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1280,1305 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
viewname | definition
---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
! pg_available_extension_versions | SELECT e.name, e.version, (x.extname IS NOT NULL) AS installed, e.superuser, e.relocatable, e.schema, e.requires, e.comment FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
! pg_available_extensions | SELECT e.name, e.default_version, x.extversion AS installed_version, e.comment FROM (pg_available_extensions() e(name, default_version, comment) LEFT JOIN pg_extension x ON ((e.name = x.extname)));
! pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
! pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted, l.fastpath FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath);
! pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
! pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolreplication, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_seclabels | (((((((((SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (rel.relkind = 'r'::"char") THEN 'table'::text WHEN (rel.relkind = 'v'::"char") THEN 'view'::text WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text ELSE NULL::text END AS objtype, rel.relnamespace AS objnamespace, CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid = 0) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'column'::text AS objtype, rel.relnamespace AS objnamespace, ((CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END || '.'::text) || (att.attname)::text) AS objname, l.provider, l.label FROM (((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid <> 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (pro.proisagg = true) THEN 'aggregate'::text WHEN (pro.proisagg = false) THEN 'function'::text ELSE NULL::text END AS objtype, pro.pronamespace AS objnamespace, (((CASE WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text ELSE 'type'::text END AS objtype, typ.typnamespace AS objnamespace, CASE WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) END AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'large object'::text AS objtype, NULL::oid AS objnamespace, (l.objoid)::text AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'language'::text AS objtype, NULL::oid AS objnamespace, quote_ident((lan.lanname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'schema'::text AS objtype, nsp.oid AS objnamespace, quote_ident((nsp.nspname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'event trigger'::text AS objtype, NULL::oid AS objnamespace, quote_ident((evt.evtname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'database'::text AS objtype, NULL::oid AS objnamespace, quote_ident((dat.datname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'tablespace'::text AS objtype, NULL::oid AS objnamespace, quote_ident((spc.spcname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'role'::text AS objtype, NULL::oid AS objnamespace, quote_ident((rol.rolname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
! pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
! pg_stat_activity | SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, s.state, s.query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d;
! pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));
pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, pg_stat_get_function_total_time(p.oid) AS total_time, pg_stat_get_function_self_time(p.oid) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
--- 1280,1305 ----
viewname | definition
---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
! pg_available_extension_versions | SELECT e.name, e.version, (x.extname IS NOT NULL) AS installed, e.superuser, e.relocatable, e.schema, e.requires, e.comment FROM (LATERAL pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment) LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
! pg_available_extensions | SELECT e.name, e.default_version, x.extversion AS installed_version, e.comment FROM (LATERAL pg_available_extensions() e(name, default_version, comment) LEFT JOIN pg_extension x ON ((e.name = x.extname)));
! pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM LATERAL pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
! pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted, l.fastpath FROM LATERAL pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted, fastpath);
! pg_prepared_statements | SELECT p.name, p.statement, p.prepare_time, p.parameter_types, p.from_sql FROM LATERAL pg_prepared_statement() p(name, statement, prepare_time, parameter_types, from_sql);
! pg_prepared_xacts | SELECT p.transaction, p.gid, p.prepared, u.rolname AS owner, d.datname AS database FROM ((LATERAL pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolreplication, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, s.setconfig AS rolconfig, pg_authid.oid FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name);
pg_seclabels | (((((((((SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (rel.relkind = 'r'::"char") THEN 'table'::text WHEN (rel.relkind = 'v'::"char") THEN 'view'::text WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text ELSE NULL::text END AS objtype, rel.relnamespace AS objnamespace, CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid = 0) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'column'::text AS objtype, rel.relnamespace AS objnamespace, ((CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END || '.'::text) || (att.attname)::text) AS objname, l.provider, l.label FROM (((pg_seclabel l JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid)))) JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (l.objsubid <> 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (pro.proisagg = true) THEN 'aggregate'::text WHEN (pro.proisagg = false) THEN 'function'::text ELSE NULL::text END AS objtype, pro.pronamespace AS objnamespace, (((CASE WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid)))) JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, CASE WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text ELSE 'type'::text END AS objtype, typ.typnamespace AS objnamespace, CASE WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) END AS objname, l.provider, l.label FROM ((pg_seclabel l JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid)))) JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'large object'::text AS objtype, NULL::oid AS objnamespace, (l.objoid)::text AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid))) WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'language'::text AS objtype, NULL::oid AS objnamespace, quote_ident((lan.lanname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'schema'::text AS objtype, nsp.oid AS objnamespace, quote_ident((nsp.nspname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, l.objsubid, 'event trigger'::text AS objtype, NULL::oid AS objnamespace, quote_ident((evt.evtname)::text) AS objname, l.provider, l.label FROM (pg_seclabel l JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid)))) WHERE (l.objsubid = 0)) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'database'::text AS objtype, NULL::oid AS objnamespace, quote_ident((dat.datname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'tablespace'::text AS objtype, NULL::oid AS objnamespace, quote_ident((spc.spcname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))) UNION ALL SELECT l.objoid, l.classoid, 0 AS objsubid, 'role'::text AS objtype, NULL::oid AS objnamespace, quote_ident((rol.rolname)::text) AS objname, l.provider, l.label FROM (pg_shseclabel l JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
! pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM LATERAL pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline);
pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolreplication AS userepl, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, s.setconfig AS useconfig FROM (pg_authid LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid)))) WHERE pg_authid.rolcanlogin;
! pg_stat_activity | SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, s.state, s.query FROM pg_database d, LATERAL pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_checkpoint_write_time() AS checkpoint_write_time, pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_blk_read_time(d.oid) AS blk_read_time, pg_stat_get_db_blk_write_time(d.oid) AS blk_write_time, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d;
! pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM LATERAL pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, LATERAL pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));
pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_tup_hot_upd, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze, pg_stat_all_tables.vacuum_count, pg_stat_all_tables.autovacuum_count, pg_stat_all_tables.analyze_count, pg_stat_all_tables.autoanalyze_count FROM pg_stat_all_tables WHERE ((pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_tables.schemaname ~ '^pg_toast'::text));
pg_stat_user_functions | SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, pg_stat_get_function_total_time(p.oid) AS total_time, pg_stat_get_function_self_time(p.oid) AS self_time FROM (pg_proc p LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace))) WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
***************
*** 1320,1327 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text));
pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = 1) THEN s.stavalues1 WHEN (s.stakind2 = 1) THEN s.stavalues2 WHEN (s.stakind3 = 1) THEN s.stavalues3 WHEN (s.stakind4 = 1) THEN s.stavalues4 WHEN (s.stakind5 = 1) THEN s.stavalues5 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = 1) THEN s.stanumbers1 WHEN (s.stakind2 = 1) THEN s.stanumbers2 WHEN (s.stakind3 = 1) THEN s.stanumbers3 WHEN (s.stakind4 = 1) THEN s.stanumbers4 WHEN (s.stakind5 = 1) THEN s.stanumbers5 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 WHEN (s.stakind5 = 2) THEN s.stavalues5 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] ELSE NULL::real END AS correlation, CASE WHEN (s.stakind1 = 4) THEN s.stavalues1 WHEN (s.stakind2 = 4) THEN s.stavalues2 WHEN (s.stakind3 = 4) THEN s.stavalues3 WHEN (s.stakind4 = 4) THEN s.stavalues4 WHEN (s.stakind5 = 4) THEN s.stavalues5 ELSE NULL::anyarray END AS most_common_elems, CASE WHEN (s.stakind1 = 4) THEN s.stanumbers1 WHEN (s.stakind2 = 4) THEN s.stanumbers2 WHEN (s.stakind3 = 4) THEN s.stanumbers3 WHEN (s.stakind4 = 4) THEN s.stanumbers4 WHEN (s.stakind5 = 4) THEN s.stanumbers5 ELSE NULL::real[] END AS most_common_elem_freqs, CASE WHEN (s.stakind1 = 5) THEN s.stanumbers1 WHEN (s.stakind2 = 5) THEN s.stanumbers2 WHEN (s.stakind3 = 5) THEN s.stanumbers3 WHEN (s.stakind4 = 5) THEN s.stanumbers4 WHEN (s.stakind5 = 5) THEN s.stanumbers5 ELSE NULL::real[] END AS elem_count_histogram FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text));
pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");
! pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
! pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, pg_shadow.userepl, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow;
pg_user_mappings | SELECT u.oid AS umid, s.oid AS srvid, s.srvname, u.umuser, CASE WHEN (u.umuser = (0)::oid) THEN 'public'::name ELSE a.rolname END AS usename, CASE WHEN (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text)) THEN u.umoptions ELSE NULL::text[] END AS umoptions FROM ((pg_user_mapping u LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) JOIN pg_foreign_server s ON ((u.umserver = s.oid)));
pg_views | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char");
--- 1320,1327 ----
pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text));
pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = 1) THEN s.stavalues1 WHEN (s.stakind2 = 1) THEN s.stavalues2 WHEN (s.stakind3 = 1) THEN s.stavalues3 WHEN (s.stakind4 = 1) THEN s.stavalues4 WHEN (s.stakind5 = 1) THEN s.stavalues5 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = 1) THEN s.stanumbers1 WHEN (s.stakind2 = 1) THEN s.stanumbers2 WHEN (s.stakind3 = 1) THEN s.stanumbers3 WHEN (s.stakind4 = 1) THEN s.stanumbers4 WHEN (s.stakind5 = 1) THEN s.stanumbers5 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 WHEN (s.stakind5 = 2) THEN s.stavalues5 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] WHEN (s.stakind5 = 3) THEN s.stanumbers5[1] ELSE NULL::real END AS correlation, CASE WHEN (s.stakind1 = 4) THEN s.stavalues1 WHEN (s.stakind2 = 4) THEN s.stavalues2 WHEN (s.stakind3 = 4) THEN s.stavalues3 WHEN (s.stakind4 = 4) THEN s.stavalues4 WHEN (s.stakind5 = 4) THEN s.stavalues5 ELSE NULL::anyarray END AS most_common_elems, CASE WHEN (s.stakind1 = 4) THEN s.stanumbers1 WHEN (s.stakind2 = 4) THEN s.stanumbers2 WHEN (s.stakind3 = 4) THEN s.stanumbers3 WHEN (s.stakind4 = 4) THEN s.stanumbers4 WHEN (s.stakind5 = 4) THEN s.stanumbers5 ELSE NULL::real[] END AS most_common_elem_freqs, CASE WHEN (s.stakind1 = 5) THEN s.stanumbers1 WHEN (s.stakind2 = 5) THEN s.stanumbers2 WHEN (s.stakind3 = 5) THEN s.stanumbers3 WHEN (s.stakind4 = 5) THEN s.stanumbers4 WHEN (s.stakind5 = 5) THEN s.stanumbers5 ELSE NULL::real[] END AS elem_count_histogram FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text));
pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");
! pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM LATERAL pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst);
! pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM LATERAL pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst);
pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, pg_shadow.userepl, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow;
pg_user_mappings | SELECT u.oid AS umid, s.oid AS srvid, s.srvname, u.umuser, CASE WHEN (u.umuser = (0)::oid) THEN 'public'::name ELSE a.rolname END AS usename, CASE WHEN (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text)) THEN u.umoptions ELSE NULL::text[] END AS umoptions FROM ((pg_user_mapping u LEFT JOIN pg_authid a ON ((a.oid = u.umuser))) JOIN pg_foreign_server s ON ((u.umserver = s.oid)));
pg_views | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char");
*** a/src/test/regress/expected/window.out
--- b/src/test/regress/expected/window.out
***************
*** 950,958 **** SELECT * FROM v_window;
(10 rows)
SELECT pg_get_viewdef('v_window');
! pg_get_viewdef
! ---------------------------------------------------------------------------------------------------------------------------------
! SELECT i.i, sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows FROM generate_series(1, 10) i(i);
(1 row)
-- with UNION
--- 950,958 ----
(10 rows)
SELECT pg_get_viewdef('v_window');
! pg_get_viewdef
! -----------------------------------------------------------------------------------------------------------------------------------------
! SELECT i.i, sum(i.i) OVER (ORDER BY i.i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows FROM LATERAL generate_series(1, 10) i(i);
(1 row)
-- with UNION
*** a/src/test/regress/sql/join.sql
--- b/src/test/regress/sql/join.sql
***************
*** 986,998 **** select * from
) on c.q2 = ss2.q1,
lateral (select ss2.y) ss3;
- -- test some error cases where LATERAL should have been used but wasn't
- select f1,g from int4_tbl a, generate_series(0, f1) g;
- select f1,g from int4_tbl a, generate_series(0, a.f1) g;
- select f1,g from int4_tbl a cross join generate_series(0, f1) g;
- select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
- -- SQL:2008 says the left table is in scope but illegal to access here
- select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
- select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
- -- LATERAL can be used to put an aggregate into the FROM clause of its query
- select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
--- 986,988 ----
Re: LATERAL, UNNEST and spec compliance
On Thu, Jan 24, 2013 at 09:12:41PM -0800, David Fetter wrote:
On Thu, Jan 24, 2013 at 09:51:46AM -0800, David Fetter wrote:
Folks,
Andrew Gierth asked me to send this out as his email is in a parlous
state at the moment. My comments will follow in replies. Without
further ado:
[snip]As I see it, the current options are:
1. Do nothing, and insist on non-standard use of the LATERAL keyword.
2. Add UNNEST to the grammar (or parse analysis) as a special case, making
it implicitly LATERAL.(This would make implementing S301 easier, but special cases are ugly.)
3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
(As far as I can tell, those cases whose behaviour would be changed by
this actually produce errors in versions prior to 9.3, so no working
code should be affected.)Since LATERAL is new in 9.3, I think the pros and cons of these choices
should be considered now, rather than being allowed to slide by unexamined.Please find attached a patch which implements approach 3. The vast
majority of it is changes to the regression tests. The removed
regression tests in join.{sql,out} are no longer errors, although some
of them are pretty standard DoS attacks, hence they're all removed.Cheers,
David.
Oops. Misspelled rtekind in the previous patch. Here's a corrected
one, much shorter.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
lateral_srf_in_from_002.patchtext/plain; charset=us-asciiDownload
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 9391,9397 **** table_ref: relation_expr opt_alias_clause
| func_table func_alias_clause
{
RangeFunction *n = makeNode(RangeFunction);
! n->lateral = false;
n->funccallnode = $1;
n->alias = linitial($2);
n->coldeflist = lsecond($2);
--- 9391,9397 ----
| func_table func_alias_clause
{
RangeFunction *n = makeNode(RangeFunction);
! n->lateral = true;
n->funccallnode = $1;
n->alias = linitial($2);
n->coldeflist = lsecond($2);
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 7928,7934 **** get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
deparse_columns *colinfo = deparse_columns_fetch(varno, dpns);
bool printalias;
! if (rte->lateral)
appendStringInfoString(buf, "LATERAL ");
/* Print the FROM item proper */
--- 7928,7934 ----
deparse_columns *colinfo = deparse_columns_fetch(varno, dpns);
bool printalias;
! if (rte->lateral && rte->rtekind != RTE_FUNCTION)
appendStringInfoString(buf, "LATERAL ");
/* Print the FROM item proper */
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
***************
*** 3577,3603 **** select * from
Output: (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2))
(26 rows)
- -- test some error cases where LATERAL should have been used but wasn't
- select f1,g from int4_tbl a, generate_series(0, f1) g;
- ERROR: column "f1" does not exist
- LINE 1: select f1,g from int4_tbl a, generate_series(0, f1) g;
- ^
- HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
- select f1,g from int4_tbl a, generate_series(0, a.f1) g;
- ERROR: invalid reference to FROM-clause entry for table "a"
- LINE 1: select f1,g from int4_tbl a, generate_series(0, a.f1) g;
- ^
- HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
- select f1,g from int4_tbl a cross join generate_series(0, f1) g;
- ERROR: column "f1" does not exist
- LINE 1: ...ct f1,g from int4_tbl a cross join generate_series(0, f1) g;
- ^
- HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
- select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
- ERROR: invalid reference to FROM-clause entry for table "a"
- LINE 1: ... f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
- ^
- HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
ERROR: invalid reference to FROM-clause entry for table "a"
--- 3577,3582 ----
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
***************
*** 21,30 **** INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ERROR: invalid reference to FROM-clause entry for table "foo2"
! LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ^
! HINT: There is an entry for table "foo2", but it cannot be referenced from this part of the query.
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
fooid | f2
--- 21,33 ----
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! fooid | f2 | fooid | f2
! -------+-----+-------+-----
! 1 | 11 | 1 | 11
! 2 | 22 | 2 | 22
! 1 | 111 | 1 | 111
! (3 rows)
!
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
fooid | f2
*** a/src/test/regress/sql/join.sql
--- b/src/test/regress/sql/join.sql
***************
*** 986,996 **** select * from
) on c.q2 = ss2.q1,
lateral (select ss2.y) ss3;
- -- test some error cases where LATERAL should have been used but wasn't
- select f1,g from int4_tbl a, generate_series(0, f1) g;
- select f1,g from int4_tbl a, generate_series(0, a.f1) g;
- select f1,g from int4_tbl a cross join generate_series(0, f1) g;
- select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
--- 986,991 ----
Re: LATERAL, UNNEST and spec compliance
* David Fetter (david@fetter.org) wrote:
As I see it, the current options are:
1. Do nothing, and insist on non-standard use of the LATERAL keyword.
I'm not a big fan of this. Providing a good error message saying "you
need to use LATERAL for this query to work" makes it slightly better,
but I don't feel like there's really any ambiguity here.
2. Add UNNEST to the grammar (or parse analysis) as a special case, making
it implicitly LATERAL.(This would make implementing S301 easier, but special cases are ugly.)
This I really don't like.
3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
(As far as I can tell, those cases whose behaviour would be changed by
this actually produce errors in versions prior to 9.3, so no working
code should be affected.)
+1 for me on this idea. If you're calling an SRF, passing in a lateral
value, 'LATERAL' seems like it's just a noise word, and apparently the
SQL authors felt the same, as they don't require it for unnest().
Since LATERAL is new in 9.3, I think the pros and cons of these choices
should be considered now, rather than being allowed to slide by unexamined.
I agree that we should really hammer this down before 9.3 is out the
door.
Thanks,
Stephen
Re: LATERAL, UNNEST and spec compliance
Stephen Frost <sfrost@snowman.net> writes:
* David Fetter (david@fetter.org) wrote:
3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
(As far as I can tell, those cases whose behaviour would be changed by
this actually produce errors in versions prior to 9.3, so no working
code should be affected.)
+1 for me on this idea. If you're calling an SRF, passing in a lateral
value, 'LATERAL' seems like it's just a noise word, and apparently the
SQL authors felt the same, as they don't require it for unnest().
At first I didn't like this idea, but it's growing on me.
However ... David is wrong to claim that it's zero-risk. It's true that
an SRF can't contain any side-references today, but it can contain an
outer reference. Consider a case like
SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)
In existing releases the "y" could be a valid outer reference to a.y.
If b also has a column y, David's proposal would cause us to prefer
that interpretation, since b.y would be more closely nested than a.y.
If you're lucky, you'd get a type-mismatch error, but if the two y's
are of similar datatypes the query would just silently do something
different than it used to.
This is a little bit far-fetched, but it could happen. As against that,
we make incompatible changes in every release, and it does seem like
assuming LATERAL for functions in FROM would be a usability gain most
of the time. And special-casing UNNEST to satisfy the standard seems
*really* ugly.
I agree that we should really hammer this down before 9.3 is out the
door.
Yeah, if we're going to do this it'd make the most sense to do it in the
same release that introduces LATERAL.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: LATERAL, UNNEST and spec compliance
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
However ... David is wrong to claim that it's zero-risk. It's true that
an SRF can't contain any side-references today, but it can contain an
outer reference. Consider a case likeSELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)
I see what you mean, but on the other hand, that looks like something we
might actually want to complain about as 'y' is pretty clearly ambiguous
here. I'm a bit surprised that doesn't already throw an error.
This is a little bit far-fetched, but it could happen. As against that,
we make incompatible changes in every release, and it does seem like
assuming LATERAL for functions in FROM would be a usability gain most
of the time. And special-casing UNNEST to satisfy the standard seems
*really* ugly.
It's definitely far-fetched, imv. If it's possible, within reason, to
explicitly throw a "please disambiguate 'y'" type of error in those
specific cases, that'd be nice, but I don't think it'd be required. A
mention in the release notes would be sufficient.
Thanks,
Stephen
Re: LATERAL, UNNEST and spec compliance
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)
Actually, this appears to fail already, at least in 9.2.2:
=> select * from (values (1)) v(a) where v.a in (select x from (values (2)) v2(a),
-> generate_series(1,a) x);
ERROR: function expression in FROM cannot refer to other relations of same query level
LINE 2: generate_series(1,a) x);
^
Unless it's something else that you were referring to...?
Thanks,
Stephen
Re: LATERAL, UNNEST and spec compliance
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)
Actually, this appears to fail already, at least in 9.2.2:
=> select * from (values (1)) v(a) where v.a in (select x from (values (2)) v2(a),
-> generate_series(1,a) x);
ERROR: function expression in FROM cannot refer to other relations of same query level
LINE 2: generate_series(1,a) x);
^
Huh ... you're right, I'd forgotten about that. That's an ancient bug
that got fixed in passing in the LATERAL work. So, as long as we're not
going to fix that bug in the back branches (which would be difficult
anyway IIRC), we don't have a compatibility problem ...
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: LATERAL, UNNEST and spec compliance
David Fetter <david@fetter.org> writes:
Please find attached a patch which implements approach 3. The vast
majority of it is changes to the regression tests. The removed
regression tests in join.{sql,out} are no longer errors, although some
of them are pretty standard DoS attacks, hence they're all removed.
Here's a less quick-hack-y approach to that.
regards, tom lane
Attachments:
implicit-lateral-1.patchtext/x-patch; charset=us-asciiDownload
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index bcee9468240e8a10b8e491a8f1ab8a1e2c5d9ede..caa9f1b3389e5ce57e2e50d13011e41c0ed3d11b 100644
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
*************** SELECT *
*** 717,730 ****
</indexterm>
<para>
! Subqueries and table functions appearing in <literal>FROM</> can be
preceded by the key word <literal>LATERAL</>. This allows them to
reference columns provided by preceding <literal>FROM</> items.
! (Without <literal>LATERAL</literal>, each <literal>FROM</> item is
evaluated independently and so cannot cross-reference any other
<literal>FROM</> item.)
A <literal>LATERAL</literal> item can appear at top level in the
! <literal>FROM</> list, or within a <literal>JOIN</> tree; in the latter
case it can also refer to any items that are on the left-hand side of a
<literal>JOIN</> that it is on the right-hand side of.
</para>
--- 717,740 ----
</indexterm>
<para>
! Subqueries appearing in <literal>FROM</> can be
preceded by the key word <literal>LATERAL</>. This allows them to
reference columns provided by preceding <literal>FROM</> items.
! (Without <literal>LATERAL</literal>, each subquery is
evaluated independently and so cannot cross-reference any other
<literal>FROM</> item.)
+ </para>
+
+ <para>
+ Table functions appearing in <literal>FROM</> can also be
+ preceded by the key word <literal>LATERAL</>, but for functions the
+ key word is optional; the function's arguments can contain references
+ to columns provided by preceding <literal>FROM</> items in any case.
+ </para>
+
+ <para>
A <literal>LATERAL</literal> item can appear at top level in the
! <literal>FROM</> list, or within a <literal>JOIN</> tree. In the latter
case it can also refer to any items that are on the left-hand side of a
<literal>JOIN</> that it is on the right-hand side of.
</para>
*************** FROM polygons p1 CROSS JOIN LATERAL vert
*** 770,776 ****
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
</programlisting>
! or in several other equivalent formulations.
</para>
<para>
--- 780,788 ----
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
</programlisting>
! or in several other equivalent formulations. (As already mentioned,
! the <literal>LATERAL</> key word is unnecessary in this example, but
! we use it for clarity.)
</para>
<para>
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 26d511fad8c5b8d02bda618006ce2606036db7c7..0f9d52753d832fa458aca563fa2bfcf558120818 100644
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
*************** TABLE [ ONLY ] <replaceable class="param
*** 504,521 ****
<varlistentry>
<term><literal>LATERAL</literal></term>
<listitem>
! <para>The <literal>LATERAL</literal> key word can precede a
! sub-<command>SELECT</command> or function-call <literal>FROM</>
! item. This allows the sub-<command>SELECT</command> or function
! expression to refer to columns of <literal>FROM</> items that appear
! before it in the <literal>FROM</> list. (Without
! <literal>LATERAL</literal>, each <literal>FROM</> item is evaluated
! independently and so cannot cross-reference any other
! <literal>FROM</> item.) A <literal>LATERAL</literal> item can
! appear at top level in the <literal>FROM</> list, or within a
! <literal>JOIN</> tree; in the latter case it can also refer to any
! items that are on the left-hand side of a <literal>JOIN</> that it is
! on the right-hand side of.
</para>
<para>
--- 504,531 ----
<varlistentry>
<term><literal>LATERAL</literal></term>
<listitem>
! <para>
! The <literal>LATERAL</literal> key word can precede a
! sub-<command>SELECT</command> <literal>FROM</> item. This allows the
! sub-<command>SELECT</command> to refer to columns of <literal>FROM</>
! items that appear before it in the <literal>FROM</> list. (Without
! <literal>LATERAL</literal>, each sub-<command>SELECT</command> is
! evaluated independently and so cannot cross-reference any other
! <literal>FROM</> item.)
! </para>
!
! <para>
! <literal>LATERAL</literal> can also precede a function-call
! <literal>FROM</> item, but in this case it is a noise word, because
! the function expression can refer to earlier <literal>FROM</> items
! in any case.
! </para>
!
! <para>
! A <literal>LATERAL</literal> item can appear at top level in the
! <literal>FROM</> list, or within a <literal>JOIN</> tree. In the
! latter case it can also refer to any items that are on the left-hand
! side of a <literal>JOIN</> that it is on the right-hand side of.
</para>
<para>
*************** SELECT distributors.* WHERE distributors
*** 1738,1744 ****
sub-<command>SELECT</command>; that is, the syntax
<literal>FROM <replaceable>func</>(...) <replaceable>alias</></literal>
is approximately equivalent to
! <literal>FROM (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.
</para>
</refsect2>
--- 1748,1759 ----
sub-<command>SELECT</command>; that is, the syntax
<literal>FROM <replaceable>func</>(...) <replaceable>alias</></literal>
is approximately equivalent to
! <literal>FROM LATERAL (SELECT <replaceable>func</>(...)) <replaceable>alias</></literal>.
! Note that <literal>LATERAL</> is considered to be implicit; this is
! because the standard requires <literal>LATERAL</> semantics for an
! <literal>UNNEST()</> item in <literal>FROM</>.
! <productname>PostgreSQL</productname> treats <literal>UNNEST()</> the
! same as other set-returning functions.
</para>
</refsect2>
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index dd78500aa9328bbf8ed29d2ef6388cdc201eb03f..b9655954cde32d9525e971b61a668822be040b49 100644
*** a/src/backend/parser/parse_clause.c
--- b/src/backend/parser/parse_clause.c
*************** transformRangeFunction(ParseState *pstat
*** 503,508 ****
--- 503,509 ----
{
Node *funcexpr;
char *funcname;
+ bool is_lateral;
RangeTblEntry *rte;
/*
*************** transformRangeFunction(ParseState *pstat
*** 514,525 ****
funcname = FigureColname(r->funccallnode);
/*
! * If the function is LATERAL, make lateral_only names of this level
! * visible to it. (LATERAL can't nest within a single pstate level, so we
! * don't need save/restore logic here.)
*/
Assert(!pstate->p_lateral_active);
! pstate->p_lateral_active = r->lateral;
/*
* Transform the raw expression.
--- 515,530 ----
funcname = FigureColname(r->funccallnode);
/*
! * We make lateral_only names of this level visible, whether or not the
! * function is explicitly marked LATERAL. This is needed for SQL spec
! * compliance in the case of UNNEST(), and seems useful on convenience
! * grounds for all functions in FROM.
! *
! * (LATERAL can't nest within a single pstate level, so we don't need
! * save/restore logic here.)
*/
Assert(!pstate->p_lateral_active);
! pstate->p_lateral_active = true;
/*
* Transform the raw expression.
*************** transformRangeFunction(ParseState *pstat
*** 534,543 ****
assign_expr_collations(pstate, funcexpr);
/*
* OK, build an RTE for the function.
*/
rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
! r, r->lateral, true);
/*
* If a coldeflist was supplied, ensure it defines a legal set of names
--- 539,554 ----
assign_expr_collations(pstate, funcexpr);
/*
+ * Mark the RTE as LATERAL if the user said LATERAL explicitly, or if
+ * there are any lateral cross-references in it.
+ */
+ is_lateral = r->lateral || contain_vars_of_level(funcexpr, 0);
+
+ /*
* OK, build an RTE for the function.
*/
rte = addRangeTableEntryForFunction(pstate, funcname, funcexpr,
! r, is_lateral, true);
/*
* If a coldeflist was supplied, ensure it defines a legal set of names
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 22265d7a7c88fa3e331588dbc1bbea0803155fbc..3421a559f25e2d59a7e715db28a1bff03a81af4c 100644
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
*************** select *, (select r from (select q1 as q
*** 3157,3163 ****
4567890123456789 | -4567890123456789 | 4567890123456789
(5 rows)
! -- lateral SRF
select count(*) from tenk1 a, lateral generate_series(1,two) g;
count
-------
--- 3157,3163 ----
4567890123456789 | -4567890123456789 | 4567890123456789
(5 rows)
! -- lateral with function in FROM
select count(*) from tenk1 a, lateral generate_series(1,two) g;
count
-------
*************** explain (costs off)
*** 3184,3189 ****
--- 3184,3200 ----
-> Function Scan on generate_series g
(4 rows)
+ -- don't need the explicit LATERAL keyword for functions
+ explain (costs off)
+ select count(*) from tenk1 a, generate_series(1,two) g;
+ QUERY PLAN
+ ------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Seq Scan on tenk1 a
+ -> Function Scan on generate_series g
+ (4 rows)
+
-- lateral with UNION ALL subselect
explain (costs off)
select * from generate_series(100,200) g,
*************** select * from
*** 3578,3602 ****
(26 rows)
-- test some error cases where LATERAL should have been used but wasn't
! select f1,g from int4_tbl a, generate_series(0, f1) g;
ERROR: column "f1" does not exist
! LINE 1: select f1,g from int4_tbl a, generate_series(0, f1) g;
! ^
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a, generate_series(0, a.f1) g;
ERROR: invalid reference to FROM-clause entry for table "a"
! LINE 1: select f1,g from int4_tbl a, generate_series(0, a.f1) g;
! ^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a cross join generate_series(0, f1) g;
ERROR: column "f1" does not exist
! LINE 1: ...ct f1,g from int4_tbl a cross join generate_series(0, f1) g;
! ^
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
ERROR: invalid reference to FROM-clause entry for table "a"
! LINE 1: ... f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
! ^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
--- 3589,3613 ----
(26 rows)
-- test some error cases where LATERAL should have been used but wasn't
! select f1,g from int4_tbl a, (select f1 as g) ss;
ERROR: column "f1" does not exist
! LINE 1: select f1,g from int4_tbl a, (select f1 as g) ss;
! ^
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a, (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
! LINE 1: select f1,g from int4_tbl a, (select a.f1 as g) ss;
! ^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a cross join (select f1 as g) ss;
ERROR: column "f1" does not exist
! LINE 1: select f1,g from int4_tbl a cross join (select f1 as g) ss;
! ^
HINT: There is a column named "f1" in table "a", but it cannot be referenced from this part of the query.
! select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
ERROR: invalid reference to FROM-clause entry for table "a"
! LINE 1: select f1,g from int4_tbl a cross join (select a.f1 as g) ss...
! ^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 0fe8ca4c4e927da12cfe9ae75c9479b3ed8c4c22..16782776f45222b9b9ad75c5c776993baf5554dc 100644
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
*************** INSERT INTO foo2 VALUES(1, 11);
*** 19,30 ****
INSERT INTO foo2 VALUES(2, 22);
INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
! -- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ERROR: invalid reference to FROM-clause entry for table "foo2"
! LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! ^
! HINT: There is an entry for table "foo2", but it cannot be referenced from this part of the query.
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
fooid | f2
--- 19,33 ----
INSERT INTO foo2 VALUES(2, 22);
INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
! -- function with implicit LATERAL
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
! fooid | f2 | fooid | f2
! -------+-----+-------+-----
! 1 | 11 | 1 | 11
! 2 | 22 | 2 | 22
! 1 | 111 | 1 | 111
! (3 rows)
!
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
fooid | f2
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 6c1e3394adca36c837bff3d20f62602fafd70f5b..6f51b8532763eb2bddde7872be2ce2874e51240b 100644
*** a/src/test/regress/sql/join.sql
--- b/src/test/regress/sql/join.sql
*************** explain (costs off)
*** 901,912 ****
select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl;
! -- lateral SRF
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
-- lateral with UNION ALL subselect
explain (costs off)
--- 901,915 ----
select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl;
! -- lateral with function in FROM
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
+ -- don't need the explicit LATERAL keyword for functions
+ explain (costs off)
+ select count(*) from tenk1 a, generate_series(1,two) g;
-- lateral with UNION ALL subselect
explain (costs off)
*************** select * from
*** 987,996 ****
lateral (select ss2.y) ss3;
-- test some error cases where LATERAL should have been used but wasn't
! select f1,g from int4_tbl a, generate_series(0, f1) g;
! select f1,g from int4_tbl a, generate_series(0, a.f1) g;
! select f1,g from int4_tbl a cross join generate_series(0, f1) g;
! select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
--- 990,999 ----
lateral (select ss2.y) ss3;
-- test some error cases where LATERAL should have been used but wasn't
! select f1,g from int4_tbl a, (select f1 as g) ss;
! select f1,g from int4_tbl a, (select a.f1 as g) ss;
! select f1,g from int4_tbl a cross join (select f1 as g) ss;
! select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql
index 54cfc178c057c40f82e27cbd1f3a8e0e271c34b3..f1a405a5f7eb5716d66dd815b0908d8e3accc95a 100644
*** a/src/test/regress/sql/rangefuncs.sql
--- b/src/test/regress/sql/rangefuncs.sql
*************** INSERT INTO foo2 VALUES(1, 111);
*** 7,13 ****
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
! -- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
-- function in subselect
--- 7,13 ----
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
! -- function with implicit LATERAL
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
-- function in subselect