在函数中使用 EXECUTE 时 PostgreSQL 语法错误

PostgreSQL syntax error when using EXECUTE in Function

提问by Mike Deck

I'm trying to create a function which references a temporary table in PostgreSQL 8.4. Based on my research it seems the best way to do this is to use the EXECUTEcommand to execute my query from a defined string.

我正在尝试创建一个引用 PostgreSQL 8.4 中临时表的函数。根据我的研究，似乎最好的方法是使用EXECUTE命令从定义的字符串执行我的查询。

Unfortunately I'm getting an odd syntax error when trying to create the function.

不幸的是，我在尝试创建函数时遇到了一个奇怪的语法错误。

My current function definition is as follows:

我当前的函数定义如下：

CREATE OR REPLACE FUNCTION example() RETURNS void AS $$
  EXECUTE 'INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3 from temp_table';
$$ LANGUAGE SQL;

The error I am getting is:

我得到的错误是：

ERROR:  syntax error at or near "'INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3 from temp_table'"
LINE 2:   execute 'INSERT INTO table1 (col1, col2, col3) SELECT col1...

It seems I get the same error regardless of what is actually in the string literal.

无论字符串文字中的实际内容如何，​​我似乎都会遇到相同的错误。

My questions are, 1) what is the correct syntax for using the EXECUTE feature, and 2) is there a better way to write a function like this that references a temporary table?

我的问题是，1) 使用 EXECUTE 功能的正确语法是什么，2) 有没有更好的方法来编写这样一个引用临时表的函数？

回答by mu is too short

I think your problem is the language you're using. EXECUTEin the SQL language:

我认为你的问题是你使用的语言。SQL语言中的EXECUTE：

EXECUTEis used to execute a previously prepared statement. Since prepared statements only exist for the duration of a session, the prepared statement must have been created by a PREPAREstatement executed earlier in the current session.

EXECUTE用于执行先前准备好的语句。由于准备好的语句仅在会话期间存在，因此准备好的语句必须是由PREPARE在当前会话中较早执行的语句创建的。

isn't the same as EXECUTE in PL/pgSQL:

与PL/pgSQL 中的 EXECUTE 不同：

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands (as discussed in Section 39.10.2) will not work in such scenarios. To handle this sort of problem, the EXECUTEstatement is provided:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

通常，您希望在 PL/pgSQL 函数中生成动态命令，即每次执行时将涉及不同表或不同数据类型的命令。PL/pgSQL 对命令缓存计划的正常尝试（如第 39.10.2 节所述）在这种情况下将不起作用。为了处理此类问题，EXECUTE提供了以下语句：

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

You're using the SQL EXECUTE (which executes a prepared statement) when you want to be using the PL/pgSQL EXECUTE (which executes a string as SQL).

当您想使用 PL/pgSQL EXECUTE（将字符串作为 SQL 执行）时，您正在使用 SQL EXECUTE（它执行准备好的语句）。

Try this:

尝试这个：

CREATE OR REPLACE FUNCTION example() RETURNS void AS $$
BEGIN
    EXECUTE 'INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3 from temp_table';
END;
$$ LANGUAGE PLPGSQL;

Or, another example that seems closer to what you seem to be trying to do:

或者，另一个似乎更接近您似乎正在尝试做的示例：

create or replace function example(tname text) returns void as $$
begin
    execute 'insert into ' || tname || ' (name) values(''pancakes'')';
end;
$$ language plpgsql;

That will insert 'pancakes'into the table that you pass in the tnameargument to the function.

这将插入'pancakes'到您将tname参数传递给函数的表中。

回答by Milen A. Radev

EXECUTEis used to execute prepared statements and only expects a prepared statement name as argument.

EXECUTE用于执行准备好的语句并且只需要准备好的语句名称作为参数。

If you are trying to execute an SQL statement (as in your example) simply include it in the body of the function.

如果您尝试执行 SQL 语句（如您的示例中所示），只需将其包含在函数体中即可。

Check the manualfor more info about "Query Language (SQL) Functions".

查看手册以获取有关“查询语言 (SQL) 函数”的更多信息。

OTOH if you are trying to create a PL/pgSQL function (which is not what you've shown in your question), then you need to convert your function to be a PL/pgSQL function.

OTOH 如果您尝试创建 PL/pgSQL 函数（这不是您在问题中显示的内容），那么您需要将您的函数转换为PL/pgSQL 函数。

回答by Cristian

It is a example tested by me where I use EXECUTE to run a select and put its result in a cursor.

这是我测试的一个示例，我使用 EXECUTE 运行选择并将其结果放在游标中。

1. Create the table:

1. 创建表：

create table people (
  nickname varchar(9),
  name varchar(12),
  second_name varchar(12),
  country varchar(30)
  );

2. Create the function:

2. 创建函数：

CREATE OR REPLACE FUNCTION fun_find_people (col_name text, col_value varchar)
RETURNS void AS
$BODY$
DECLARE
    local_cursor_p refcursor;
    row_from_people RECORD;

BEGIN
    open local_cursor_p FOR
        EXECUTE 'select * from people where '|| col_name || ' LIKE ''' || col_value || '%'' ';

    raise notice 'col_name: %',col_name;
    raise notice 'col_value: %',col_value;

    LOOP
        FETCH local_cursor_p INTO row_from_people; EXIT WHEN NOT FOUND;

        raise notice 'row_from_people.nickname: %',  row_from_people.nickname ;
        raise notice 'row_from_people.name: %', row_from_people.name ;
        raise notice 'row_from_people.country: %', row_from_people.country;
    END LOOP;
END;
$BODY$ LANGUAGE 'plpgsql'

3. Run the functionselect fun_find_people('name', 'Cristian'); select fun_find_people('country', 'Chile');

3. 运行函数select fun_find_people('name', 'Cristian'); select fun_find_people('country', 'Chile');

回答by ANeves

Alternatively, you can run it inside an anonymous code-block using DO.
According to the documentation(emphasis mine):

或者，您可以使用DO.
根据文档（强调我的）：

DOexecutes an anonymous code block, or in other words a transient anonymous function in a procedural language.

The code block is treated as though it were the body of a function with no parameters, returning void.It is parsed and executed a single time.

DO执行匿名代码块，或者换句话说，在过程语言中执行一个临时匿名函数。

代码块被视为没有参数的函数体，返回 void。它被解析和执行一次。

---

---

This allows you to run constructed SQL that you would normally not be able to run, without forcing you to build a function to call it:

这允许您运行通常无法运行的构造 SQL，而不会强制您构建一个函数来调用它：

DO $$
BEGIN
    execute 'ALTER DATABASE ' || current_database() || ' SET timezone TO ''UTC''';
    execute 'SET timezone TO ''UTC''';
END;
$$

instead of:

代替：

CREATE OR REPLACE FUNCTION fix_database_timezone()
RETURNS void AS
$BODY$
BEGIN
    execute 'ALTER DATABASE ' || current_database() || ' SET timezone TO ''UTC''';
    execute 'SET timezone TO ''UTC''';
END;
$BODY$ LANGUAGE 'plpgsql';

fix_database_timezone();