Re: Access a Field / Column of a resultset by Number

hy group,

i currently look for a solution to access a resultset in a db-stored
function by number. in plpgsql thats not possible.
so i checked out plpython. so far so good, thats working:

CREATE OR REPLACE FUNCTION dokv_dorecnokeywords(sqlstatement VARCHAR)
RETURNS VOID AS
$$
rv = plpy.execute(sqlstatement, 1)
|||||->"SELECT * FROM art WHERE ak_nr='TEST'"
s = 'ak_bez' |||| fieldname is static here, should be by number
plpy.notice(s+'='+rv[0][s])
return
$$ LANGUAGE plpythonu;

now i need a solution to get the number of fields as well as the
fieldnames of that resultset and run through all fields (by number or by
name). (i need the fieldname too)

--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

thats exactly the same i'm looking for:

http://wiki.postgresql.org/wiki/Todo
http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php

(todo list for plpgsql)

*Server-Side Languages
*PL/pgSQL
*
*[D] Allow listing of record column names, and access to record columns
*via variables, e.g. columns := r.(*), tval2 := r.(colname)
*
*Re: PL/PGSQL: Dynamic Record Introspection

is that is possible in any pl* language?

Daniel Schuchardt schrieb:
> hy group,
>
> i currently look for a solution to access a resultset in a db-stored
> function by number. in plpgsql thats not possible.
> so i checked out plpython. so far so good, thats working:
>
> CREATE OR REPLACE FUNCTION dokv_dorecnokeywords(sqlstatement VARCHAR)
> RETURNS VOID AS
> $$
> rv = plpy.execute(sqlstatement, 1)
> |||||->"SELECT * FROM art WHERE ak_nr='TEST'"
> s = 'ak_bez' |||| fieldname is static here, should be by number
> plpy.notice(s+'='+rv[0][s])
> return
> $$ LANGUAGE plpythonu;
>
> now i need a solution to get the number of fields as well as the
> fieldnames of that resultset and run through all fields (by number or by
> name). (i need the fieldname too)
>

--
Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

On Fri, Nov 27, 2009 at 10:09 AM, Daniel Schuchardt
<d(dot)schuchardt(at)prodat-sql(dot)de> wrote:
> thats exactly the same i'm looking for:
>
> http://wiki.postgresql.org/wiki/Todo
> http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php
>
> (todo list for plpgsql)
>
> *Server-Side Languages
> *PL/pgSQL
> *
> *[D] Allow listing of record column names, and access to record columns *via
> variables, e.g. columns := r.(*), tval2 := r.(colname)
> *
> *Re: PL/PGSQL: Dynamic Record Introspection
>
>
> is that is possible in any pl* language?

Yes, if the language has the architecture to handle it. plpgsql
doesn't right now. pltcl, plperl, and plain old C functions can
examine records and do dynamic stuff with them. Any attempt at doing
dynamic queries right now in plpgsql leads to madness, or so I've been
told.

hy scott, thanks for your awnser.

yes thats clear. but i can't find any sourcecode. the pl* language
doesnt matter, i need that feature only in one function all over my db.

did you have any sourcecode examples?

http://www.postgresql.org/docs/8.4/interactive/plperl-database.html

nothing about how to access a column by fieldname.

daniel

Scott Marlowe schrieb:
> On Fri, Nov 27, 2009 at 10:09 AM, Daniel Schuchardt
> <d(dot)schuchardt(at)prodat-sql(dot)de> wrote:
>> thats exactly the same i'm looking for:
>>
>> http://wiki.postgresql.org/wiki/Todo
>> http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php
>>
>> (todo list for plpgsql)
>>
>> *Server-Side Languages
>> *PL/pgSQL
>> *
>> *[D] Allow listing of record column names, and access to record columns *via
>> variables, e.g. columns := r.(*), tval2 := r.(colname)
>> *
>> *Re: PL/PGSQL: Dynamic Record Introspection
>>
>>
>> is that is possible in any pl* language?
>
> Yes, if the language has the architecture to handle it. plpgsql
> doesn't right now. pltcl, plperl, and plain old C functions can
> examine records and do dynamic stuff with them. Any attempt at doing
> dynamic queries right now in plpgsql leads to madness, or so I've been
> told.
>

--
Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/

In response to Daniel Schuchardt :
> hy group,
>
> i currently look for a solution to access a resultset in a db-stored
> function by number. in plpgsql thats not possible.

Can you wait until 8.5?

http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html

Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99