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

Question

我正在尝试创建一个引用PostgreSQL 8.4中临时表的函数。根据我的研究，似乎最好的方法是使用EXECUTE命令从定义的字符串执行查询。不幸的是，在尝试创建函数时，我遇到了奇怪的语法错误。我的当前函数定义如下：

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

无论字符串字面量中实际包含什么，我似乎都会遇到相同的错误。

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

- Mike Deck

Answer 1

我认为你的问题在于你使用的语言。SQL语言中的EXECUTE用于执行先前准备好的语句。由于准备好的语句只存在于会话的持续时间内，所以已经准备好的语句必须由当前会话中早期执行的PREPARE语句创建。

而PL/pgSQL中的EXECUTE并不相同：

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

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

你正在使用 SQL EXECUTE（用于执行预处理语句），而你应该使用 PL/pgSQL EXECUTE（用于执行 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;

或者，另一个更接近你尝试做的事情的例子：

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

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

- mu is too short

Answer 2

EXECUTE 用于执行预处理语句，只需要一个预处理语句名称作为参数。

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

有关“查询语言（SQL）函数”的更多信息，请查看手册。

另一方面，如果您要创建一个 PL/pgSQL 函数（这不是您在问题中展示的内容），则需要将您的函数转换为PL/pgSQL 函数。

- Milen A. Radev

---

我不能仅在函数体中包含查询，因为它引用了一个在创建函数时不存在的临时表。唯一的解决方法是使用 PL/pgSQL 函数吗？ - Mike Deck

Answer 3

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

1. 创建表：

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

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. 运行函数 select fun_find_people('name', 'Cristian'); select fun_find_people('country', 'Chile');

- Cristian

Answer 4

或者，您可以使用DO在匿名代码块中运行它。
根据文档（我强调）：

DO执行一个匿名代码块，换句话说，在过程语言中执行一个瞬态的匿名函数。

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

---

这使您能够运行构建的 SQL，而无需强制构建一个函数来调用它：

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

改为：

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();

- ANeves