Reputation: 18397
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?
Upvotes: 10
Views: 39599
Reputation: 1
can be used in PL/pgSQL language(LANGUAGE plpgsql
) for a function, procedure or DO
statement.
cannot be used in SQL language(LANGUAGE SQL
) otherwise there is error.
For example, you can use an EXECUTE
statement in the PL/pgSQL function my_func()
as shown below.
CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
EXECUTE 'UPDATE person SET age = 13 WHERE id = 2';
END;
$$ LANGUAGE plpgsql;
Upvotes: 0
Reputation: 6395
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();
Upvotes: 0
Reputation: 578
It is a example tested by me where I use EXECUTE to run a select and put its result in a cursor.
1. Create the table:
create table people (
nickname varchar(9),
name varchar(12),
second_name varchar(12),
country varchar(30)
);
2. Create the function:
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 function
select fun_find_people('name', 'Cristian');
select fun_find_people('country', 'Chile');
Upvotes: 0
Reputation: 62683
EXECUTE
is used to execute prepared statements and only expects a prepared statement name as argument.
If you are trying to execute an SQL statement (as in your example) simply include it in the body of the function.
Check the manual for more info about "Query Language (SQL) Functions".
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.
Upvotes: 0
Reputation: 434985
I think your problem is the language you're using. EXECUTE in the SQL language:
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 aPREPARE
statement executed earlier in the current session.
isn't the same as EXECUTE in PL/pgSQL:
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
EXECUTE
statement is provided: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).
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 tname
argument to the function.
Upvotes: 19