在函数中使用EXECUTE时PostgreSQL语法错误 [英] PostgreSQL syntax error when using EXECUTE in Function

问题描述

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

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 EXECUTE command to execute my query from a defined string.

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

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;

我得到的错误是:

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.

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

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?

推荐答案

我认为您的问题是您使用的语言.以SQL语言执行:

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

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

EXECUTE is 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 PREPARE statement executed earlier in the current session.

与不同在PL/pgSQL中执行:

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

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

当您想使用PL/pgSQL EXECUTE(将字符串作为SQL执行)时，您正在使用SQL EXECUTE(其执行准备好的语句).

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).

尝试一下:

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;

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

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

这篇关于在函数中使用EXECUTE时PostgreSQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！