Seeing what is defined

One call lists every vault table and what it allows.

SELECT * FROM pgvault_tables.view_vault_tables();

 schema_name | table_name |     permissions      | update_columns | retention_days
-------------+------------+----------------------+----------------+----------------
 archive     | statements | insert               |                |           2555
 public      | audit_log  | insert               |                |
 public      | ledger     | insert,update,delete | status,notes   |
 reference   | currencies | insertonce           |                |

Anyone can call it. It is a convenience, not a secret — the same information is already visible in PostgreSQL's own catalogue to anybody who cares to look. The function simply returns it in a sensible shape instead of making you join system tables and unpick an options array.

---

Reading the output

permissions is always shown in the same order regardless of how you typed it, so two tables with the same permissions always look the same. If you wrote 'drop,insert', it comes back as insert,drop.

update_columns lists the columns update is narrowed to, and is blank when it is not narrowed. Blank therefore means one of two things — either update was granted for every column, or it was not granted at all — and the permissions column tells you which. It is reported separately rather than folded into permissions so that comparing what two tables grant stays a plain string comparison.

A name is shown quoted when it needs quoting, exactly as it is stored, so "Reconciled By" appears with its quotes.

retention_days is blank (null) for a table that never set retention. That distinction matters: blank means "no retention period", not "zero days".

---

Useful queries

Everything in one schema:

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE schema_name = 'archive';

Everything with a retention period:

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE retention_days IS NOT NULL
 ORDER BY retention_days DESC;

Anything that can be dropped — worth reviewing occasionally:

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE permissions LIKE '%drop%';

Anything where retention is not actually protecting the rows, because delete is also granted:

SELECT * FROM pgvault_tables.view_vault_tables()
 WHERE retention_days IS NOT NULL
   AND permissions LIKE '%delete%';

That last one is worth running now and then. It finds tables that look protected but are not — see Retention.

---

Checking one table

To see the deadline on individual rows, query the table itself:

SELECT id, _$purge_ts, _$purge_ts < now() AS can_be_purged
  FROM archive.statements
 ORDER BY _$purge_ts
 LIMIT 10;