Синтаксическая ошибка PostgreSQL при использовании EXECUTE в функции

Я пытаюсь создать функцию, которая ссылается на временную таблицу в 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) есть ли лучший способ написать такую ​​функцию, которая ссылается на временную таблицу?


postgresql function syntax-error temp-tables execute
person Mike Deck    schedule 28.07.2011    source источник

Ответы (4)


arrow_upward
15
arrow_downward

Я думаю, ваша проблема в том, на каком языке вы говорите. EXECUTE на языке SQL:

EXECUTE используется для выполнения заранее подготовленного оператора. Поскольку подготовленные операторы существуют только в течение сеанса, подготовленные операторы должны быть созданы оператором PREPARE, выполненным ранее в текущем сеансе.

не то же самое, что EXECUTE в PL / pgSQL:

Часто вам может потребоваться сгенерировать динамические команды внутри ваших функций 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 функции.

person mu is too short    schedule 28.07.2011

arrow_upward
0
arrow_downward

EXECUTE используется для выполнения подготовленных операторов и ожидает только подготовленных операторов. имя как аргумент.

Если вы пытаетесь выполнить оператор SQL (как в вашем примере), просто включите его в тело функции.

Дополнительную информацию о языке запросов (SQL) см. В руководстве Функции ».

OTOH, если вы пытаетесь создать функцию PL / pgSQL (а это не то, что вы указали в своем вопросе), вам необходимо преобразовать вашу функцию в функция PL / pgSQL.

person Milen A. Radev    schedule 28.07.2011
comment
Я не могу просто включить запрос в тело функции, поскольку он ссылается на временную таблицу, которая не существует на момент создания функции. Единственный способ обойти это - использовать функцию PL / pgSQL? - person Mike Deck; 28.07.2011

arrow_upward
0
arrow_downward

Это проверенный мной пример, в котором я использую EXECUTE для запуска выбора и помещения его результата в курсор.

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

person Cristian    schedule 18.02.2017

arrow_upward
0
arrow_downward

В качестве альтернативы вы можете запустить его внутри анонимного блока кода, используя 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();
person ANeves thinks SE is evil    schedule 16.08.2019