Re: Referencing Cursor/Row/Record Fields in PL/PgSQL

От:

Pavel Stehule <pavel.stehule@gmail.com>

Дата:

11 февраля 2009 г. в 00:21:12

Hello

2009/2/11 Lee Hughes :
> 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  wrote:
>>
>> Lee Hughes  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
>
>
>

Re: Referencing Cursor/Row/Record Fields in PL/PgSQL

От:

Pavel Stehule <pavel.stehule@gmail.com>

Дата:

11 февраля 2009 г. в 12:59:30

2009/2/11 Lee Hughes :
> 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 
> wrote:
>>
>> Hello
>>
>> 2009/2/11 Lee Hughes :
>> > 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  wrote:
>> >>
>> >> Lee Hughes  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
>> >
>> >
>> >
>
>
>
>

Re: Referencing Cursor/Row/Record Fields in PL/PgSQL

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

10 февраля 2009 г. в 17:22:38

Lee Hughes  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

Referencing Cursor/Row/Record Fields in PL/PgSQL

От:

Lee Hughes <lee@hughesys.com>

Дата:

10 февраля 2009 г. в 17:15:27

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

Re: Referencing Cursor/Row/Record Fields in PL/PgSQL

От:

Lee Hughes <lee@hughesys.com>

Дата:

10 февраля 2009 г. в 20:17:25

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:
> 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

Re: Referencing Cursor/Row/Record Fields in PL/PgSQL

От:

Lee Hughes <lee@hughesys.com>

Дата:

11 февраля 2009 г. в 12:42:32

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:

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
>
>
>