pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Using calculated columns as arguments in same SELECT
2+ messages / 1 participants
[nested] [flat]

* Using calculated columns as arguments in same SELECT
@ 2006-02-16 15:07  Michael Burke <michael@engtech.ca>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael Burke @ 2006-02-16 15:07 UTC (permalink / raw)
  To: pgsql-sql

Hello,

I am looking to simplify this query (uses PostGIS, but I have encountered this 
scenario with other chains of functions):

gtest=# SELECT X(
  SetSRID(
    Transform(
      GeomFromText(
        'POINT(142512 1020225)', 26910
      ), 4326
    ),
  -1)
) as xcoord, Y(
  SetSRID(
    Transform(
      GeomFromText(
        'POINT(142512 1020225)', 26910
      ), 4326
    ),
  -1)
) AS ycoord;

This SELECT works, but the rather long arguments to X(geom) and Y(geom) are 
the same.  Is it possible and feasible to pre-calculate the argument, such 
as:

gtest=# SELECT 
  SetSRID(
    Transform(
      GeomFromText(
        'POINT(142512 1020225)', 26910
      ), 4326
    ),
  -1) AS transformed_geom,
  X(transformed_geom) AS xcoord,
  Y(transformed_geom) AS ycoord

Where I don't really care about transformed_geom being returned, but it saves 
double-calling the inside functions  This doesn't work -- it complains that 
transformed_geom is not a column.

SELECT version(); gives:
PostgreSQL 8.0.6 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2 
[FreeBSD] 20040728

Thanks in advance!
Mike.

-- 
Michael Burke
Engineering Technologies Canada Ltd.
michael@engtech.ca  1 (902) 628-1705



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Using calculated columns as arguments in same SELECT
@ 2006-02-16 15:42  Michael Burke <michael@engtech.ca>
  parent: Michael Burke <michael@engtech.ca>
  0 siblings, 0 replies; 2+ messages in thread

From: Michael Burke @ 2006-02-16 15:42 UTC (permalink / raw)
  To: pgsql-sql

On February 16, 2006 11:07 am, Michael Burke wrote:
> Hello,
>
> I am looking to simplify this query (uses PostGIS, but I have encountered
> this scenario with other chains of functions):
>
-- snip --

Immediately after sending this one, I realized I could do it with a 
sub-select:

gtest=# SELECT X(SubSel.transformed_geom), Y(SubSel.transformed_geom) FROM (
  SELECT SetSRID(
    Transform(
      GeomFromText(
        'POINT(142512 1020225)', 26910
      ), 4326
    ),
  -1) AS transformed_geom) SubSel;

This works fine.
Mike.

-- 
Michael Burke
Engineering Technologies Canada Ltd.
michael@engtech.ca  1 (902) 628-1705



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-02-16 15:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-16 15:07 Using calculated columns as arguments in same SELECT Michael Burke <michael@engtech.ca>
2006-02-16 15:42 ` Michael Burke <michael@engtech.ca>

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