[GENERAL] sql or pgsql question, accessing a created value

2011-07-11 Thread David Salisbury 


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

Re: [GENERAL] sql or pgsql question, accessing a created value

2011-07-11 Thread David Johnston 

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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] sql or pgsql question, accessing a created value

2011-07-11 Thread Raymond O'Donnell 

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
r...@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general