Functions

Client Instructions

  • set vs set local
    • set changes configuration parameters for the entire session (connection)
    • set local changes configuration parameters only for the current transaction
    • set local settings are automatically reverted when the transaction commits or rolls back. For this reason, it must be executed within a transaction block.
  • plpgsql.extra_errors
    • Controls additional error checking in PL/pgSQL stored procedures and functions
    • Possible values: 'none' (default), 'all', or comma-separated list of specific checks
    • Available checks:
    • 'strict_multi_assignment' ensures variable count matches value count in assignments
    • 'shadowed_variables' warns about variable name conflicts in nested scopes
    • 'too_many_rows' raises errors when SELECT INTO returns multiple rows
    • Generally used during development to catch potential bugs
    • See PostgreSQL PL/pgSQL Extra Checks
  • client_min_messages
    • Controls which message types are sent from the server to the client
    • Possible values (from lowest to highest): debug5, debug4, debug3, debug2, debug1, log, notice (default), warning, error
    • Setting to warning means only WARNING, ERROR, FATAL, and PANIC messages are shown
    • Lower settings show more verbose output, higher settings show fewer messages
    • See PostgreSQL Client Connection Defaults
  • statement_timeout
    • Sets a maximum execution time limit for individual SQL statements
    • Possible values: time intervals (e.g., '10ms', '1s', '5min') or 0 (no timeout, default)
    • Any statement that runs longer than the specified time will be automatically cancelled
    • See PostgreSQL Statement Timeout
  • transaction_timeout
    • Sets a maximum execution time limit for entire transactions (added in PostgreSQL 17)
    • Possible values: time intervals (e.g., '1min', '5min', '1h') or 0 (no timeout, default)
    • Terminates any session with a transaction that runs longer than the specified time
    • Applies to both explicit transactions (BEGIN/COMMIT) and implicit single-statement transactions
    • See PostgreSQL Transaction Timeout

Snippets

    for _key, _value in select key, value from jsonb_each_text(to_jsonb(_record)) loop
        raise warning '% : %', _key, _value; 
    end loop;

Watch out for

Null Equality Always Returns Null

select null='Hello' , null!='Hello', null=null,null!=null;

gives (null, null, null, null)

To do: