PL / pgSQL Execution Versus Execution
What is the difference between execution and execution in 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 does not have a useful result value. To do this in PL / pgSQL, use the PERFORM statement.
But when I try something like:
perform 'create table foo as (select 1)';
Nothing happens. Although this query must have side effects (table creation) and the result can be discarded.
I think I am right 1: to run functions I can use execute:
perform pg_temp.addInheritance(foo);
source to share
PERFORM
is the plpgsql command used to call void functions. PLpgSQL is careful with useless SELECT
- SELECT
no offer INTO
is allowed. But sometimes you need to call a function and you don't need to store the result (or the functions have no result). The function in SQL
is called with SELECT
. But this is not possible in PLpgSQL - this is how the command was introduced PERFORM
.
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;
$$;
Operators PERFORM
execute the parameter and forget the result.
In your example perform 'create table foo as (select 1)';
similarly SELECT 'create table foo as (select 1)'
. Returns the string "create table foo as (select 1)" and this string is discarded.
The operator EXECUTE
evaluates the expression to get the string. In the next step, this line is executed.
So it EXECUTE 'create table ' || some_var || '(a int)';
EXECUTE 'create table ' || some_var || '(a int)';
has two steps
- evaluate expression
'create table ' || some_var || '(a int)'
'create table ' || some_var || '(a int)'
- if
some_var
, for example, mytab, then execute the commandcreate table mytab(a int)
The operator is PERFORM
used for function calls when the functions are not used in the assignment operator. EXECUTE
used to evaluate dynamic SQL - when the form of the SQL command is known at runtime.
source to share
Next the next line in the docs you specify :
Executes the request and discards the result. To write the query the same you should write the SQL SELECT statement , but instead of the SELECT keyword with PERFORM.
Mine focus
execute
in turn executes a dynamic query (same docs above)
source to share