amit sehas <cun23@yahoo.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