From: Michael Burke <michael@engtech.ca>
To: pgsql-sql@postgresql.org
Subject: Using calculated columns as arguments in same SELECT
Date: Thu, 16 Feb 2006 11:07:50 -0400
Message-ID: <200602161107.50608.michael@engtech.ca> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michael@engtech.ca
Subject: Re: Using calculated columns as arguments in same SELECT
In-Reply-To: <200602161107.50608.michael@engtech.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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