Making the subquery alias optional in the FROM clause

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Making the subquery alias optional in the FROM clause
Date: 2022-06-27 13:49:20
Message-ID: CAEZATCUCGCf82=hxd9N5n6xGHPyYpQnxW8HneeH+uP7yNALkWA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

This was discussed previously in [1], and there seemed to be general
consensus in favour of it, but no new patch emerged.

Attached is a patch that takes the approach of not generating an alias
at all, which seems to be neater and simpler, and less code than
trying to generate a unique alias.

It still generates an eref for the subquery RTE, which has a made-up
relation name, but that is marked as not visible on the
ParseNamespaceItem, so it doesn't conflict with anything else, need
not be unique, and cannot be used for qualified references to the
subquery's columns.

The only place that exposes the eref's made-up relation name is the
existing query deparsing code in ruleutils.c, which uniquifies it and
generates SQL spec-compliant output. For example:

CREATE OR REPLACE VIEW test_view AS
SELECT *
FROM (SELECT a, b FROM foo),
(SELECT c, d FROM bar)
WHERE a = c;

\sv test_view

CREATE OR REPLACE VIEW public.test_view AS
SELECT subquery.a,
subquery.b,
subquery_1.c,
subquery_1.d
FROM ( SELECT foo.a,
foo.b
FROM foo) subquery,
( SELECT bar.c,
bar.d
FROM bar) subquery_1
WHERE subquery.a = subquery_1.c

Regards,
Dean

[1] https://www.postgresql.org/message-id/flat/1487773980.3143.15.camel%40oopsware.de

Attachment Content-Type Size
make-subquery-alias-optional.patch text/x-patch 15.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Geier 2022-06-27 14:55:55 Re: Lazy JIT IR code generation to increase JIT speed with partitions
Previous Message vignesh C 2022-06-27 13:38:44 Re: Handle infinite recursion in logical replication setup