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:
Show quoted text
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.
Show quoted text
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