Re: Using relations in the SELECT part

Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org> writes:
> the solution I found is:
> postgres=# select key,
> unnest(regexp_split_to_array(plates, E'\\s+')) AS plate from t;

> 1. why may I put in the SELECT part (instead of the FROM) a relation?
> When I studied SQL, I was told to put all relations in FROM, and put in
> the SELECT part only the colmns or expressions with columns for
> formatting the output.

If you don't like it, don't do it ;-). A more theoretically pure approach
is

select key, plate
from t,
lateral unnest(regexp_split_to_array(plates, E'\\s+')) as plate;

although really you chose the wrong regexp function and should have used

select key, plate
from t,
lateral regexp_split_to_table(plates, E'\\s+') as plate;

Also, the keyword "lateral" is optional here per SQL standard, although
I think it's better to include it to make it clearer what's happening.

The fact that Postgres allows set-returning functions in the
SELECT targetlist is a hangover from Berkeley QUEL, which at this
point we sort of regret not having ripped out twenty years ago;
it's a real wart both semantically and implementation-wise. But it's
hard to get rid of such things. Putting a set-returning function
in LATERAL is cleaner and more standards-compliant, though.

> 2. why postgresql create a cartesian product using a first element (a
> single columns "key") and a second element (a relation "plate")?

It's not really very different from what happens with LATERAL, at least
for the case with just one SRF in the targetlist.

> Furthermore, let's assume postgres does a cartesian product, if I add a
> new relation as third element, does it create 4x3 product?

You've hit on the reason why it's semantically ugly: it's not very
clear what to do with multiple SRFs in one targetlist. LATERAL,
together with the ROWS FROM construct, allows clear specification
of both of the useful behaviors (cartesian product and eval-set-
returning-functions-in-lockstep). The multiple-SRFs-in-targetlist
behavior that we inherited from Berkeley is just a mess, as it
effectively runs the SRFs until reaching the least common multiple of
their periods. We're changing that for v10 though. You might find
this commit informative (at least the commit message and documentation
changes):

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=69f4b9c85

regards, tom lane