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

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?