Function reference¶

Everything this extension exposes to you, in one place.

`pgvault_tables.view_vault_tables()`¶

Lists every vault table with its settings.

Returns

Column	Type	Description
`schema_name`	`name`	The schema the table is in
`table_name`	`name`	The table
`permissions`	`text`	Comma-separated, in a fixed order
`update_columns`	`text`	The columns `update` is narrowed to, or NULL where every column may be changed
`retention_days`	`int`	Null if the table has no retention period

Example

SELECT * FROM pgvault_tables.view_vault_tables();

Callable by anyone. See Seeing What Is Defined.

`pgvault_tables.purge_vault(schema_name, table_name)`¶

Deletes rows whose retention deadline has passed.

Arguments

Argument	Type	Default	Description
`schema_name`	`name`	`NULL`	Limit to one schema. Null means all schemas.
`table_name`	`name`	`NULL`	Limit to one table. Requires `schema_name`.

Examples

CALL pgvault_tables.purge_vault();
CALL pgvault_tables.purge_vault('archive');
CALL pgvault_tables.purge_vault('archive', 'statements');
CALL pgvault_tables.purge_vault(schema_name => 'archive', table_name => 'statements');

Errors

Condition	Behaviour
`table_name` given without `schema_name`	Raises immediately
Named table is not retention-gated	Raises before deleting anything
Named schema contains nothing to purge	Succeeds, removes nothing

Only tables that set retention are ever visited. Not callable by ordinary roles by default — ask your DBA. See Purging Expired Rows.

Table options¶

Not functions, but this is where people look for them.

`permissions`¶

Compulsory on every vault table. Comma-separated list of insert, insertonce, update, delete, truncate, drop. Case and spacing insignificant.

WITH (permissions = 'insert,delete')

`retention`¶

Optional. Whole number of days, minimum 1.

WITH (permissions = 'insert', retention = 2555)

Adds a _$purge_ts column of type timestamptz holding the moment each row becomes eligible for deletion.

Rejected at creation¶

Combination	Why
No `permissions`	A table permitting nothing, forever, is a mistake
Empty `permissions` list	Same
An unrecognised permission name	Only the six above exist
`insert` with `insertonce`	`insertonce` is a variant of `insert`
`truncate` with `retention`	`TRUNCATE` ignores retention entirely
`retention` of 0 or less	Minimum is 1 day
Any partitioning	Not supported with the vault access method
`CREATE TABLE AS` with `retention`	The deadline column cannot be added to a query-derived table. `CREATE TABLE AS` with `permissions` alone works normally

`pgvault_tables.convert_table(...)`¶

Converts ordinary tables in a schema into vault tables in place, keeping their OIDs and everything that depends on them. Superuser only, and not reversible.

CALL pgvault_tables.convert_table(
    p_schema                 => 'app',
    p_table                  => 'orders',        -- NULL for every table, or a LIKE pattern
    p_insert                 => true,            -- one boolean per permission
    p_delete                 => true,
    p_retention              => 2555,            -- NULL for none
    p_min_date               => '2020-01-01+00', -- required with retention
    p_retention_start_column => 'created_at',    -- a column or any expression
    p_test                   => false,           -- DEFAULTS TO TRUE: report only
    p_stop_on_error          => false);          -- carry on to the next table

p_test defaults to true, so a call that omits it reports what it would do and changes nothing — including how many rows have no start date and will take the floor instead. Read that before passing p_test => false.

Vault tables cannot be created in pg_catalog, information_schema or any other schema PostgreSQL reserves for itself.

Full detail, and the manual equivalent for tables it excludes, is in Converting Existing Tables.

Function reference¶

pgvault_tables.view_vault_tables()¶

pgvault_tables.purge_vault(schema_name, table_name)¶