From: | Lee Hachadoorian <Lee(dot)Hachadoorian+L(at)gmail(dot)com> |
---|---|
To: | Ferruccio Zamuner <nonsolosoft(at)diff(dot)org> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: sub query and AS |
Date: | 2012-05-23 19:07:33 |
Message-ID: | CANnCtnKHpXuZOCZYhqQ9WJv9FLNHADLLQ2t+tuv+aey_RZy0rQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, May 23, 2012 at 5:24 AM, Ferruccio Zamuner <nonsolosoft(at)diff(dot)org> wrote:
> 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
Ferrucio,
The problem is that you are attempting to use a "subquery" to generate
SQL that will be evaluated by the main query. This won't work the same
way that
SELECT (SELECT 'column_name') FROM some_table;
wouldn't work.
If you want to dynamically generate the SQL this way you will have to
create a function or use the DO statement (Postgres 9.0+). It would
look something like this (not tested):
DO $do$
DECLARE
sql text;
output_columns text;
BEGIN
select 'uscita int, ' || substr(descr, 7, length(descr)-8) INTO output_columns
from (select string_agg('" int,"',freephone) as descr
from (select distinct freephone
from calendario order by 1) as a
) as b;
sql := $$select *
FROM crosstab('select uscita,freephone,id from calendario order by
1','select distinct freephone from calendario order by 1')
AS pivot ($$ || output_columns || $$);$$;
EXECUTE sql;
END$do$;
If you are using Postgres <9.0 and don't have access to the DO
statement, you'll have to stick the above into a plpgsql function.
Hope this helps,
--Lee
--
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu/
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2012-05-24 06:09:32 | Re: Understanding Binary Data Type |
Previous Message | Oliveiros d'Azevedo Cristina | 2012-05-23 10:41:04 | Re: left outer join only select newest record |