postgresql - Why can PL/pgSQL functions have side effect, while SQL functions can't?

Question

PostgreSQL document says:

The entire body of a SQL function is parsed before any of it is executed. While a SQL function can contain commands that alter the system catalogs (e.g., CREATE TABLE), the effects of such commands will not be visible during parse analysis of later commands in the function. Thus, for example, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); will not work as desired if packaged up into a single SQL function, since foo won't exist yet when the INSERT command is parsed.

It's recommended to use PL/pgSQL instead of a SQL function in this type of situation.

• Why "It's recommended to use PL/pgSQL instead of a SQL function in this type of situation", where the PL/pgSQL or SQL function contains commands that alter the system catalogs, such as CREATE TABLE foo (...); INSERT INTO foo VALUES(...); ?

• "The entire body of a SQL function is parsed before any of it is executed". Is it not true for a PL/pgSQL function? What differences are between SQL functions and PL/pgSQL functions, in terms of parsing and executing the commands in their bodies?

Answer 1

Plpgsql functions are parsed and syntax-checked at definition time, then at first execution a plan is generated.

https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

then that plan is executed with the given parameters.

Temporary files seem to work as expected, except those that already exist on the first execution.

As mentioned in there use of dynamic SQL (EXECUTE) is a way to foil the planner allowing access to arbitrary tables.

https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN