Обсуждение: Dynamically access to field on a RECORD variable

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.

--
Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>

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

Ricardo Vaz Mannrich <rvm_l1@silcom.com.br> writes:
> Is it possible?

Not in plpgsql.  I believe you could do it in any of the other PLs though.

            regards, tom lane

I am newbie. Could you give a example?
Thank you.

> Ricardo Vaz Mannrich <rvm_l1@silcom.com.br> writes:
> > Is it possible?
>
> Not in plpgsql.  I believe you could do it in any of the other PLs though.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

If it's not possible, can I create a function that gets a RECORD and a
tablename and returns the correct value. For example:

CREATE FUNCTION my_value(TEXT, RECORD) RETURNS TEXT AS '
  DECLARE
    table_name ALIAS FOR $1
    rec ALIAS FOR $2
  BEGIN
    IF (table_name = 'my_table1') THEN
      RETURN rec.my1_field;
    ELSIF (table_name = 'my_table2') THEN
      RETURN rec.my2.field;
    ...
  END;
' LANGUAGE plpgsql;

Em Ter, 2005-05-03 às 11:52, Tom Lane escreveu:
> Ricardo Vaz Mannrich <rvm_l1@silcom.com.br> writes:
> > Is it possible?
>
> Not in plpgsql.  I believe you could do it in any of the other PLs though.
>
>             regards, tom lane
--
Ricardo Vaz Mannrich <rvm_l1@silcom.com.br>

Hi

I've downloaded the zip binaries of postgresql 8.0.3 from some of the
mirrors.
All of the dowmloaded files are corrupt.

With best regards.

--
Mario Günterberg
mattheis. werbeagentur
IT Engineer / Projektleiter

Zillestrasse 105a. D - 10585 Berlin
Tel#49-(0)30 . 34 80 633 - 0
Fax#49-(0)30 . 34 80 633 50
http://www.mattheis-berlin.de