[vox-tech] Re: vox-tech Digest, Vol 32, Issue 9

[Kevin Hrim]

The "EXECUTE" command is typically used on complete sql expressions.
(the information is parsed, and passed to the database.)

my_command := 'select count(*) from my_table'
EXECUTE my_command;

So if you want to dynamically select a column.

my_column := 'DUMMY'
my_command := 'select '||my_column||' from my_table;'
EXECUTE my_command;

Be careful using dynamic sql on web pages.  The unchecked
concatenation of strings can lead to "SQL Injection" vulnerabilities.

Kevin

On 1/10/07, vox-tech-request at lists.lugod.org
<vox-tech-request at lists.lugod.org> wrote:
> Send vox-tech mailing list submissions to
>         vox-tech at lists.lugod.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>         http://lists.lugod.org/mailman/listinfo/vox-tech
> or, via email, send a message with subject or body 'help' to
>         vox-tech-request at lists.lugod.org
>
> You can reach the person managing the list at
>         vox-tech-owner at lists.lugod.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of vox-tech digest..."
>
>
> Today's Topics:
>
>    1. pl/pgsql question: dynamic access to record.column
>       (Dylan Beaudette)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Wed, 10 Jan 2007 11:28:47 -0800
> From: Dylan Beaudette <dylan.beaudette at gmail.com>
> Subject: [vox-tech] pl/pgsql question: dynamic access to record.column
> To: vox-tech at lists.lugod.org
> Message-ID: <200701101128.47428.dylan.beaudette at gmail.com>
> Content-Type: text/plain;  charset="us-ascii"
>
> Hi,
>
> 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!
>
> --
> Dylan Beaudette
> Soils and Biogeochemistry Graduate Group
> University of California at Davis
> 530.754.7341
>
>
> ------------------------------
>
> _______________________________________________
> vox-tech mailing list
> vox-tech at lists.lugod.org
> http://lists.lugod.org/mailman/listinfo/vox-tech
>
>
> End of vox-tech Digest, Vol 32, Issue 9
> ***************************************
>


-- 
-- Kevin Hrim

/*
Teach this triple truth to all: A generous heart, kind speech, and a
life of service and compassion are the things which renew humanity. -
Buddha
*/