EXECUTE ... USING a statement in PL / pgSQL does not work with record type?

Question

I am trying to write a function in PL / PgSQL that should work with the table that it receives as a parameter.

I use EXECUTE..INTO..USING statements in the function definition to create dynamic queries (this is the only way I know that), but ... I ran into a problem with RECORD data types.

Consider the following (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 produces the following error:

ERROR: Could not determine "descr" column in record data type

although the syntax I used seems correct to me. I cannot use static selection (commented out in the example) because I want to dynamically refer to column names.

So ... does anyone know what happened to the above code?

Answer 1

It's true. You cannot use type notation outside the PL / pgSQL space.

The value RECORD is valid only in plpgsql.

You can do

 EXECUTE 'SELECT $1.descr' INTO d USING r::text::xx; 

Answer 2

$1 must be inside || for example || $1 || || $1 || and give spaces properly, then it will work.

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