SQL 有什么方法可以在 PostgreSQL 中的字符串值（如 eval）中执行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7433201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
提问by minhee
I want to do like this:
我想这样做：
SELECT (EVAL 'SELECT 1') + 1;
Are there any way to do like this (EVAL) in PostgreSQL?
有什么办法可以EVAL在 PostgreSQL 中做这样的 ( ) 吗？
回答by a_horse_with_no_name
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.
如果您尝试“eval”的语句总是返回相同的数据类型，您可以编写一个使用 Grzegorz 提到的 EXECUTE 的 eval() 函数。
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.
还要记住，这会因为运行任意语句而带来巨大的安全风险。如果这是一个问题，则取决于您的环境。如果这仅用于交互式 SQL 会话，那么这不是问题。
回答by Peter Krauss
NOTES
笔记
The language PLpgSQL syntax have many ways to say:
语言 PLpgSQL 语法有多种说法：
Y := f(X);
The EXECUTEclause is only for "dynamic execution" (less performance),
该EXECUTE子句仅用于“动态执行”（性能较差），
EXECUTE 'f(X)' INTO Y;
Use Y := f(X);or SELECTfor execute static declarations,
使用Y := f(X);或SELECT用于执行静态声明，
SELECT f(X) INTO Y;
Use PERFORMstatment when discard the results or to work with void returns:
丢弃结果或处理无效返回时使用PERFORM语句：
PERFORM f(X);
回答by Andreas Dietrich
I'd go with data type textsince it's more flexible using casting operators like ::intif needed:
我会使用数据类型 text因为它更灵活地使用强制转换运算符，例如::int如果需要：
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-valuesubstitutions, e.g. for handy :param1substitutionsto postgres-utils
我还添加了这一点，其他eval( sql, keys_arr, vals_arr )功能支持一些自定义的键值替代，例如用于方便:param1替换到Postgres的-utils的
回答by Grzegorz Gierlik
回答by Oberdan
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');
