What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?

Question

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');

Pitts

Answer 1

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:

• Difference between language sql and language plpgsql in PostgreSQL functions

Starting with Postgres 11 there are also SQL procedures:

• When to use stored procedure / user-defined function?

Wavemeter

Thanks. What does "SQL functions cannot save the query plan (by itself)" mean? – Pitts

@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. – Wavemeter

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)? – Pitts

Also what does "inlined" in a bigger query mean for a SQL function? – Pitts

More details in the linked answer I provided above: #24755968 – Wavemeter