On Wed, May 23, 2012 at 5:24 AM, Ferruccio Zamuner <nonsolos...@diff.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/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
