Query Signature File¶

Query signature files (*.sig1.pgn.yaml) are auto-generated by pGenie alongside each .sql query file. They record the resolved type signature of a query.

Purpose¶

Signature files serve several purposes:

Type contract visibility: Signatures are human-readable YAML and appear in pull request diffs, making schema-driven type changes visible to reviewers.
Schema drift detection: On every run, pGenie re-resolves the query signature from the live database and compares it against the committed sig file. If they differ, the build fails, forcing an explicit acknowledgement of the breaking change.
Constraint tightening: You can manually edit a signature file to tighten constraints that pGenie cannot infer statically. The most common example is marking a parameter as non-nullable when you know callers will always pass a concrete value — for instance, marking $id as not_null: true in a WHERE id = $id clause. pGenie will use your edited version on subsequent runs and generate a non-nullable type for that parameter.

Signature files should be committed to version control.

Signature files are not automatically updated

pGenie writes a signature file the first time a query is analysed. On subsequent runs it reads the existing file, re-resolves the signature from the database, and validates the two against each other. It never silently overwrites your signature files. To regenerate a signature file from scratch, delete it and re-run pgn generate.

Format¶

parameters:
  <param_name>:
    type: <postgres_type>
    not_null: <bool>
  ...
result:
  cardinality: <zero_or_one|one|many>
  columns:
    <column_name>:
      type: <postgres_type>
      not_null: <bool>
    ...

The result section is omitted entirely for queries that return no rows (e.g. UPDATE or DELETE without RETURNING).

Array fields have additional fields:

parameters:
  <param_name>:
    type: <element_postgres_type>
    not_null: <bool>
    dims: <number_of_dimensions>
    element_not_null: <bool>   # only present when true

Example¶

For the query:

-- queries/select_album_by_name.sql

select id, name, released, format, recording
from album
where name = $name

The generated signature file is:

parameters:
  name:
    type: text
    not_null: false
result:
  cardinality: many
  columns:
    id:
      type: int8
      not_null: true
    name:
      type: text
      not_null: true
    released:
      type: date
      not_null: false
    format:
      type: album_format
      not_null: false
    recording:
      type: recording_info
      not_null: false

For a query returning an array column:

-- queries/select_album_with_tracks.sql

select id, tracks
from album
where id = $id

The generated signature file includes array metadata:

parameters:
  id:
    type: int8
    not_null: false
result:
  cardinality: zero_or_one
  columns:
    id:
      type: int8
      not_null: true
    tracks:
      type: track_info
      not_null: false
      dims: 1

The type field for an array column contains the element type name. dims is the number of array dimensions (1 for a simple array, 2 for a 2D array, etc.). element_not_null is only written when it is true.

Fields¶

`parameters`¶

A map of parameter names (matching $param_name in the SQL) to their resolved types.

Field	Description
`type`	PostgreSQL type name (e.g. `text`, `int8`, `album_format`). For array parameters this is the element type.
`not_null`	`true` if the parameter is guaranteed non-null; `false` if nullable
`dims`	(array only) Number of array dimensions (e.g. `1` for `text[]`, `2` for `text[][]`)
`element_not_null`	(array only) `true` if array elements are non-null. Omitted when `false`.

`result`¶

Describes the query's result. Omitted entirely for queries that produce no result set.

Field	Values	Description
`cardinality`	`zero_or_one`, `one`, `many`	`zero_or_one` = zero or one row; `one` = exactly one row; `many` = zero or more rows
`columns`	map	Column names and their types

Each column entry has the same fields as a parameter (type, not_null, and optionally dims and element_not_null).

Type Names¶

The type field uses the PostgreSQL type names that pGenie's shared gen-sdk input model supports for primitive values. The current supported primitive set is:

bit
bool
box
bpchar
bytea
char
cidr
circle
citext
date
datemultirange
daterange
float4
float8
hstore
inet
int2
int4
int4multirange
int4range
int8
int8multirange
int8range
interval
json
jsonb
line
lseg
macaddr
macaddr8
money
name
numeric
nummultirange
numrange
oid
path
pg_lsn
pg_snapshot
point
polygon
text
time
timestamp
timestamptz
timetz
tsmultirange
tsquery
tsrange
tstzmultirange
tstzrange
tsvector
uuid
varbit
varchar
xml

Custom enumerations, composite types, and domains use their declared names (for example, album_format or recording_info).

For array columns and parameters, type holds the element type name (e.g. text for a text[] column, track_info for a track_info[] column). The dims field records the number of dimensions.

Versioning¶

The filename suffix sig1 refers to version 1 of the signature file format. Future breaking changes to the format would introduce sig2.