### Manual installation of PL/Perl

Source: https://postgrespro.com/docs/enterprise/18/xplang-install

Example demonstrating the manual installation steps for the PL/Perl language.

```sql
CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS
    '$libdir/plperl' LANGUAGE C;
```

```sql
CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS
    '$libdir/plperl' LANGUAGE C STRICT;

CREATE FUNCTION plperl_validator(oid) RETURNS void AS
    '$libdir/plperl' LANGUAGE C STRICT;
```

```sql
CREATE TRUSTED LANGUAGE plperl
    HANDLER plperl_call_handler
    INLINE plperl_inline_handler
    VALIDATOR plperl_validator;
```

--------------------------------

### userlist.txt example

Source: https://postgrespro.com/docs/enterprise/18/pgbouncer

Contains the users allowed in, with username and password in quotes. Used as the second step in the quick start.

```text
"someuser" "same_password_as_in_server"

```

--------------------------------

### Start a psql session

Source: https://postgrespro.com/docs/enterprise/18/app-psql

Example of starting a psql session by connecting to the 'testdb' database.

```bash
$ **psql testdb**
psql (18.4.1)
Type "help" for help.

testdb=>
```

--------------------------------

### Simple Setup for pgpro_pwr

Source: https://postgrespro.com/docs/enterprise/18/pgpro-pwr.html

Procedure for installing pgpro_pwr on the target cluster as a superuser.

```sql
CREATE SCHEMA profile;
CREATE EXTENSION pgpro_pwr SCHEMA profile;
```

--------------------------------

### Create nodes for grouping examples

Source: https://postgrespro.com/docs/enterprise/18/apache-age.html

Initial setup to create nodes with properties a, b, and c for subsequent grouping demonstrations.

```sql
SELECT * FROM cypher('graph_name', $$
CREATE (:L {a: 1, b: 2, c: 3}),
       (:L {a: 2, b: 3, c: 1}),
       (:L {a: 3, b: 1, c: 2})
$$) as (a agtype);
```

--------------------------------

### GET Command Syntax and Examples

Source: https://postgrespro.com/docs/enterprise/18/proxima

Syntax for reading data from the KVik database, including examples with simple and composite primary keys.

```text
CRUD:_database_name_._schema_name_._table_name_:{_primary_key_}
```

```text
GET 'CRUD:mydb.public.test:{"id":1}'
```

```text
GET 'CRUD:mydb.public.test:{"id":1,"suffix":"ex"}'
```

--------------------------------

### Install file_fdw and Create External Server

Source: https://postgrespro.com/docs/enterprise/18/pg-proaudit.html

Initial setup to enable file_fdw and define a foreign server for accessing log files.

```sql
CREATE EXTENSION file_fdw;
CREATE SERVER pg_proauditlog FOREIGN DATA WRAPPER file_fdw;
```

--------------------------------

### Setup SQL for Composite Type Example

Source: https://postgrespro.com/docs/enterprise/18/ecpg-variables

SQL commands to create a composite type and a table for testing ECPG composite type access.

```sql
CREATE TYPE comp_t AS (intval integer, textval varchar(32));
CREATE TABLE t4 (compval comp_t);
INSERT INTO t4 VALUES ( (256, 'Postgres Pro') );
```

--------------------------------

### pgpro_gbtree global index example: SQL setup and EXPLAIN queries

Source: https://postgrespro.com/docs/enterprise/18/pgpro-gbtree

Creates the pgpro_gbtree extension, a partitioned table with three partitions, a global index on column v, and a normal index on column x. Then runs EXPLAIN for conditions on each index to compare query plans.

```sql
-- Creating the pgpro_gbtree extension
CREATE EXTENSION pgpro_gbtree;

-- Creating a partitioned table with three partitions
CREATE TABLE t_int (i int PRIMARY KEY, v int, x int) PARTITION BY RANGE (i);
CREATE TABLE t_int_1 PARTITION OF t_int FOR VALUES FROM (0) TO (100);
CREATE TABLE t_int_2 PARTITION OF t_int FOR VALUES FROM (100) TO (200);
CREATE TABLE t_int_3 PARTITION OF t_int FOR VALUES FROM (200) TO (300);

-- Creating a global index for the v column
CREATE INDEX t_int_v ON t_int USING gbtree (v);

-- Creating a normal index for the x column
CREATE INDEX t_int_x ON t_int (x);

-- Query plan for a condition on the column with the global index
EXPLAIN SELECT * FROM t_int WHERE v > 100;

-- Query plan for a condition on the column with the normal index
EXPLAIN SELECT * FROM t_int WHERE x > 100;
```

```text
CREATE EXTENSION
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using t_int_v on t_int  (cost=0.12..8.14 rows=0 width=0)
   Index Cond: (v > 100)
(2 rows)

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Append  (cost=9.42..93.97 rows=2040 width=12)
   ->  Bitmap Heap Scan on t_int_1  (cost=9.42..27.92 rows=680 width=12)
         Recheck Cond: (x > 100)
         ->  Bitmap Index Scan on t_int_1_x_idx  (cost=0.00..9.25 rows=680 width=0)
               Index Cond: (x > 100)
   ->  Bitmap Heap Scan on t_int_2  (cost=9.42..27.92 rows=680 width=12)
         Recheck Cond: (x > 100)
         ->  Bitmap Index Scan on t_int_2_x_idx  (cost=0.00..9.25 rows=680 width=0)
               Index Cond: (x > 100)
   ->  Bitmap Heap Scan on t_int_3  (cost=9.42..27.92 rows=680 width=12)
         Recheck Cond: (x > 100)
         ->  Bitmap Index Scan on t_int_3_x_idx  (cost=0.00..9.25 rows=680 width=0)
               Index Cond: (x > 100)
(13 rows)
```

--------------------------------

### Production Example: Unix Password File Emulation

Source: https://postgrespro.com/docs/enterprise/18/ddl-rowsecurity

A comprehensive example showing table creation, role setup, enabling row-level security, and defining specific policies for administrators and normal users.

```sql
-- Simple passwd-file based example
CREATE TABLE passwd (
  user_name             text UNIQUE NOT NULL,
  pwhash                text,
  uid                   int  PRIMARY KEY,
  gid                   int  NOT NULL,
  real_name             text NOT NULL,
  home_phone            text,
  extra_info            text,
  home_dir              text NOT NULL,
  shell                 text NOT NULL
);

CREATE ROLE admin;  -- Administrator
CREATE ROLE bob;    -- Normal user
CREATE ROLE alice;  -- Normal user

-- Populate the table
INSERT INTO passwd VALUES
  ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
  ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
  ('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

-- Be sure to enable row-level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
CREATE POLICY user_mod ON passwd FOR UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = user_name AND
    shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
  );

-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;
```

--------------------------------

### Start server (pg_ctl start)

Source: https://postgrespro.com/docs/enterprise/18/app-pg-ctl

Starts the server and waits until it is accepting connections.

```shell
$ **pg_ctl start**
```

--------------------------------

### pgbouncer.ini configuration example

Source: https://postgrespro.com/docs/enterprise/18/pgbouncer

Basic pgbouncer configuration file with database mapping and pgbouncer settings. Used as the first step in the quick start.

```ini
[databases]
template1 = host=localhost dbname=template1 auth_user=someuser

[pgbouncer]
listen_port = 6432
listen_addr = localhost
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser

```

--------------------------------

### Solaris autostart line in /etc/init.d/postgresql

Source: https://postgrespro.com/docs/enterprise/18/server-start

Creates /etc/init.d/postgresql on Solaris with the given line to start the server using su. Use proper installation directory and user name.

```bash
su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"
```

--------------------------------

### Example postgresql.conf contents

Source: https://postgrespro.com/docs/enterprise/18/config-setting.html

Shows a minimal example of a postgresql.conf file with a comment, log_connections, log_destination, search_path, and shared_buffers. One parameter per line; the equal sign is optional; whitespace is insignificant except within quoted values; hash marks start comments; non-identifier/number values must be single-quoted.

```plaintext
# This is a comment
log_connections = all
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB

```

--------------------------------

### IF-THEN-ELSE Examples

Source: https://postgrespro.com/docs/enterprise/18/plpgsql-control-structures.html

Examples demonstrating conditional returns and database inserts.

```plpgsql
IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
```

```plpgsql
IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;
```

--------------------------------

### Example database configuration

Source: https://postgrespro.com/docs/enterprise/18/pgbouncer

Example showing how to map database names to specific hosts and database names.

```ini
foodb = host=host1.example.com port=5432
bardb = host=localhost dbname=bazdb
```

--------------------------------

### pg_get_wal_records_info example

Source: https://postgrespro.com/docs/enterprise/18/pgwalinspect

Example of calling pg_get_wal_records_info with a start and end LSN, returning one row per WAL record. Raises an error if the start LSN is not available.

```sql
postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;
-[ RECORD 1 ]----+--------------------------------------------------------------
start_lsn        | 0/1E913618
end_lsn          | 0/1E913650
prev_lsn         | 0/1E9135A0
xid              | 0
resource_manager | Standby
record_type      | RUNNING_XACTS
record_length    | 50
main_data_length | 24
fpi_length       | 0
description      | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 33775
block_ref        |

```

--------------------------------

### OPEN FOR Example

Source: https://postgrespro.com/docs/enterprise/18/plpgsql-cursors

Example of opening an unbound cursor with a static SELECT query.

```sql
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
```

--------------------------------

### Initialize pgpro_rp and Create a Prioritization Plan

Source: https://postgrespro.com/docs/enterprise/18/pgpro-rp

This example demonstrates how to enable the extension, create a user, define a resource prioritization plan, and assign that plan to the user.

```sql
CREATE EXTENSION pgpro_rp;
CREATE USER test_user;
SELECT pgpro_rp_create_plan(
 'test_plan',
 '{ "session_cpu_weight":8, "session_ioread_weight":8, "session_iowrite_weight":8 }'
);
SELECT pgpro_rp_create_role_plan('test_user', 'test_plan');
```

--------------------------------

### database_files usage example

Source: https://postgrespro.com/docs/enterprise/18/pgpro-validate

Example of running the database_files consistency check.

```bash
pgpro_validate database_files \
    --pgdata=/var/lib/postgresql/15/data \
    --username=postgres
```

--------------------------------

### Example: Using GET STACKED DIAGNOSTICS in exception handler

Source: https://postgrespro.com/docs/enterprise/18/plpgsql-control-structures.html

Example of using GET STACKED DIAGNOSTICS inside an EXCEPTION WHEN OTHERS block to capture MESSAGE_TEXT, PG_EXCEPTION_DETAIL, and PG_EXCEPTION_HINT into text variables.

```sql
DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- some processing which might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;
```

--------------------------------

### Start server with options (pg_ctl -o "-F -p 5433" start)

Source: https://postgrespro.com/docs/enterprise/18/app-pg-ctl

Starts the server using port 5433 and disables fsync.

```shell
$ **pg_ctl -o "-F -p 5433" start**
```

--------------------------------

### Start upgraded node3 server

Source: https://postgrespro.com/docs/enterprise/18/logical-replication-upgrade

Start the upgraded PostgreSQL server instance on node3.

```bash
pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile
```

--------------------------------

### Example Proxima Primary-Standby Configuration

Source: https://postgrespro.com/docs/enterprise/18/proxima

A concrete example of guc cluster configuration for a primary-standby setup.

```text
proxima.cluster_mode = 'guc'
proxima.cluster_config = '0,192.168.0.57,4590,P;1,192.168.0.77,4590,S;'
proxima.cluster_node_id = 0
```

--------------------------------

### SHOW ALL example

Source: https://postgrespro.com/docs/enterprise/18/sql-show

Displays the values of all configuration parameters with descriptions. The output is truncated with ellipses and shows a row count.

```sql
SHOW ALL;
            name         | setting |                description
-------------------------+---------+-------------------------------------------------
 allow_system_table_mods | off     | Allows modifications of the structure of ...
    .
    .
    .
 xmloption               | content | Sets whether XML data in implicit parsing ...
 zero_damaged_pages      | off     | Continues processing past damaged page headers.
(196 rows)

```

--------------------------------

### SET CONNECTION Examples

Source: https://postgrespro.com/docs/enterprise/18/ecpg-sql-set-connection

Examples showing how to switch the current database connection to a previously established connection name.

```sql
EXEC SQL SET CONNECTION TO con2;
EXEC SQL SET CONNECTION = con1;
```

--------------------------------

### GET DIAGNOSTICS Example

Source: https://postgrespro.com/docs/enterprise/18/plpgsql-statements

An example of retrieving the row count of the most recent SQL command into an integer variable.

```sql
GET DIAGNOSTICS integer_var = ROW_COUNT;
```

--------------------------------

### Complete SRF pseudo-code example using SRF_IS_FIRSTCALL and SRF_RETURN_NEXT

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Shows the full structure of a PostgreSQL C-language set-returning function. Use SRF_IS_FIRSTCALL() for one-time setup, switch to funcctx->multi_call_memory_ctx for cross-call data, and use SRF_PERCALL_SETUP() before each call. Return rows with SRF_RETURN_NEXT() and finish with SRF_RETURN_DONE(). The comment warns against putting cleanup code before SRF_RETURN_DONE().

```c
Datum
my_set_returning_function(PG_FUNCTION_ARGS)
{
    FuncCallContext  *funcctx;
    Datum             result;
    _further declarations as needed_

    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext oldcontext;

        funcctx = SRF_FIRSTCALL_INIT();
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
        /* One-time setup code appears here: */
        _user code_
        _if returning composite_
            _build TupleDesc, and perhaps AttInMetadata_
        _endif returning composite_
        _user code_
        MemoryContextSwitchTo(oldcontext);
    }

    /* Each-time setup code appears here: */
    _user code_
    funcctx = SRF_PERCALL_SETUP();
    _user code_

    /* this is just one way we might test whether we are done: */
    if (funcctx->call_cntr < funcctx->max_calls)
    {
        /* Here we want to return another item: */
        _user code_
        _obtain result Datum_
        SRF_RETURN_NEXT(funcctx, result);
    }
    else
    {
        /* Here we are done returning items, so just report that fact. */
        /* (Resist the temptation to put cleanup code here.) */
        SRF_RETURN_DONE(funcctx);
    }
}
```

--------------------------------

### Example: get error message for connection 'dtest1'

Source: https://postgrespro.com/docs/enterprise/18/contrib-dblink-error-message

Example query that calls dblink_error_message on the connection named 'dtest1'.

```sql
SELECT dblink_error_message('dtest1');

```

--------------------------------

### Start migration with bihactl upgrade start

Source: https://postgrespro.com/docs/enterprise/18/biha-administration.html

Run this command on the prepared additional server to initiate the migration. Ensure the PGDATA directories for both versions are empty before execution.

```bash
bihactl upgrade start \
        --magic-string=_magic_string_of_old_leader_ \
        --new-leader-biha-port=_new_leader_biha_port_ \
        --new-leader-host=_new_leader_host_ \
        --new-leader-port=_new_leader_port_ \
        --old-bin=_/path/to/binaries/of/version17_ \
        --old-pgdata=_PGDATA_version17_ \
        --new-pgdata=_PGDATA_version18_ \
        --username=postgres
```

--------------------------------

### CREATE SERVER Example

Source: https://postgrespro.com/docs/enterprise/18/sql-createserver

An example of creating a server named 'myserver' that utilizes the 'postgres_fdw' foreign-data wrapper with specific connection options.

```sql
CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname 'foodb', port '5432');
```

--------------------------------

### Initialize database with pg-setup

Source: https://postgrespro.com/docs/enterprise/18/binary-installation-on-linux.html

Run this command as root to create the default database. You can provide initdb options and the tune argument to customize the database configuration.

```bash
/opt/pgpro/ent-18/bin/pg-setup initdb [_initdb_options_]
```

--------------------------------

### Get current timestamp with now

Source: https://postgrespro.com/docs/enterprise/18/functions-datetime

Returns the current date and time at the start of the transaction.

```sql
now()
```

--------------------------------

### Setup User, Table, and Procedure

Source: https://postgrespro.com/docs/enterprise/18/pgpro-usage.html

Creates a new user, a table, grants access, and defines a procedure to prepare for usage statistics tracking.

```sql
postgres=# CREATE USER u1;
CREATE ROLE
postgres=# CREATE TABLE t1(val int);
CREATE TABLE
postgres=# GRANT all on t1 to u1;
GRANT
postgres=# CREATE PROCEDURE p1() AS $$ BEGIN RAISE NOTICE 'Here we are'; END; $$ LANGUAGE plpgsql;
CREATE PROCEDURE
```

--------------------------------

### pg_get_wal_record_info example

Source: https://postgrespro.com/docs/enterprise/18/pgwalinspect

Example of calling pg_get_wal_record_info with an LSN. If the LSN is not at the start of a WAL record, information about the next valid record is shown; if none exists, the function raises an error.

```sql
postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');
-[ RECORD 1 ]----+-------------------------------------------------
start_lsn        | 0/E419E28
end_lsn          | 0/E419E68
prev_lsn         | 0/E419D78
xid              | 0
resource_manager | Heap2
record_type      | VACUUM
record_length    | 58
main_data_length | 2
fpi_length       | 0
description      | nunused: 5, unused: [1, 2, 3, 4, 5]
block_ref        | blkref #0: rel 1663/16385/1249 fork main blk 364

```

--------------------------------

### Small example configuration

Source: https://postgrespro.com/docs/enterprise/18/pgbouncer

A basic configuration example showing database definitions and core PgBouncer settings.

```ini
[databases]
template1 = host=localhost dbname=template1 auth_user=someuser

[pgbouncer]
pool_mode = session
listen_port = 6432
listen_addr = localhost
auth_type = md5
auth_file = users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser
stats_users = stat_collector
```

--------------------------------

### Begin a Transaction Block

Source: https://postgrespro.com/docs/enterprise/18/sql-begin

Example of starting a transaction block using the BEGIN command.

```sql
BEGIN;
```

--------------------------------

### Example: set up roles with INHERIT and SET options

Source: https://postgrespro.com/docs/enterprise/18/role-membership

Creates several roles and grants membership with different WITH INHERIT and WITH SET options to illustrate privilege inheritance.

```sql
CREATE ROLE joe LOGIN;
CREATE ROLE admin;
CREATE ROLE wheel;
CREATE ROLE island;
GRANT admin TO joe WITH INHERIT TRUE;
GRANT wheel TO admin WITH INHERIT FALSE;
GRANT island TO joe WITH INHERIT TRUE, SET FALSE;

```

--------------------------------

### Retrieve Call Stack with GET DIAGNOSTICS

Source: https://postgrespro.com/docs/enterprise/18/plpgsql-control-structures.html

This example demonstrates defining two functions, outer_func and inner_func, where the latter uses GET DIAGNOSTICS to capture and display the current call stack.

```plpgsql
CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
  RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
  stack text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE E'--- Call Stack ---\n%', stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Call Stack ---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
 ------------
           1
(1 row)
```

--------------------------------

### libpq Binary I/O and Parameters Example (testlibpq3.c)

Source: https://postgrespro.com/docs/enterprise/18/libpq-example

Demonstrates the use of out-of-line parameters and binary I/O in libpq. Requires a pre-populated table as described in the code comments.

```c
/*
 * src/test/examples/testlibpq3.c
 *
 *
 * testlibpq3.c
 *      Test out-of-line parameters and binary I/O.
 *
 * Before running this, populate a database with the following commands
 * (provided in src/test/examples/testlibpq3.sql):
 *
 * CREATE SCHEMA testlibpq3;
 * SET search_path = testlibpq3;
 * SET standard_conforming_strings = ON;
 * CREATE TABLE test1 (i int4, t text, b bytea);
 * INSERT INTO test1 values (1, 'joe''s place', '\000\001\002\003\004');
 * INSERT INTO test1 values (2, 'ho there', '\004\003\002\001\000');
 *
 * The expected output is:
 *
 * tuple 0: got
 *  i = (4 bytes) 1
 *  t = (11 bytes) 'joe's place'
 *  b = (5 bytes) \000\001\002\003\004
 *
 * tuple 0: got
 *  i = (4 bytes) 2
 *  t = (8 bytes) 'ho there'
 *  b = (5 bytes) \004\003\002\001\000
 */

#ifdef WIN32
#include <windows.h>
#endif

#include <stdio.h>
#include <stdlib.h>
#include <stdint.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"
```

--------------------------------

### Audit rule for database with extension

Source: https://postgrespro.com/docs/enterprise/18/pg-proaudit.html

Example of creating an audit rule for a database after installing the extension.

```sql
create database " D b 2";
\c " D b 2"
create extension pg_proaudit;
SELECT pg_proaudit_set_rule(current_database(), 'disconnect', null, null, null);
```

--------------------------------

### DESCRIBE Example

Source: https://postgrespro.com/docs/enterprise/18/ecpg-sql-describe

A complete example showing how to allocate a descriptor, prepare a statement, describe it, retrieve metadata, and deallocate the descriptor.

```sql
EXEC SQL ALLOCATE DESCRIPTOR mydesc;
EXEC SQL PREPARE stmt1 FROM :sql_stmt;
EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;
EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :charvar = NAME;
EXEC SQL DEALLOCATE DESCRIPTOR mydesc;
```

--------------------------------

### Get transaction timestamp with transaction_timestamp

Source: https://postgrespro.com/docs/enterprise/18/functions-datetime

Returns the current date and time at the start of the current transaction.

```sql
transaction_timestamp()
```

--------------------------------

### Get statement timestamp with statement_timestamp

Source: https://postgrespro.com/docs/enterprise/18/functions-datetime

Returns the current date and time at the start of the current statement.

```sql
statement_timestamp()
```

--------------------------------

### Start Postgres Pro Enterprise Server

Source: https://postgrespro.com/docs/enterprise/18/proxima

Start the server using pg_ctl after configuration.

```bash
pg_ctl start -D _PGDATA_directory_
```

--------------------------------

### DROP SERVER Example

Source: https://postgrespro.com/docs/enterprise/18/sql-dropserver

An example demonstrating how to drop a server named 'foo' if it exists.

```sql
DROP SERVER IF EXISTS foo;
```

--------------------------------

### Sample output of pg_probackup show

Source: https://postgrespro.com/docs/enterprise/18/app-pgprobackup

This is an example of the detailed configuration and result information returned by the 'show' command.

```text
#Configuration
backup-mode = FULL
stream = false
compress-alg = lz4
compress-level = 1
from-replica = false

#Compatibility
block-size = 8192
xlog-block-size = 8192
checksum-version = 1
program-version = 2.7.3
server-version = 17

#Result backup info
timelineid = 1
start-lsn = 0/41000028
stop-lsn = 0/420000C0
start-time = '2024-04-09 18:19:52+03'
end-time = '2024-04-09 18:19:58+03'
end-validation-time = '2024-04-09 18:19:59+03'
recovery-xid = 757
recovery-time = '2024-04-09 18:19:56.603355+03'
data-bytes = 395651278
wal-bytes = 16777216
uncompressed-bytes = 578552566
pgdata-bytes = 578552248
status = OK
primary_conninfo = 'user=backup channel_binding=prefer host=localhost port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
content-crc = 3862224379
```

--------------------------------

### Install the small demo database

Source: https://postgrespro.com/docs/enterprise/18/demodb-bookings-installation

Use this command to create and populate the 'demo' database using the small 3-month dataset. The script must be run as the 'postgres' user, and note that any existing 'demo' database will be overwritten.

```bash
gunzip -c demo-20250901-3m.sql.gz | psql -U postgres
```

--------------------------------

### regexp_count Examples

Source: https://postgrespro.com/docs/enterprise/18/functions-matching.html

The regexp_count function counts matches of a POSIX regular expression pattern in a string. The optional start parameter specifies the starting character index, and the flags parameter can include 'i' for case-insensitive matching.

```sql
regexp_count('ABCABCAXYaxy', 'A.')
```

```sql
regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')
```

--------------------------------

### Initialize the backup catalog with pg_probackup init

Source: https://postgrespro.com/docs/enterprise/18/app-pgprobackup

Run this command to initialize the backup catalog. The backup directory must not exist or must be empty, otherwise an error is returned.

```bash
pg_probackup init -B _backup_dir_
```

--------------------------------

### Initial state of table mytab

Source: https://postgrespro.com/docs/enterprise/18/transaction-iso

The starting data set for the example scenario involving concurrent serializable transactions.

```text
 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200
```

--------------------------------

### Version-1 Calling Convention Examples

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Examples demonstrating various C function implementations for PostgreSQL, including handling pass-by-value, pass-by-reference (fixed and variable length), and calling existing functions with collation.

```c
#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"
#include "varatt.h"

PG_MODULE_MAGIC;

/* by value */

PG_FUNCTION_INFO_V1(add_one);

Datum
add_one(PG_FUNCTION_ARGS)
{
    int32   arg = PG_GETARG_INT32(0);

    PG_RETURN_INT32(arg + 1);
}

/* by reference, fixed length */

PG_FUNCTION_INFO_V1(add_one_float8);

Datum
add_one_float8(PG_FUNCTION_ARGS)
{
    /* The macros for FLOAT8 hide its pass-by-reference nature. */
    float8   arg = PG_GETARG_FLOAT8(0);

    PG_RETURN_FLOAT8(arg + 1.0);
}

PG_FUNCTION_INFO_V1(makepoint);

Datum
makepoint(PG_FUNCTION_ARGS)
{
    /* Here, the pass-by-reference nature of Point is not hidden. */
    Point     *pointx = PG_GETARG_POINT_P(0);
    Point     *pointy = PG_GETARG_POINT_P(1);
    Point     *new_point = (Point *) palloc(sizeof(Point));

    new_point->x = pointx->x;
    new_point->y = pointy->y;

    PG_RETURN_POINT_P(new_point);
}

/* by reference, variable length */

PG_FUNCTION_INFO_V1(copytext);

Datum
copytext(PG_FUNCTION_ARGS)
{
    text     *t = PG_GETARG_TEXT_PP(0);

    /*
     * VARSIZE_ANY_EXHDR is the size of the struct in bytes, minus the
     * VARHDRSZ or VARHDRSZ_SHORT of its header.  Construct the copy with a
     * full-length header.
     */
    text     *new_t = (text *) palloc(VARSIZE_ANY_EXHDR(t) + VARHDRSZ);
    SET_VARSIZE(new_t, VARSIZE_ANY_EXHDR(t) + VARHDRSZ);

    /*
     * VARDATA is a pointer to the data region of the new struct.  The source
     * could be a short datum, so retrieve its data through VARDATA_ANY.
     */
    memcpy(VARDATA(new_t),          /* destination */
           VARDATA_ANY(t),          /* source */
           VARSIZE_ANY_EXHDR(t));   /* how many bytes */
    PG_RETURN_TEXT_P(new_t);
}

PG_FUNCTION_INFO_V1(concat_text);

Datum
concat_text(PG_FUNCTION_ARGS)
{
    text  *arg1 = PG_GETARG_TEXT_PP(0);
    text  *arg2 = PG_GETARG_TEXT_PP(1);
    int32 arg1_size = VARSIZE_ANY_EXHDR(arg1);
    int32 arg2_size = VARSIZE_ANY_EXHDR(arg2);
    int32 new_text_size = arg1_size + arg2_size + VARHDRSZ;
    text *new_text = (text *) palloc(new_text_size);

    SET_VARSIZE(new_text, new_text_size);
    memcpy(VARDATA(new_text), VARDATA_ANY(arg1), arg1_size);
    memcpy(VARDATA(new_text) + arg1_size, VARDATA_ANY(arg2), arg2_size);
    PG_RETURN_TEXT_P(new_text);
}

/* A wrapper around starts_with(text, text) */

PG_FUNCTION_INFO_V1(t_starts_with);

Datum
t_starts_with(PG_FUNCTION_ARGS)
{
    text       *t1 = PG_GETARG_TEXT_PP(0);
    text       *t2 = PG_GETARG_TEXT_PP(1);
    Oid         collid = PG_GET_COLLATION();
    bool        result;

    result = DatumGetBool(DirectFunctionCall2Coll(text_starts_with,
                                                  collid,
                                                  PointerGetDatum(t1),
                                                  PointerGetDatum(t2)));
    PG_RETURN_BOOL(result);
}
```

--------------------------------

### Standard SQL comment example

Source: https://postgrespro.com/docs/enterprise/18/sql-syntax-lexical.html

Shows a single-line comment starting with double dashes, which extends to the end of the line.

```sql
-- This is a standard SQL comment
```

--------------------------------

### OPEN command examples

Source: https://postgrespro.com/docs/enterprise/18/ecpg-sql-open

Examples of opening cursors using identifiers, host variables, and descriptors in ECPG.

```sql
EXEC SQL OPEN a;
EXEC SQL OPEN d USING 1, 'test';
EXEC SQL OPEN c1 USING SQL DESCRIPTOR mydesc;
EXEC SQL OPEN :curname1;
```

--------------------------------

### Example injection point callback

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

A sample implementation of an InjectionPointCallback that reports wait events and logs a notice.

```c
static void
custom_injection_callback(const char *name,
                          const void *private_data,
                          void *arg)
{
    uint32 wait_event_info = WaitEventInjectionPointNew(name);

    pgstat_report_wait_start(wait_event_info);
    elog(NOTICE, "%s: executed custom callback", name);
    pgstat_report_wait_end();
}
```

--------------------------------

### Create a database with default settings

Source: https://postgrespro.com/docs/enterprise/18/app-createdb

Creates a database named demo using the default database server connection settings.

```bash
$ **createdb demo**
```

--------------------------------

### EXPLAIN with FORMAT YAML

Source: https://postgrespro.com/docs/enterprise/18/sql-explain

Shows the same query as the previous example but with YAML output formatting using EXPLAIN (FORMAT YAML).

```sql
EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
          QUERY PLAN
-------------------------------
 - Plan:                      +
     Node Type: "Index Scan"  +
     Scan Direction: "Forward"+
     Index Name: "fi"         +
     Relation Name: "foo"     +
     Alias: "foo"             +
     Startup Cost: 0.00       +
     Total Cost: 5.98         +
     Plan Rows: 1             +
     Plan Width: 4            +
     Index Cond: "(i = 4)"
(1 row)

```

--------------------------------

### SQL/JSON like_regex filter example

Source: https://postgrespro.com/docs/enterprise/18/functions-json

A path query that case-insensitively matches all strings in an array starting with an English vowel.

```sql
$[*] ? (@ like_regex "^[aeiou]" flag "i")
```

--------------------------------

### PG_MODULE_MAGIC_EXT with name and version

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Example of PG_MODULE_MAGIC_EXT with a name and version string. The version string meaning is not restricted, but semantic versioning is recommended.

```c
PG_MODULE_MAGIC_EXT(
    .name = "my_module_name",
    .version = "1.2.3"
);

```

--------------------------------

### Retrieve approximate table statistics with pgstattuple_approx

Source: https://postgrespro.com/docs/enterprise/18/pgstattuple.html

Example of using pgstattuple_approx to get faster, approximate statistics for a table.

```sql
test=> SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);
-[ RECORD 1 ]--------+-------
table_len            | 573440
scanned_percent      | 2
approx_tuple_count   | 2740
approx_tuple_len     | 561210
approx_tuple_percent | 97.87
dead_tuple_count     | 0
dead_tuple_len       | 0
dead_tuple_percent   | 0
approx_free_space    | 11996
approx_free_percent  | 2.09
```

--------------------------------

### Initialize Shared Memory with AddinShmemInitLock

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Example of using AddinShmemInitLock to safely initialize shared memory and assign a tranche name to avoid race conditions.

```c
static mystruct *ptr = NULL;
bool        found;

LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
ptr = ShmemInitStruct("my struct name", size, &found);
if (!found)
{
    ... initialize contents of shared memory ...
    ptr->locks = GetNamedLWLockTranche("my tranche name");
}
LWLockRelease(AddinShmemInitLock);
```

--------------------------------

### Get array element type OID

Source: https://postgrespro.com/docs/enterprise/18/dump-stat.html

Example of using anyarray_elemtype to return the OID of an array's element type.

```sql
test=# SELECT anyarray_elemtype(array_in('{1,2,3}', 'int'::regtype, -1));
 anyarray_elemtype 
-------------------
                23
(1 row)
```

--------------------------------

### Implement a polymorphic make_array function in C

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

This example demonstrates a C function that accepts a single element of any type and returns a one-dimensional array of that type. It uses get_fn_expr_argtype to determine the input type and construct_md_array to build the result.

```c
PG_FUNCTION_INFO_V1(make_array);
Datum
make_array(PG_FUNCTION_ARGS)
{
    ArrayType  *result;
    Oid         element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
    Datum       element;
    bool        isnull;
    int16       typlen;
    bool        typbyval;
    char        typalign;
    int         ndims;
    int         dims[MAXDIM];
    int         lbs[MAXDIM];

    if (!OidIsValid(element_type))
        elog(ERROR, "could not determine data type of input");

    /* get the provided element, being careful in case it's NULL */
    isnull = PG_ARGISNULL(0);
    if (isnull)
        element = (Datum) 0;
    else
        element = PG_GETARG_DATUM(0);

    /* we have one dimension */
    ndims = 1;
    /* and one element */
    dims[0] = 1;
    /* and lower bound is 1 */
    lbs[0] = 1;

    /* get required info about the element type */
    get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);

    /* now build the array */
    result = construct_md_array(&element, &isnull, ndims, dims, lbs,
                                element_type, typlen, typbyval, typalign);

    PG_RETURN_ARRAYTYPE_P(result);
}
```

--------------------------------

### Use the foo package

Source: https://postgrespro.com/docs/enterprise/18/sql-createpackage

Demonstrates interacting with the public members of the foo package.

```sql
DO $$
#import foo
BEGIN
  RAISE INFO 'y = %', foo.y;
  RAISE INFO 'x = %', foo.get_x();
  CALL foo.set_x(25);
  RAISE INFO 'x = %', foo.get_x();
END;
$$;

INFO:  y = 5
INFO:  x = 1
INFO:  x set to natural value
INFO:  x = 25
```

--------------------------------

### Get AttInMetadata for C string processing

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Call this function if you plan to work with C strings.

```c
AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)
```

--------------------------------

### SQL query using c_overpaid function

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Example query that calls the c_overpaid function on the emp table, filtering by name.

```sql
SELECT name, c_overpaid(emp, 1500) AS overpaid
    FROM emp
    WHERE name = 'Bill' OR name = 'Sam';

```

--------------------------------

### Pass configuration parameters to postgres command

Source: https://postgrespro.com/docs/enterprise/18/config-setting.html

Example of passing configuration settings during server startup using the -c and --name command-line parameters.

```bash
postgres -c log_connections=all --log-destination='syslog'
```

--------------------------------

### Server not running error

Source: https://postgrespro.com/docs/enterprise/18/tutorial-createdb

This error means the server was not started or is not listening where createdb expects to contact it. Check installation instructions or consult the administrator.

```shell
createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
```

--------------------------------

### Get Named Dynamic Shared Memory Segment

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Reserves or attaches to a dynamic shared memory segment after startup. The init_callback function is used for initialization if the segment does not exist.

```c
void *GetNamedDSMSegment(const char *name, size_t size,
                         void (*init_callback) (void *ptr),
                         bool *found)
```

--------------------------------

### libpq Binary Result Handling and Connection Example

Source: https://postgrespro.com/docs/enterprise/18/libpq-example

This example demonstrates how to connect to a database, set a search path, and process binary-format query results using libpq functions like PQfnumber, PQgetvalue, and PQgetlength.

```c
/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>


static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

/*
 * This function prints a query result that is a binary-format fetch from
 * a table defined as in the comment above.  We split it out because the
 * main() function uses it twice.
 */
static void
show_binary_results(PGresult *res)
{
    int         i,
                j;
    int         i_fnum,
                t_fnum,
                b_fnum;

    /* Use PQfnumber to avoid assumptions about field order in result */
    i_fnum = PQfnumber(res, "i");
    t_fnum = PQfnumber(res, "t");
    b_fnum = PQfnumber(res, "b");

    for (i = 0; i < PQntuples(res); i++)
    {
        char       *iptr;
        char       *tptr;
        char       *bptr;
        int         blen;
        int         ival;

        /* Get the field values (we ignore possibility they are null!) */
        iptr = PQgetvalue(res, i, i_fnum);
        tptr = PQgetvalue(res, i, t_fnum);
        bptr = PQgetvalue(res, i, b_fnum);

        /*
         * The binary representation of INT4 is in network byte order, which
         * we'd better coerce to the local byte order.
         */
        ival = ntohl(*((uint32_t *) iptr));

        /*
         * The binary representation of TEXT is, well, text, and since libpq
         * was nice enough to append a zero byte to it, it'll work just fine
         * as a C string.
         *
         * The binary representation of BYTEA is a bunch of bytes, which could
         * include embedded nulls so we have to pay attention to field length.
         */
        blen = PQgetlength(res, i, b_fnum);

        printf("tuple %d: got\n", i);
        printf(" i = (%d bytes) %d\n",
               PQgetlength(res, i, i_fnum), ival);
        printf(" t = (%d bytes) '%s'\n",
               PQgetlength(res, i, t_fnum), tptr);
        printf(" b = (%d bytes) ", blen);
        for (j = 0; j < blen; j++)
            printf("\\%03o", bptr[j]);
        printf("\n\n");
    }
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    const char *paramValues[1];
    int         paramLengths[1];
    int         paramFormats[1];
    uint32_t    binaryIntVal;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */
    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "%s", PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /* Set always-secure search path, so malicious users can't take control. */
    res = PQexec(conn, "SET search_path = testlibpq3");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    /*
     * The point of this program is to illustrate use of PQexecParams() with
     * out-of-line parameters, as well as binary transmission of data.
     *
     * This first example transmits the parameters as text, but receives the
     * results in binary format.  By using out-of-line parameters we can avoid
     * a lot of tedious mucking about with quoting and escaping, even though
     * the data is text.  Notice how we don't have to do anything special with
     * the quote mark in the parameter value.
     */

    /* Here is our out-of-line parameter value */
    paramValues[0] = "joe's place";

    res = PQexecParams(conn,
                       "SELECT * FROM test1 WHERE t = $1",
                       1,       /* one param */
                       NULL,    /* let the backend deduce param type */
                       paramValues,
                       NULL,    /* don't need param lengths since text */
                       NULL,    /* default to all text params */
                       1);      /* ask for binary results */

    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    show_binary_results(res);

    PQclear(res);

    /*
     * In this second example we transmit an integer parameter in binary form,
     * and again retrieve the results in binary form.
     *
     * Although we tell PQexecParams we are letting the backend deduce
     * parameter type, we really force the decision by casting the parameter
     * symbol in the query text.  This is a good safety measure when sending
     * binary parameters.
     */
```

--------------------------------

### Define text type structure

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Example of defining a variable-length text type using a flexible array member.

```c
typedef struct {
    int32 length;
    char data[FLEXIBLE_ARRAY_MEMBER];
} text;
```

--------------------------------

### Obtain Pointer to Reserved Shared Memory

Source: https://postgrespro.com/docs/enterprise/18/xfunc-c.html

Use this function to get a pointer to the shared memory reserved at startup. The foundPtr parameter indicates whether the memory was already initialized.

```c
void *ShmemInitStruct(const char *name, Size size, bool *foundPtr)
```

--------------------------------

### Configure PSQL_EDITOR_LINENUMBER_ARG

Source: https://postgrespro.com/docs/enterprise/18/app-psql

Examples of setting the command-line argument used to pass a starting line number to the editor when using \e, \ef, or \ev.

```bash
PSQL_EDITOR_LINENUMBER_ARG='+'
PSQL_EDITOR_LINENUMBER_ARG='--line '
```

--------------------------------

### mtm.join_node(node_id int, backup_end_lsn pg_lsn)

Source: https://postgrespro.com/docs/enterprise/18/multimaster

Completes the cluster setup after adding a new node. This function should be called after the added node has been started.

```APIDOC
## mtm.join_node(node_id int, backup_end_lsn pg_lsn)

### Description
Completes the cluster setup after adding a new node. This function should be called after the added node has been started.

### Function Signature
mtm.join_node(node_id int, backup_end_lsn pg_lsn)

### Parameters
- **node_id** (int) - Required - ID of the node to add to the cluster. It corresponds to the value in the id column returned by mtm.nodes().
- **backup_end_lsn** (pg_lsn) - Required - The last LSN of the base backup copied to the new node. This LSN will be used as the starting point for data replication once the node joins the cluster.

### Return Value
No return value specified.
```

--------------------------------

### Setup test table and index

Source: https://postgrespro.com/docs/enterprise/18/pgpro-stats.html

Create a sample table with random data and an index to generate statistics for query analysis.

```sql
CREATE TABLE test AS (SELECT i, random() x FROM generate_series(1,1000000) i);
CREATE INDEX test_x_idx ON test (x);
```