postgresql - Reference Local Variable In PL/PGSQL Dynamic SQL Inside Function -

i have pl/pgsql function data processing. need first select each row table, , retrieve column names , associated values of each column. un-pivoting records horizontal state. necessary since going key/value store instead of being stored horizontally.

here abstract of function have far:

create or replace function myfunc()     returns int      $body$ declare  x record;  aesql varchar; aeval varchar; y information_schema.columns%rowtype; begin     x in     select * mytable     loop         y in         select * information_schema.columns table_schema = 'public' , table_name = 'mytable'         loop                                             execute 'select cast(x.'||y.column_name||' varchar) aeval';         end loop;         -- add processing aeval once dynamic sql figured out                          end loop;        return 1; end; $body$ language plpgsql volatile; 

i have troubleshot far enough understanding execute statement should crud query or similar. queries tried straight assignment like

execute 'aeval := x.'||y.column_name; 

failed in syntax errors 'aeval' or ':' if using ':aeval' etc.

so know if possible , how might go performing dynamic sql? sum need grab value of record x know column name.

when try run function receive error:

error: missing from-clause entry table "x" where: pl/pgsql function myfunc() line 23 @ execute statement

this funny query:

select     translate(string_to_array(mytable.*::text,',')::text,'()','')::text[] mytable; 

returns rows mytable text arrays. easier loop on arrays in function:

create or replace function myfunc() returns setof text language plpgsql $$ declare     eaval text;     x text[]; begin     x in         select translate(string_to_array(mytable.*::text,',')::text,'()','')::text[]          mytable     loop         foreach eaval in array x loop             return next eaval;         end loop;         return next '-- next row --';     end loop; end $$;  select * myfunc(); 

---

the function argument - table name:

create or replace function myfunc(table_name text) returns setof text language plpgsql $$ declare     eaval text;     x text[]; begin     x in         execute format($fmt$             select translate(string_to_array(%s.*::text,',')::text,'()','')::text[]              %s              $fmt$,              table_name, table_name)     loop         foreach eaval in array x loop             return next eaval;         end loop;         return next '-- next row --';     end loop; end $$;  select * myfunc('mytable'); select * myfunc('myschema.myanothertable'); 

read more: 39.5.4. executing dynamic commands , 9.4.1. format