Feature request: (VALUES (1), (2), (3)) AS t(n)

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Feature request: (VALUES (1), (2), (3)) AS t(n)

Ainar Garipov
Good day!  It's my first time writing to a mail list for a very long
time, so I may not get this right the first time.

I would like to request the ability to write:

  SELECT *
    FROM (VALUES (1, 1), (2, 2)) AS t(a, b)
  ;

Instead of:

  SELECT t.column1 AS a, t.column2 AS b
    FROM (VALUES (1, 1), (2, 2)) AS t
  ;

That is, support column names in AS aliases.  Currently I can do that in
PostgreSQL but not in SQLite.  The latter gives me this error:

  Error: near "(": syntax error
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: (VALUES (1), (2), (3)) AS t(n)

Keith Medcalf
How about something like:

with t(a, b) as (values (1, 1), (2, 2)) select a, b from t;

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Ainar Garipov
>Sent: Thursday, 7 November, 2019 13:44
>To: [hidden email]
>Subject: [sqlite] Feature request: (VALUES (1), (2), (3)) AS t(n)
>
>Good day!  It's my first time writing to a mail list for a very long
>time, so I may not get this right the first time.
>
>I would like to request the ability to write:
>
>  SELECT *
>    FROM (VALUES (1, 1), (2, 2)) AS t(a, b)
>  ;
>
>Instead of:
>
>  SELECT t.column1 AS a, t.column2 AS b
>    FROM (VALUES (1, 1), (2, 2)) AS t
>  ;
>
>That is, support column names in AS aliases.  Currently I can do that in
>PostgreSQL but not in SQLite.  The latter gives me this error:
>
>  Error: near "(": syntax error
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: (VALUES (1), (2), (3)) AS t(n)

Ainar Garipov
(Hopefully this works.)

> How about something like:
>
> with t(a, b) as (values (1, 1), (2, 2)) select a, b from t;

Yeah, CTEs are an obvious alternative.  I mostly request this AS t(n)
feature because I have had some otherwise-portable PostgreSQL queries
that I needed to tweak for SQLite.  The thing with PostgreSQL is that
until very recently CTEs would generally perform worse than sub-queries,
so a lot of people avoided it.  My SQLite version used CTEs with no
issues, but still I thought I might as well request the feature, to
improve compatibility.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users