Access Record Column Stored in a Variable in PostgreSQL

Question

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.

$$..... $$ make the inner block as string literal. execute is exceptional. so in a string block, rec.col will be rec.col. It will not transform to rec.emp_code. — jian
– jian, Commented Dec 9, 2022 at 10:24

Pavel Stehule · Accepted Answer · 2022-12-09 09:44:01Z

3

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.

answered Dec 9, 2022 at 9:44

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

simply-put Over a year ago

Thanks @Pavel-stehule and you are right. 90% of the time static conventions and alias works pretty well. Actual use case requires columns to be passed as an array so exact number of cols would not be known before hand. JSONB is a nice hack but will have some parsing performance issues on large tables. Is there any other way possible like having some record function to access col value? Sorry for my ignorance. I am new to the postgres world.

Pavel Stehule Over a year ago

@simply-put - there is third way, but it is not used now, because it needs second dynamic query, and it is significantly slower than cast to jsonb. There is not any other way in PL/pgSQL. You can use another language like PL/Perl or PL/Python or C. But in PL/pgSQL is not other possibility.

henfiber Over a year ago

The jsonb hack also works without an intermediate variable if you wrap the conversion in parenthesis like: (to_jsonb(r))['x'] or (to_jsonb(r)) ->> 'x'. Instead of 'x' you can use a variable where you have stored the column you want to extract: (to_jsonb(r)) ->> col. This is useful in FOR/FOREACH loops.

Collectives™ on Stack Overflow

Access Record Column Stored in a Variable in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related