What are the difference between perform and execute on PL/pgSQL?
From the manual:
Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement.
But, when I'm trying something like:
perform 'create table foo as (select 1)';
Nothing happens. Although this query should have side effects (creating table), and the result can be discarded.
I think I get 1 thing right: in order to run functions I can use perform:
perform pg_temp.addInheritance(foo);
Answer
PERFORM is plpgsql command used for calls of void functions. PLpgSQL is careful about useless SELECT statements - the SELECT without INTO clause is not allowed. But sometimes you need to call a function and you don't need to store result (or functions has not any result). The function in SQL is called with SELECT statement. But it is not possible in PLpgSQL - so the command PERFORM was introduced.
CREATE OR REPLACE FUNCTION foo()
RETURNS void AS $$
BEGIN
RAISE NOTICE 'Hello from void function';
END;
$$ LANGUAGE plpgsql;
-- direct call from SQL
SELECT foo();
-- in PLpgSQL
DO $$
BEGIN
SELECT foo(); -- is not allowed
PERFORM foo(); -- is ok
END;
$$;
The PERFORM statements execute a parameter and forgot result.
Your example perform 'create table foo as (select 1)';
is same like SELECT 'create table foo as (select 1)'. It returns a string "create table foo as (select 1)" and this string is discarded.
The EXECUTE statement evaluate a expression to get string. In next step this string is executed.
So EXECUTE 'create table ' || some_var || '(a int)'; has two steps
- evaluate expression
'create table ' || some_var || '(a int)' - if
some_varis mytab for example, then execute a commandcreate table mytab(a int)
The PERFORM statement is used for function calls, when functions are not used in assignment statement. The EXECUTE is used for evaluation of dynamic SQL - when a form of SQL command is known in runtime.