Access Record Column Stored in a Variable in PostgreSQL-CodePudding

I have been looking for a way to access columns returned by a SQL select query in PostgreSQL dynamically. For e.g. see the below sample function:

CREATE FUNCTION my_func(col text) RETURNS integer LANGUAGE plpgsql
$$
DECLARE
val text;
rec record;
BEGIN
FOR rec IN
  EXECUTE 'SELECT ' || col || ' FROM employee_tb WHERE sal > 10000'
LOOP
-- trying this but failing
    val = rec[col];
-- this is also failing
    val = rec.col;
-- What is the syntax for doing this. There should be one I think.
END LOOP;
RETURN 1;
$$

-- Call function
select my_func('emp_code');

This is very much possible in any other language. I have been looking for a solution for quite sometime now but still not able to find one.

Thanks in advance.

CodePudding user response：

Usually, this necessity is signal of overusing of dynamic SQL. On second hand, this necessity can be real. There are more possibilities. The most simply possibility is using of column label.

do $$
declare r record;
begin
  execute 'select 1 as x' into r;
  raise notice '%', r.x;
end;

$$;
NOTICE:  1
DO

Second possibility is transformation to jsonb and using [] selector syntax:

do $$
declare r record; j jsonb;
begin
  execute 'select 1' into r; j := to_jsonb(r);
  raise notice '%', j['?column?'];
end;

$$;
NOTICE:  1
DO

Attention - selector returns jsonb value - sometimes is necessary to extract text or bool value from jsonb value.

PLpgSQL is very static language. The programming style should be more like C than like Python or Perl. Dynamic SQL should not be overused.