问题描述
我有以下要在PostgreSQL中实现的MySQL脚本.
I have the following MySQL script which I want to implement in PostgreSQL.
SET @statement = search_address_query;
PREPARE dynquery FROM @statement;
EXECUTE dynquery;
DEALLOCATE PREPARE dynquery;
如何使用PostgreSQL定义用户定义的变量"@statement".
How can I define user defined variable "@statement" using PostgreSQL.
推荐答案
PostgreSQL在普通SQL中通常不使用变量.但是您也可以这样做:
PostgreSQL does not normally use variables in plain SQL. But you can do that, too:
SET foo.test = 'SELECT bar FROM baz';
SELECT current_setting('foo.test');
阅读手册中的自定义选项.
在 PostgreSQL 9.1或更早的版本中，您需要声明custom_variable_classes ，然后才能使用它.
In PostgreSQL 9.1 or earlier you needed to declare custom_variable_classes before you could use that.
但是，您不能 EXECUTE动态SQL ，不带PL(过程语言).您可以使用 DO 命令来执行即席语句(但是您不能从中返回数据).或使用 CREATE FUNCTION 创建执行动态SQL的函数(并且可以以任何可以想象的方式返回数据).
However, You cannot EXECUTE dynamic SQL without a PL (procedural language). You would use a DO command for executing ad-hoc statements (but you cannot return data from it). Or use CREATE FUNCTION to create a function that executes dynamic SQL (and can return data in any fashion imaginable).
使用动态SQL时，请确保防止SQL注入.
Be sure to safeguard against SQL injection when using dynamic SQL.
有关自定义变量的相关问题:
有没有办法定义PostgreSQL查询中一个命名常量?
Related question about custom variables:
Is there a way to define a named constant in a PostgreSQL query?
这篇关于PostgreSQL中的用户定义变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！