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
`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

Function reference¶

pgvault_tables.view_vault_tables()¶

pgvault_tables.purge_vault(schema_name, table_name)¶