pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsub query and AS
3+ messages / 3 participants
[nested] [flat]
* sub query and AS
@ 2012-05-23 09:24 Ferruccio Zamuner <nonsolosoft@diff.org>
2012-05-23 19:07 ` Re: sub query and AS Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ferruccio Zamuner @ 2012-05-23 09:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sub query and AS
2012-05-23 09:24 sub query and AS Ferruccio Zamuner <nonsolosoft@diff.org>
@ 2012-05-23 19:07 ` Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
2012-05-24 07:07 ` Re: sub query and AS Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 3+ messages in thread
From: Lee Hachadoorian @ 2012-05-23 19:07 UTC (permalink / raw)
To: Ferruccio Zamuner <nonsolosoft@diff.org>; +Cc: pgsql-sql
On Wed, May 23, 2012 at 5:24 AM, Ferruccio Zamuner <nonsolosoft@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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: sub query and AS
2012-05-23 09:24 sub query and AS Ferruccio Zamuner <nonsolosoft@diff.org>
2012-05-23 19:07 ` Re: sub query and AS Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
@ 2012-05-24 07:07 ` Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 3+ messages in thread
From: Samuel Gendler @ 2012-05-24 07:07 UTC (permalink / raw)
To: Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>; +Cc: Ferruccio Zamuner <nonsolosoft@diff.org>; pgsql-sql
On Wed, May 23, 2012 at 12:07 PM, Lee Hachadoorian <
Lee.Hachadoorian+L@gmail.com> wrote:
> On Wed, May 23, 2012 at 5:24 AM, Ferruccio Zamuner <nonsolosoft@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.
>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-05-24 07:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-23 09:24 sub query and AS Ferruccio Zamuner <nonsolosoft@diff.org>
2012-05-23 19:07 ` Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
2012-05-24 07:07 ` Samuel Gendler <sgendler@ideasculptor.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox