Re: sql or pgsql question, accessing a created value

On 11/07/2011 20:19, David Salisbury wrote:
>
> Hope someone's out there for this one. Basically I'm creating a summary
> table of many
> underlying tables in one select statement ( though that may have to
> change ). My problem
> can be shown in this example..
>
> select my_function( timeofmeasurement, longitude ) as solarnoon,
> extract(epoch from ( timeofmeasurement - solarnoon ) as solardiff
> ( case when solardiff < 3600 then 'Y' else 'N' end ) as within_solar_hour
> from
> my_table;
>
> But I get an error along the lines of
> ERROR: column "solarnoon" does not exist
> LINE 8: extract(epoch from (timeofmeasurement - solarnoon) ) as sola...
>

One (slightly messy) way to do that is create another, outer layer of
SELECT - so your on-the-fly calculations are executed in the sub-select,
and the values are then available to the outer select. You have three
levels of dependency, so you'll need two subqueries:

<not tested>

select
x.solarnoon,
x.solardiff,
(case when x.solardiff < 3600 then 'Y' else 'N' end) as
within_solar_hour
from (
select
extract(epoch from (y.timeofmeasurement - y.solarnoon) as solardiff,
y.timeofmeasurement
from (
select
my_function(timeofmeasurement, longitude) as solarnoon,
timeofmeasurement
from
my_table
) y
) x;

</not tested>

I think you can also do it more elegantly with a CTE; not something I've
played with yet, but you can read about it here:

http://www.postgresql.org/docs/9.0/static/queries-with.html

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie