PL/pgSQL perform vs execute

Question

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 1

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

1. evaluate expression 'create table ' || some_var || '(a int)'
2. if some_var is mytab for example, then execute a command create 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.

Answer 2

Further next line in docs you quote:

This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM.

Emphasis mine

execute in its turn executes dynamic query (same docs above)

Answer 3

I recently had a case where I needed to set specific constraints to DEFERRED and found something interesting.

 - EXECUTE 'SET CONSTRAINTS fk_a DEFERRED';
 - PERFORM 'SET CONSTRAINTS fk_a DEFERRED';

Both EXECUTE and PERFORM executed the statements without error but only EXECUTE persisted the action for the rest of the code. Somehow, it looks like PERFORM runs in it's own transaction "bubble".

In my case I had two tables a and b and a FK (the real stuation is much more complex). There was a need to insert the data out of the parent/child order and for that we needed the constraint DEFERRED. Using the PERFORM we had a foreign key violation, with EXECUTE we didn't.