25

In PostgreSQL, what is the difference between a prepared statement and a SQL or PL/pgSQL function, in terms of their purposes, advantages and disadvantages? When shall we use which?

In this very simple example, do they work the same, correct? 

CREATE TABLE foo (id INT, name VARCHAR(80)); 

CREATE FUNCTION myfunc1(INT, VARCHAR(80)) RETURNS void AS ' 
INSERT INTO foo VALUES ($1, $2);
' LANGUAGE SQL; 

SELECT myfunc1(3, 'ben');

CREATE FUNCTION myfunc2(INT, VARCHAR(80)) RETURNS void AS ' 
BEGIN
INSERT INTO foo VALUES ($1, $2);
END' LANGUAGE plpgsql; 

SELECT myfunc2(3, 'ben');

PREPARE fooplan (INT, VARCHAR(80)) AS
    INSERT INTO foo VALUES($1, $2);
PREPARE

EXECUTE fooplan(3, 'ben');
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228

1 Answers1

14

All three "work the same" in that they execute the simple SQL statement:

INSERT INTO foo VALUES (3, 'ben');

The prepared statement is only good for a single prepared SQL statement (as the name suggests). And only DML commands. The manual:

Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

The function can contain any number of statements. DML and DDL. Only SQL for SQL functions. Plus some non-SQL procedural elements in PL/pgSQL.

The prepared statement is only visible inside the same session and gone at the end of the session, while the function persists and is visible to all - still only usable for those with the EXECUTE privilege.

The prepared statement is encumbered with the least overhead. (Not much difference.)

The SQL function is the only one of the three that cannot save the query plan (by itself). Read details about plan caching in PL/pgSQL functions in the manual here.

The SQL function is also the only one that could be inlined when used within a bigger query. (Not with an INSERT, though.)

A rather comprehensive list of differences between SQL and PL/pgSQL functions:

Starting with Postgres 11 there are also SQL procedures:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    Thanks. What does "SQL functions cannot save the query plan (by itself)" mean? –  Jun 27 '18 at 00:48
  • 1
    @Ben: Prepared statements save the query plan for repeated execution within the same session. PL/pgSQL functions treat statements effectively as prepared statements. SQL functions do not. But the query plan might still be saved indirectly when the SQL function is used in a query for which the plan is saved. – Erwin Brandstetter Jun 27 '18 at 00:50
  • Thanks. PostgreSQL document says that "**When the PREPARE statement is executed**, the specified statement is **parsed**, **analyzed**, and **rewritten**. **When an EXECUTE command is subsequently issued**, the prepared statement is **planned** and **executed**. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied." Similarly, what is it like for defining a SQL function and for calling the SQL function with some argument(s)? –  Jun 27 '18 at 00:58
  • Also what does "inlined" in a bigger query mean for a SQL function? –  Jun 27 '18 at 04:04
  • More details in the linked answer I provided above: https://stackoverflow.com/questions/24755468/difference-between-language-sql-and-language-plpgsql-in-postgresql-functions/24771561#24771561 – Erwin Brandstetter Jun 27 '18 at 11:03