Re: subselect in the column list

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-hackers(at)postgresql(dot)org>,"amit sehas" <cun23(at)yahoo(dot)com>
Subject: Re: subselect in the column list
Date: 2012-03-13 15:11:16
Message-ID: 4F5F1D4402000025000461FC@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

amit sehas <cun23(at)yahoo(dot)com> wrote:
> If we have a query of the form:
>
> Select *, (Select * FROM T2 WHERE p2 = T1.p1) FROM T1 ORDER
> BY 1 WHERE p3 = 75
>
> In SQL, if a subselect is present in the column-list, is the
> result set of this subselect considered to be a part of a
> single tuple returned from the outer query, or does the result
> set of the whole query look like a cross product of results
> of outer and inner query

I very much doubt that the above query conforms to the SQL standard.
It doesn't take much effort to find out what PostgreSQL does with
it, if that's what you want to know:

test=# create table t1 (id1 int, val1 text);
CREATE TABLE
test=# create table t2 (id2 int, val2 text);
CREATE TABLE
test=# insert into t1 values (1,'one'),(2,'two');
INSERT 0 2
test=# insert into t2 values (1,'einz'),(3,'drei');
INSERT 0 2
test=# select *, (select * from t2 where id2 = id1) from t1;
ERROR: subquery must return only one column
LINE 1: select *, (select * from t2 where id2 = id1) from t1;
^

Now, if your goal is to get the value of the row from the subquery
as a RECORD, that can be done; every table has a RECORD definition
with the same name:

test=# select *, (select t2 from t2 where id2 = id1) from t1;
id1 | val1 | t2
-----+------+----------
1 | one | (1,einz)
2 | two |
(2 rows)

Not only does such a subquery need to be limited to a single column
(in this example a column of type RECORD), but it must only return
one row; otherwise you get a different error:

test=# insert into t2 values (1,'uno');
INSERT 0 1
test=# select *, (select t2 from t2 where id2 = id1) from t1;
ERROR: more than one row returned by a subquery used as an
expression

The pgsql-hackers list is intended for discussing the development of
the PostgreSQL product, not for questions about how to use it. The
question would probably have been more appropriate on pgsql-general.

-Kevin

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2012-03-13 15:14:06 Re: Command Triggers, patch v11
Previous Message Alvaro Herrera 2012-03-13 15:08:41 Re: pg_upgrade and statistics