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

Question

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

Sure have. I get NOTICE: ErrCode (42703) (column "...var name, which is _a in the example" does not exist) — Gonki
– Gonki, Commented Sep 6, 2012 at 16:58
If I remove the quotes from around _a in the VALUES expression, it outputs INFO: value of _b 1 for me. — András Váczi
– András Váczi, Commented Sep 6, 2012 at 19:39
@deszo: you are right. in real program the values come from a query. Please see my code edit, it queries a table now and throws the same error. — Gonki
– Gonki, Commented Sep 7, 2012 at 1:55

András Váczi · Accepted Answer · 2012-09-08 13:21:42Z

2

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)

edited Sep 8, 2012 at 13:21

András Váczi

3,02225 silver badges30 bronze badges

answered Sep 8, 2012 at 12:19

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

4 Comments

Gonki Over a year ago

Thanks! It's slightly much overhead for a real app imo.

Chris Travers Over a year ago

Honestly, I don't like it either although I have used the temp table solution in the past for very complex areas. If I needed to do a real app, I'd do similar but: 1) I'd create a type to represent my conditions 2) I'd represent my conditions in the stored proc as an array of tuples of that type and 3) I'd use a select c.value from unnest(conditions) c instead of selecting from the temp table. However, that's more developer overhead. Otherwise it is exactly the same principle at work.

Gonki Over a year ago

The problem becomes that name of the variable is available after "for _r in select _col as _nam from __ test __ a loop" query, which returns variable name that exists somewhere in the plpgsql function. I don't see how putting it into array and using unnest would "eval()" its value. There is no knowing what variable name the query returns. In your example variable names are static values + one that is passed as input, not quite the same imo. Your example would work after adding all function variables into arrays/temptable.

Chris Travers Over a year ago

The point is you have to have some representation in the function of your conditions and output. Then you can select from your conditions and produce the output. This gets around the scoping issue because you creating, in effect, a lookup table (but one serialized as an array instead of stored to a temp table). The only differences between that and the solution I offered is: 1) schema-wise it is more complex because you need the custom type, and 2) since the table format is defined in a serialized form in the function, you don't need the overhead of actual table creation/cleanup.

Daniel Vérité · Accepted Answer · 2012-09-07 21:14:50Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Related