Re: Dynamically access to field on a RECORD variable

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