7

I'm trying to write a function in PL/PgSQL that have to work with a table it receives as a parameter.

I use EXECUTE..INTO..USING statements within the function definition to build dynamic queries (it's the only way I know to do this) but ... I encountered a problem with RECORD data types.

Let's consider the follow (extremely simplified) example.

 -- A table with some values.
 DROP TABLE IF EXISTS table1;
 CREATE TABLE table1 (
     code INT,
     descr TEXT
 );

INSERT INTO table1 VALUES ('1','a');
INSERT INTO table1 VALUES ('2','b');


-- The function code. 
DROP FUNCTION IF EXISTS foo (TEXT);
CREATE FUNCTION foo (tbl_name TEXT) RETURNS VOID AS $$
DECLARE 
    r RECORD;
    d TEXT;
BEGIN
    FOR r IN
    EXECUTE 'SELECT * FROM ' || tbl_name
    LOOP
    --SELECT r.descr INTO d; --IT WORK
    EXECUTE 'SELECT ($1)' || '.descr' INTO d USING r; --IT DOES NOT WORK
    RAISE NOTICE '%', d;
END LOOP;

END;
$$ LANGUAGE plpgsql STRICT;

-- Call foo function on table1
SELECT foo('table1');

It output the following error:

ERROR: could not identify column "descr" in record data type

although the syntax I used seems valid to me. I can't use the static select (commented in the example) because I want to dinamically refer the columns names.

So..someone know what's wrong with the above code?

Hobbes
  • 978
  • 2
  • 9
  • 15

2 Answers2

8

It's true. You cannot to use type record outside PL/pgSQL space.

RECORD value is valid only in plpgsql.

you can do 

EXECUTE 'SELECT $1.descr' INTO d USING r::text::xx;
Pavel Stehule
  • 156
  • 1
  • 4
    Ok sorry obviously your code works by replacing 'xx' with 'table1'. But that way I can't dinamically specify the table name. So the right answer is: "EXECUTE 'SELECT $1::text::table1.descr' INTO d USING r;". THANKS A LOT! – Hobbes Jan 21 '10 at 08:34
  • I'm able to do `EXECUTE 'SELECT ($1::xx).descr' INTO d USING r;` and avoid the `text` cast. – bradley.ayers Aug 18 '20 at 16:24
1

$1 should be inside the || ,like || $1 || and give spaces properly then it will work.

BEGIN

EXECUTE ' delete from  ' ||  quote_ident($1)  || ' where condition ';

END;
Nazik
  • 8,696
  • 27
  • 77
  • 123
Chinmay R Ullur
  • 35
  • 2
  • The problem in question was how to get a RECORD field in _reflective_ way (by name, using string concatenation). – botchniaque Oct 16 '15 at 09:08