PostgreSQL syntax error when using EXECUTE in Function

Mike_Deck_1247 0
Viewed 29553

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;

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?

execute temp-tables syntax-error postgresql function
4 Answers

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

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

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:

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