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

Question

I want to do like this:

SELECT (EVAL 'SELECT 1') + 1;

Are there any way to do like this (EVAL) in PostgreSQL?

Witling

Answer 1

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.

Jeffry

Answer 2

I'd go with data type text since it's more flexible using casting operators like ::int if needed:

create or replace function eval( sql  text ) returns text as $$
declare
  as_txt  text;
begin
  if  sql is null  then  return null ;  end if ;
  execute  sql  into  as_txt ;
  return  as_txt ;
end;
$$ language plpgsql
-- select eval('select 1')::int*2         -- => 2
-- select eval($$ select 'a'||1||'b' $$)  -- => a1b
-- select eval( null )                    -- => null

I also added this and another eval( sql, keys_arr, vals_arr ) function supporting some custom key-value substitutions, e.g. for handy :param1 substitutions to postgres-utils

Bowlin

Is it possible to use this to return multiple columns? ie: select * from eval('select 1, ''hello''') ... currently this implementation only returns the first column in the resultset, ie 1 – Personal

@john-skilbeck: Here:bitbucket.org/andi1234/… I added eval2(...) variants that support multi-column/multi-row capabilities. – Bowlin

@AndreasCovidiot The link to postgres-utils doesn't work anymore. Is that code available somewhere else? – Pretorius

@RobertMikes unfortunately not. do you have a suggestion on which platform one could provide and maintain it easily? Is there a way to write to you in private here? (couldn't find it :-( ) (I will delete this comment later) – Bowlin

On github? You can look in my profile and find me on LinkedIn. – Pretorius

Answer 3

NOTES

The language PLpgSQL syntax have many ways to say:

 Y := f(X);

The EXECUTE clause is only for "dynamic execution" (less performance),

 EXECUTE 'f(X)' INTO Y;     

Use Y := f(X); or SELECT for execute static declarations,

 SELECT f(X) INTO Y;

Use PERFORM statment when discard the results or to work with void returns:

 PERFORM f(X);     

Harney

Answer 4

I am not sure if it suits you but PostgreSQL has EXECUTE statement.

Hellespont

It has to declare the statement using PREPARE before EXECUTE that, right? – Witling

No, you can execute a string directly, but not as part of another statement – Jeffry

Grzegorz is referring to the EXECUTE command in the plpgsql programming language that comes with PostgreSQL; this is a "full" programming language that can be used for writing functions, loops and conditionals etc. The source of confusion is that there's also a "plain SQL" EXECUTE statement in PostgreSQL that does something totally different -- it runs a PREPAREd statement. – Adumbrate

Answer 5

Good idea. You can modify to perform direct expressions:

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

To run just type this:

SELECT eval('2*2');

Auburta

Answer 6

Assuming that most sql queries are a part of a bigger system, there mostly will be cases where you form a query with your backend code and then execute it.

So if that’s the case for you, you can just use subselects or common table expressions that are put into your query string by the backend code before execution.

I have trouble coming up with cases where the solution you want works and my solution doesn’t, apart from not having any backend app, of course.

Satisfaction