How to Evaluate Expression in Select Statement in Postgres

How to evaluate expression in select statement in Postgres

you can write an SQL function that does this for you and use e.g. the ones supplied with postgres-utils:

select 
  c.name as cust_name,
  p.name as prod_name,
  p.cost as prod_cost,

  eval(  
    'select '||c.price_expression||' from product where id=:pid',
    '{"{cost}",:pid}',  
    array[ p.cost, p.id ]  
  )      as cust_cost

from product p,  customer c

But of course it may be slow, insecure, you could use materialized views to cache it more easily, etc. - see docu there.

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

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)

How to write Postgres Dynamic SQL statement to evaluate expressions stored in rows?

create or replace function eval_bool(data record, key text, expr text)
returns bool stable strict language plpgsql
as $$
declare
    result bool;
begin
    execute 'select $1.' || key || ' ' || expr into result using data;
    return result;
end $$;

-- test:

select relname
from pg_class as t
where eval_bool(t, 'relname', 'like ''%class%''');

┌───────────────────────────────────┐
│              relname              │
├───────────────────────────────────┤
│ pg_class_oid_index                │
│ pg_class_relname_nsp_index        │
│ pg_class_tblspc_relfilenode_index │
│ pg_opclass_am_name_nsp_index      │
│ pg_opclass_oid_index              │
│ pg_class                          │
│ pg_opclass                        │
└───────────────────────────────────┘

And for now it should be simple:

SELECT "Rule".* FROM "Rule",
  (
    SELECT "Transaction".*
    FROM "Transaction"
    WHERE "Transaction".id = $1
  ) AS "this_transaction"
WHERE eval_bool("this_transaction"::"Transaction", "Rule".key, "Rule".sql_expression);

Note that the first value (data) should to be the value of a registered type. So wee need to cast the subquery result to the "Transaction" type.

Is it possible in PL/pgSQL to evaluate a string as an expression, not a statement?

In the plpgsql function you can dynamically create any expression. This does not apply, however, in the case you described. The query must be explicitly defined before it is executed, while the choice of the field occurs while the query is executed.

Your query is the best approach. You may try to use a function, but it will not bring any benefits as the essence of the issue will remain unchanged.

Are there any way to execute a query inside the string value (like eval) in PostgreSQL?

If the statements you are trying to "eval" always return the same data type, you could write an eval() function that uses the EXECUTE mentioned by Grzegorz.

create or replace function eval(expression text) returns integer
as
$body$
declare
  result integer;
begin
  execute expression into result;
  return result;
end;
$body$
language plpgsql

Then you could do something like

SELECT eval('select 41') + 1;

But this approach won't work if your dynamic statements return something different for each expression that you want to evaluate.

Also bear in mind that this opens a huge security risk by running arbitrary statements. If that is a problem depends on your environment. If that is only used in interactive SQL sessions then it isn't a problem.

Eval Calculation-string: 2 * 3 + 4 * 5 in Postgresql

You need PL/pgSQL:

create or replace function f(_s text)
returns numeric as $$
declare i numeric;
begin
    execute format('select %s', _s) into i;
    return i;
end;
$$ language plpgsql;

select f('1 + 1');
 f 
---
 2

Evaluate SELECT clause embedded in each row

You can utilize query_to_xml function and synthetize its argument using your formula column. See example (dbfiddle here):

create table t (formula text,var1 int,var2 int,var3 int);
insert into t
  values ('     var1 + var2        ',   12  ,    9   ,    2   )
       , ('   var3 * (var1 * var2) ',   20  ,   10   ,    1   );
select *
     , (xpath('/row/f/text()',
          query_to_xml(
            'select ' || formula || ' as f from t where t.ctid = ''' || ctid || '''::tid'
            , false, true, ''
          )
       ))[1]::text::int as result
from t

Explanation: Synthetized query works on single row of original table. Since it has from t clause, it has access to any needed column. To pass proper row from outer query, my example uses ctid system column. I hope you actually have id column in your table which is more appropriate.

Evaluate expression as a column name

The best choice is to normalize the table structure by adding a column for currency and a row for each value as @Clodoaldo Neto stated. If this can't be done, dynamic SQL is you next best option. Note that it must be used inside a pl/pgsql code. Here's an example:

CREATE OR REPLACE FUNCTION GetCurrencyValue(Currency text)
RETURNS SETOF DOUBLE PRECISION AS
$BODY$
BEGIN
  RETURN QUERY EXECUTE 'SELECT value_'||Currency||' from cbr;';
END;
$BODY$
LANGUAGE plpgsql;

This function takes in the parameter "Currency" and uses it as part of the column name. You can then get the result of your second query by calling this function:

--Updated QUERY_3
SELECT * FROM GetCurrencyValue('usd');

What is the order of evaluation of conditions combining AND/OR in PostgreSQL?

AND is stronger than OR, so:

a AND b OR c == (a AND b) OR c

demo:db<>fiddle

a  | b  | c  | a AND b OR c | (a AND b) OR c | a AND (b OR c)
:- | :- | :- | :----------- | :------------- | :-------
f  | f  | f  | f            | f              | f       
f  | f  | t  | t            | t              | f       
f  | t  | f  | f            | f              | f       
f  | t  | t  | t            | t              | f       
t  | f  | f  | f            | f              | f       
t  | f  | t  | t            | t              | t       
t  | t  | f  | t            | t              | t       
t  | t  | t  | t            | t              | t 

That, of course, means in your case:

a = 1 AND b = 2 OR c = 3    ==    (a = 1 AND b = 2) OR c = 3

---

Related Topics