Thread: sql or pgsql question, accessing a created value
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... It's probably a compile-time run-time sort of chicken and egg thing. ;) So I' off onto pl/pgsql, but still not having much luck. Full under construction sql right now is: create or replace function load_air_temp_summary() returns void as $$ declare solarnoon timestamp; solardiff interval; BEGIN select count(*) from ( select aird.current_temp, aird.minimum_temp, aird.measured_at, subd.datum_id, subd.datum_type, subm.person_id, subm.site_id, loc.latitude, loc.longitude, select solarnoon( aird.measured_at, loc.longitude ) INTO solarnoon <-- ** trying to save the value from air_temp_data aird, submission_data subd, submissions subm, sites sites, locations loc where subd.datum_type = 'AirTempDatum' and subd.datum_id = aird.id and subd.submission_id = subm.id and subm.site_id = sites.id and loc.locatable_type = 'Site' and sites.id = loc.locatable_id ) as fred; END $$ LANGUAGE plpgsql; but it dislikes the third "select" stmt, or if I remove that select stmt, I get ERROR: syntax error at or near "(" LINE 1: ...d, subm.site_id, loc.latitude, loc.longitude, $1 ( aird.mea... Any tips or tricks on how I should approach this are appreciated. How do I store and use values that are calculated on the fly. -ds
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... It's probably a compile-time run-time sort of chicken and egg thing. ;) >>>>>>>>>>>>>>>>>>>>>>>>> It is. You need to use sub-selects. SELECT solarnoon, solardiff, CASE... AS within_solar_hour FROM SELECT solarnoon, func() AS solardiff FROM ( SELECT func() AS solarnoon ) AS sn -- close solarnoon from ) AS sd -- close solardiff from David J.
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@iol.ie