Re: sub query and AS

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/