From: | Ferruccio Zamuner <nonsolosoft(at)diff(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | sub query and AS |
Date: | 2012-05-23 09:24:07 |
Message-ID: | 4FBCACB7.9070501@diff.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I like PostgreSQL for many reasons, one of them is the possibility to
use sub query everywhere. Now I've found where it doesn't support them.
I would like to use a AS (sub query) form.
This is an example:
First the subquery:
select substr(descr, 7, length(descr)-8)
from (select string_agg('" int,"',freephone) as descr
from (select distinct freephone
from calendario order by 1
) as a
) as b;
substr
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
"800900420" int,"800900450" int,"800900480" int,"800900570"
int,"800900590" int,"800900622" int,"800900630" int,"800900644"
int,"800900688" int,"800900950" int
(1 row)
Then the wishing one:
itv2=#
select *
FROM crosstab('select uscita,freephone,id from calendario order by
1','select distinct freephone from calendario order by 1')
-- following AS fails
AS (select 'uscita int, ' || substr(descr, 7, length(descr)-8)
from (select string_agg('" int,"',freephone) as descr
from (select distinct freephone
from calendario order by 1) as a
) as b;
);
ERROR: syntax error at or near "select"
LINE 4: ...stinct freephone from calendario order by 1') as (select 'us...
More is on http://paste.scsys.co.uk/198877
I think that AS must evaluate the sub query in advance.
It could be possible to have such behavior?
Best regards, \ferz
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2012-05-23 09:27:39 | left outer join only select newest record |
Previous Message | Carlos Mennens | 2012-05-22 17:13:12 | Understanding Binary Data Type |