1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228

2 Answers2

2

You bolded the key sentence in the manual yourself:

The entire body of a SQL function is parsed before any of it is executed.

Also read about The Parser Stage in the manual.

It consists of two major parts: the parser and the transformation process. Quoting the manual:

the transformation process takes the tree handed back by the parser as input and does the semantic interpretation needed to understand which tables, functions, and operators are referenced by the query.

If an SQL function contains these commands:

CREATE TABLE foo (...);
INSERT INTO foo VALUES(...);

Both statements are planned at virtually the same time (based on the same snapshot of the system catalogs). Hence, the INSERT cannot see the table "foo" presumably created with the previous CREATE command. That creates one of the following problems:

  1. If there is no other table named "foo" in your search_patch (yet), Postgres complains when trying to create the function:

    ERROR:  relation "foo" does not exist

  2. If another table named "foo" already exists in your search_patch (and you don't use conflicting column names), Postgres will plan the INSERT based on that pre-existing table. Typically that results in an error at execution time, if any values cause conflicts in the (wrong!) table. Or, with some bad luck, it might even write to that table without error message! Very sneaky bug.

That cannot happen with a PL/pgSQL function, because it treats SQL commands like prepared statements, planned and executed sequentially. So each statement can see objects created in previous statements.

Consequently, statements that are never visited are never even planned - unlike with SQL functions. And the execution plan for statements can be cached within the same session - also unlike SQL functions. Read details about plan caching in PL/pgSQL functions in the manual here.
Each approach has advantages for some use cases. Further reading:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks. I'd appreciate if you could also consider https://stackoverflow.com/questions/51052885/what-is-the-difference-between-a-prepared-statement-and-a-sql-or-pl-pgsql-functi –  Jun 27 '18 at 00:16
  • "the execution plan for statements can be cached within the same session - also unlike SQL functions. " What is it like for any SQL function, for example, is the plan for the SQL function ever cached? If yes, cached for the current session? –  Jun 27 '18 at 04:30
  • @Ben: Unlike prepared statements and PL/pgSQL functions, SQL functions have no mechanisms for plan caching. Plan caching is *always* for the current session exclusively. – Erwin Brandstetter Jun 27 '18 at 11:11
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

Jasen
  • 11,837
  • 2
  • 30
  • 48
  • Thanks. What differences are between SQL functions and PL/pgSQL functions, in terms of parsing and execution? –  Jun 23 '18 at 22:08
  • 1
    everything. `PLPGSQL`is a procedural language constructed out of SQL statements (and plpgsql keywords). so in PLPGSQL execution order can be variable. `SQL` only has fixed execution order. – Jasen Jun 23 '18 at 22:12
  • Could you explain what " It's recommended to use PL/pgSQL instead of a SQL function in this type of situation." means –  Jun 23 '18 at 22:15
  • PLPGSQL allows you to create and use temp tables in the same function SQL does not. PLPGSQL is the Postgres developer's preferred procedural extension language. so that's the one they recommend. – Jasen Jun 23 '18 at 22:17
  • I updated my post and hopefully clarified my questions to you. Thanks. –  Jun 25 '18 at 03:06
  • From the document:"The entire body of a SQL function is parsed before any of it is executed." and from your reply: "Plpgsql functions are parsed and syntax-checked at definition time, then at first execution a plan is generated." What is the difference between them? –  Jun 25 '18 at 04:12
  • there's an initial syntax check when they are defined, there's another parse when thet are executed... – Jasen Jun 25 '18 at 04:31
  • Thanks. Could you update your reply, and be more specific about that and explicitly point out the differences between a SQL function and a PL/pgSQL function? –  Jun 25 '18 at 04:33