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
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:
Show quoted text
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
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
Show quoted text
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
On Tue, Feb 10, 2009 at 8:21 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
Show quoted text
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 aSELECT
string and passing it to EXECUTE?
not in plpgsql. Try, plperl or some synamic PL language, please
regards
Pavel StehuleOn 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 thetarget
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.
Show quoted text
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 StehuleOn 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