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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
