Re: Dynamically access to field on a RECORD variable - Mailing list pgsql-general

From Pavel Stehule
Subject Re: Dynamically access to field on a RECORD variable
Date
Msg-id Pine.LNX.4.44.0505031622590.7037-100000@kix.fsv.cvut.cz
Whole thread Raw
In response to Dynamically access to field on a RECORD variable  (Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>)
List pgsql-general
Hello,

It's not possible. Not in plpgsql. Its possible in plperl or plpython or
pltcl. But you can do

CREATE OR REPLACE FUNCTION my_fce(text) returns text AS $$
DECLARE _r RECORD;
BEGIN
  FOR _r IN EXECUTE 'SELECT '||$1||' AS _c FROM my_table ...' LOOP
    RETURN _r._c;
  END LOOP;
END; $$ LANGUAGE plpgsql;

or if you know all possible columns names

BEGIN
  SELECT INTO _r * FROM my_tab ...
  RETURN CASE $1 WHEN 'c1' THEN _r.c1 .... END;
END; $$ LANGUAGE plpgsql;

regards
Pavel Stehule


On 3 May 2005, Ricardo Vaz Mannrich wrote:

> Supose I have this function
>
> CREATE OR REPLACE my_func(TEXT) RETURNS text AS '
>   DECLARE
>     var_name ALIAS FOR $1;
>     rec RECORD;
>   BEGIN
>     SELECT * INTO rec FROM my_table WHERE my_key = 1;
>     -- Here is my problem
>     RETURN rec.var_name;
>   END;
> ' LANGUAGE plpgsql;
>
> SELECT my_func('my_field');
>
> I want the return row in the function executes as:
>
> RETURN rec.my_field;
>
> Is it possible?
>
> Thank you.
>
>


pgsql-general by date:

Previous
From: Thomas Hallgren
Date:
Subject: Re: Deep integration of PostgreSQL with Apache
Next
From: Brandon Craig Rhodes
Date:
Subject: does database shut down cleanly when WAL device fails?