Message-ID: <4FBCACB7.9070501@diff.org>
Date: Wed, 23 May 2012 11:24:07 +0200
From: Ferruccio Zamuner <nonsolosoft@diff.org>
Organization: NonSoLoSoft
User-Agent: Mozilla/5.0 (X11; DragonFly i386;
	rv:8.0) Gecko/20120116 Thunderbird/8.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: sub query and AS
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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