[postgis-users] pl/pgsql question: dynamic access to record.column

[Dylan Beaudette]

On Wednesday 10 January 2007 13:10, Mark Cave-Ayland wrote:
> On Wed, 2007-01-10 at 12:24 -0800, Dylan Beaudette wrote:
> > I am learning how to use PL/PGSQL and have come upon something that I
> > just don't seem to be able to solve on my own.
> >
> > I have a function, which takes a column name as an argument. A FOR loop
> > iterates over rows, using a RECORD datatype to store each iteration of
> > the loop.
> >
> > If I hard-code in a column name into some calculation, things work fine.
> > However, I cannot seem to figure out how to _dynamically_ access a column
> > from a RECORD datatype:
> >
> > -- this works fine : 'field_pct_clay' is a hard-coded column name
> > DEFINE
> > hz_record RECORD;
> > wt_column FLOAT;
> > thick FLOAT;
> > ...
> > wt_column := wt_column + (hz_record.field_pct_clay * thick) ;
> >
> > -- this does not work: 'the_column' is an argument to the function being
> > defined:
> >
> > wt_column := wt_column + (hz_record.the_column * thick) ;
> >                                                             ^^^^^^^^^^^^^
> >
> > obviously, the interpreter tells me that there is no column named
> > 'the_column' in the RECORD iterator...
> >
> > Searching on google, it seems that a dynamic expression like this needs
> > to be evaluated with the EXECUTE keyword.... However something like this
> > does not work:
> >
> > wt_column := EXECUTE 'wt_column + (hz_record.' || quote_ident(the_column)
> > || ' * thick)' ;
> >
> > any tips would be a great help!
> >
> > thanks!
>
> Hi Dylan,
>
> You'll need to post the entire stored procedure for this one so that we
> can tell the difference between PL/PGSQL variables and column names.
>
>
> Kind regards,
>
> Mark.

Thanks Mark, please find the entire procedure below:

-------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION depth_wt_avg (
	the_pedon_id varchar, top_lim numeric, bottom_lim numeric, the_column 
varchar )
	RETURNS FLOAT as $$
	DECLARE

-- horizon variables, use a record datatype
	hz_record RECORD;
	
	thick FLOAT :=0 ;
	thick_total FLOAT :=0 ;
	
	wt_column FLOAT :=0 ;
	wt_column_avg FLOAT :=0 ;
	
	
--	flags detecting when the defined limits intersects a horizon
	intersects_top_flag integer ;
	intersects_bottom_flag integer ;
	
	BEGIN
		
		FOR hz_record in 
			EXECUTE 'select top, bottom, ' 
			|| quote_ident(the_column)
			|| ' from horizon '
			|| ' where pedon_id = '
			|| quote_literal(the_pedon_id) 
			|| ' and ( bottom >= '
			|| top_lim 
			|| ' and top <= '
			|| bottom_lim
			|| ' ) '
			|| ' and '
			|| quote_ident(the_column)
			|| ' is not null'
			|| ' order by top '
			LOOP 
			
-- 			use something like this for each horizon, to create 
PCS_intersects_top_flag, PCS_intersects_bottom_flag
 			select into intersects_top_flag
				CASE WHEN top_lim > hz_record.top and top_lim < hz_record.bottom THEN 1 
ELSE 0 END ;

 			select into intersects_bottom_flag
				CASE WHEN bottom_lim > hz_record.top and bottom_lim < hz_record.bottom 
THEN 1 ELSE 0 END ;			
			
-- 			if the PCS does not overlap this horizon
			thick := hz_record.bottom - hz_record.top ;

-- 			else use the top of the PCS instead			
			IF intersects_top_flag = 1 THEN
				thick := hz_record.bottom - top_lim ;
			END IF;
			
-- 			else if we are at the last horizon use the bottom of the PCS as the 
bottom cutoff
			IF intersects_bottom_flag = 1 THEN
				thick := bottom_lim - hz_record.top ;
			END IF;
			
--			sum the horizon thickness within the PCS 
			thick_total := thick_total + thick;

--			the weighted clay percent, and frags pct
			EXECUTE 'select ' || wt_column || ' + (' || 'hz_record' || '.' || 
quote_ident(the_column) || ' * ' || thick || ')' into wt_column;
			
--			the weighted-avg clay percent and frags within the PCS
			wt_column_avg := wt_column / thick_total;
			
			
		END LOOP;
		
		RETURN round(wt_column_avg::numeric, 1) ;
		
	END
$$ LANGUAGE plpgsql;

-------------------------------------------------------------------------------------
-- 
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341