Re: sub query and AS

On Wed, May 23, 2012 at 12:07 PM, Lee Hachadoorian <
Lee(dot)Hachadoorian+L(at)gmail(dot)com> wrote:

> 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.
>

If that works, that's actually a pretty cute trick for generating the
column names for that generalized version of the crosstab() function
without having to do it on the client-side in a serialized transaction or
risking a different set of columns in the function call compared to when
the client issued the same query in order to get the column list. I don't
imagine that it closes the race condition entirely but it would sure make
it smaller, for those who don't set the transaction isolation level
correctly.

You should stick it in the annotated version of the documentation on the
page that describes the tablefunc functions. Or maybe it is there in
recent versions of the page. When I last looked at those docs, there was
no mention of it that I can remember.

--sam