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

Using calculated columns as arguments in same SELECT

From: Michael Burke <michael(at)engtech(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: Using calculated columns as arguments in same SELECT
Date: 2006-02-16 15:07:50
Message-ID: 200602161107.50608.michael@engtech.ca (view raw or flat)
Thread:
Lists: 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(at)engtech(dot)ca  1 (902) 628-1705

Responses

pgsql-sql by date

Next:From: Michael BurkeDate: 2006-02-16 15:42:05
Subject: Re: Using calculated columns as arguments in same SELECT
Previous:From: Tom LaneDate: 2006-02-16 15:05:15
Subject: Re: VIEWs and TEMP tables problem

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