Thread: Referencing Cursor/Row/Record Fields in PL/PgSQL
Trying to figure out how to reference a field in a cursor result, or in a row/record variable that I've FETCHed the cursor into, where the target field name is in a variable or parameter. I think I'm just missing the dereferencing syntax. I've studied the manual and tried using EXECUTE to no avail.
Worst case, if I could just reference the Nth field, then I could combine with pg_attribute info to get what I need.
Thanks for any help-
Lee
Worst case, if I could just reference the Nth field, then I could combine with pg_attribute info to get what I need.
Thanks for any help-
Lee
Lee Hughes <lee@hughesys.com> writes:
> Trying to figure out how to reference a field in a cursor result, or in a
> row/record variable that I've FETCHed the cursor into, where the target
> field name is in a variable or parameter. I think I'm just missing the
> dereferencing syntax.
There isn't any --- plpgsql doesn't deal in accesses to unknown fields
(mainly because it can't know their type, and it's a strongly typed
language). Consider plperl or plpython or pl-anything-but-pgsql.
regards, tom lane
I thought that's what EXECUTE was for in plpgsql -- isn't there a way to extract the value of a field in a row/record variable by building a SELECT string and passing it to EXECUTE?
On Tue, Feb 10, 2009 at 1:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Lee Hughes <lee@hughesys.com> writes:There isn't any --- plpgsql doesn't deal in accesses to unknown fields
> Trying to figure out how to reference a field in a cursor result, or in a
> row/record variable that I've FETCHed the cursor into, where the target
> field name is in a variable or parameter. I think I'm just missing the
> dereferencing syntax.
(mainly because it can't know their type, and it's a strongly typed
language). Consider plperl or plpython or pl-anything-but-pgsql.
regards, tom lane
Hello 2009/2/11 Lee Hughes <lee@hughesys.com>: > I thought that's what EXECUTE was for in plpgsql -- isn't there a way to > extract the value of a field in a row/record variable by building a SELECT > string and passing it to EXECUTE? > not in plpgsql. Try, plperl or some synamic PL language, please regards Pavel Stehule > On Tue, Feb 10, 2009 at 1:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> Lee Hughes <lee@hughesys.com> writes: >> > Trying to figure out how to reference a field in a cursor result, or in >> > a >> > row/record variable that I've FETCHed the cursor into, where the target >> > field name is in a variable or parameter. I think I'm just missing the >> > dereferencing syntax. >> >> There isn't any --- plpgsql doesn't deal in accesses to unknown fields >> (mainly because it can't know their type, and it's a strongly typed >> language). Consider plperl or plpython or pl-anything-but-pgsql. >> >> regards, tom lane > > >
I got it to work in plpgsql through the use of pg_attribute and a temporary table with known field names like "field0", "field1", etc. Works very nicely and performance seems fine so far.
Thanks for your help.
Lee
Thanks for your help.
Lee
On Tue, Feb 10, 2009 at 8:21 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
2009/2/11 Lee Hughes <lee@hughesys.com>:> I thought that's what EXECUTE was for in plpgsql -- isn't there a way tonot in plpgsql. Try, plperl or some synamic PL language, please
> extract the value of a field in a row/record variable by building a SELECT
> string and passing it to EXECUTE?
>
regards
Pavel Stehule
> On Tue, Feb 10, 2009 at 1:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Lee Hughes <lee@hughesys.com> writes:
>> > Trying to figure out how to reference a field in a cursor result, or in
>> > a
>> > row/record variable that I've FETCHed the cursor into, where the target
>> > field name is in a variable or parameter. I think I'm just missing the
>> > dereferencing syntax.
>>
>> There isn't any --- plpgsql doesn't deal in accesses to unknown fields
>> (mainly because it can't know their type, and it's a strongly typed
>> language). Consider plperl or plpython or pl-anything-but-pgsql.
>>
>> regards, tom lane
>
>
>
2009/2/11 Lee Hughes <lee@hughesys.com>: > I got it to work in plpgsql through the use of pg_attribute and a temporary > table with known field names like "field0", "field1", etc. Works very nicely > and performance seems fine so far. > you don't need temp. table. If you know fix sets of columns. Attentions. These columns should have same types. regards Pavel S. > Thanks for your help. > > Lee > > On Tue, Feb 10, 2009 at 8:21 PM, Pavel Stehule <pavel.stehule@gmail.com> > wrote: >> >> Hello >> >> 2009/2/11 Lee Hughes <lee@hughesys.com>: >> > I thought that's what EXECUTE was for in plpgsql -- isn't there a way to >> > extract the value of a field in a row/record variable by building a >> > SELECT >> > string and passing it to EXECUTE? >> > >> >> not in plpgsql. Try, plperl or some synamic PL language, please >> >> regards >> Pavel Stehule >> >> > On Tue, Feb 10, 2009 at 1:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> >> >> Lee Hughes <lee@hughesys.com> writes: >> >> > Trying to figure out how to reference a field in a cursor result, or >> >> > in >> >> > a >> >> > row/record variable that I've FETCHed the cursor into, where the >> >> > target >> >> > field name is in a variable or parameter. I think I'm just missing >> >> > the >> >> > dereferencing syntax. >> >> >> >> There isn't any --- plpgsql doesn't deal in accesses to unknown fields >> >> (mainly because it can't know their type, and it's a strongly typed >> >> language). Consider plperl or plpython or pl-anything-but-pgsql. >> >> >> >> regards, tom lane >> > >> > >> > > > > >