CODEWITHSUNDEEP
postgresqldynamicexecuteplpgsqlevaluation
Gonki
Gonki

Reputation: 736

Postgres how to evaluate expression from query to variables in the function

I would like to be able to get values of function variables whose names are queried from a table

Edited to show querying a table instead of query from static values:

    create table __test__
(
_col text
);
insert into __test__
(_col)
values('_a');

   create or replace function __test() 
    returns void
    language 'plpgsql' as
    $$
    declare
      _r record;
      _a int;
      _b int;
      _sql text;
    begin

      _a = 1;
      _b = 0;

      for _r in select _col as _nam from __test__ a loop
      -- query returns one row valued "_a"
        _sql = 'select ' || _r._nam ;
        execute _sql into _b;
      end loop;

      raise info 'value of _b %', _b;

    end;
    $$;
select __test()

when function executes so that _b = 1. Is it possible?

same error ...

ERROR:  column "_a" does not exist
LINE 1: select _a
               ^
QUERY:  select _a
CONTEXT:  PL/pgSQL function "__test" line 15 at EXECUTE statement

Upvotes: 0

Views: 3143

Answers (2)

Chris Travers
Chris Travers

Reputation: 26454

You could create a temporary table, insert your variable names and values in it, and then execute a select against that. Just clean up after. I have used approaches like that before. It works ok. It does have extra overhead though.

Edit: adding an example

CREATE FUNCTION switch (in_var text) RETURNS text
LANGUAGE PLPGSQL VOLATILE AS $$

declare t_test text;
    switch_vals text[];

BEGIN

   CREATE TEMPORARY TABLE switch_values (var text, value text);

   EXECUTE $e$ INSERT INTO switch_values VALUES 
       ('a', '1'), ('b', '2'), ('c', '3') $e$;

   EXECUTE $e$ SELECT value FROM switch_values WHERE var = $e$ || quote_literal(in_var)
       INTO t_test;

   DROP TABLE switch_values;

   RETURN t_test;

END; $$; 

postgres=# select switch('a');
  switch 
 --------
   1
 (1 row)

Upvotes: 2

Daniel Vérité
Daniel Vérité

Reputation: 61516

Let's try to reframe the question: what you're after would be the equivalent of Perl eval() function, with its ability to execute a dynamically generated piece of code for which "any outer lexical variables are visible to it". In your example, the variable would be _a, but as you can see from the error message, it can't be interpolated by a dynamic SQL statement. The reason is that the SQL interpreter has no visibility on the current pl/pgsql variables, or even the knowledge that such variables exist. They are confined to pl/pgsql.

What would be needed here is a context-aware dynamically-generated pl/pgsql statement, but this language does not have this feature. It's doubtful that a trick could be found to achieve the result without this feature. For all its ability to interface nicely with SQL, other than that it's a fairly static language.

On the other hand, this would be no problem for pl/perl.

Upvotes: 2

Related Questions