How to Use Prepared Statements with Postgres

How to use prepared statements in DO statements?

You cannot use a DO statement as a prepared statement.

I recommend that you use two statements:

one get the three results that you need to determine if there is an error condition
one to run the INSERT statement

The second of these would be a regular prepared statement.

It seems to me that you are mixing up transactions and BEGIN ... END blocks in PL/pgSQL.

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

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?

How to use prepared statements with Postgres

What do prepared statements mean in
the statement?

From the documentation:

This feature allows commands that will be used repeatedly to be parsed and planned just once, rather than each time they are executed.

See pg_prepare

Example from the page linked above:

<?php
// Connect to a database named "mary"
$dbconn = pg_connect("dbname=mary");

// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", 'SELECT * FROM shops WHERE name = $1');

// Execute the prepared query.  Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));

// Execute the same prepared query, this time with a different parameter
$result = pg_execute($dbconn, "my_query", array("Clothes Clothes Clothes"));
?>

The MySQL documentation for Prepared Statements nicely answers the following questions:

Why use prepared statements?
When should you use prepared
statements?

Postgresql prepared statement with automatic columns

With standard SQL you would use

INSERT INTO ps_test (data) VALUES ( $1 )

to insert $1 into the data column. Notice the INSERT statement syntax allows for the column_name, data, to be specified in parentheses after the table_name, ps_test.

Therefore, your prepared statement would be

PREPARE pstmt (text) AS INSERT INTO ps_test (data) VALUES ( $1 );

Conditions in prepared statements in postgres

This query will work for you.

select * from users where
  case
    when coalesce($1, '') = '' then true 
    else (name ~ $1)
  end;

how to insert DEFAULT into a prepared statement in PostgreSQL

There is no way to do that with a prepared statement.

The only escape would be a BEFORE INSERT trigger on the table that replaces certain data values (e.g. NULL) with the default value. But this is not a nice solution and will cost performance.

The other escape route is to use several prepared statements, one for each combination of values you want set to default.

Cannot run prepared statement/function in postgres

Why you need it? Any embedded SQL in PLpgSQL is implicitly prepared statement. Explicitly prepared statements are not supported in PLpgSQL. Maybe you want to use dynamic SQL - see EXECUTE statement.

Is it possible to create a prepared statement and reuse it later with Java under postgres?

When you use a java.sql.PreparedStatement with the PostgreSQL JDBC driver, it will at first not create a real prepared statement on the database server, but just construct a simple SQL statement to send to the database server. Only at the sixth execution it will think that it is worth the effort to create a named prepared statement on the server that it reuses for future executions.

You can use the prepareThreshold connection property to influence the behavior, see the documentation. So to make your second example use a server prepared statement, you would have to lower the threshold to 0. This is useful only if you know you will reuse all your prepared statements; consider that prepared statements are often used for other purposes like avoiding SQL injection problems.

On the database server there is a similar functionality: the first five times a prepared statement is executed, PostgreSQL computes a custom plan for it. Only at the sixth execution it will consider switching to a generic plan instead, so that you can avoid the overhead of planning from then on. This can be influenced with the PostgreSQL parameter plan_cache_mode from v12 on.

So, with the default settings, it will take ten executions of a java.sql.PreparedStatement before you see a performance increase from avoiding planning costs.

How to use prepared statements with Postgres in PHP

You query is OK. you can pass the parameter like below with ?:

$x = 0;
$update_prices = $pdo->prepare ("SELECT fund_name FROM funds WHERE fund_name = (SELECT fund_name FROM
                                  (SELECT fund_name, ROW_NUMBER () OVER (ORDER BY fund_name ASC) AS fund_line
                                FROM (SELECT fund_name FROM funds) cost) AS cost_sorted WHERE fund_line = ?);");

$update_prices->execute(array($x));

How can I log `PREPARE` statements in PostgreSQL?

As you mentioned, your queries are being prepared via the extended query protocol, which is distinct from a PREPARE statement. And according to the docs for log_statement:

For clients using extended query protocol, logging occurs when an
Execute message is received

(Which is to say that logging does not occur when a Parse or Bind message is received.)

However, if you set log_min_duration_statement = 0, then:

For clients using extended query protocol, durations of the Parse,
Bind, and Execute steps are logged independently

Enabling both of these settings together will give you two log entries per Execute (one from log_statement when the message is received, and another from log_min_duration_statement once execution is complete).