Skip site navigation (1) Skip section navigation (2)

Re: sub query and AS

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 (view raw or flat)
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/

In response to

Responses

pgsql-sql by date

Next:From: Jasen BettsDate: 2012-05-24 06:09:32
Subject: Re: Understanding Binary Data Type
Previous:From: Oliveiros d'Azevedo CristinaDate: 2012-05-23 10:41:04
Subject: Re: left outer join only select newest record

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group