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.