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:

Reproducible generation: pGenie can re-run code generators using cached signatures without re-connecting to PostgreSQL, as long as the query SQL and schema have not changed.
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: If a migration changes a column type used by a query, the signature file will be updated when pGenie is next run, and the diff will highlight the change.

Signature files should be committed to version control.

Format¶

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

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

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`)
`not_null`	`true` if the parameter is guaranteed non-null; `false` if nullable

`result`¶

Describes the query's result.

Field	Values	Description
`cardinality`	`one`, `many`, `none`	`one` = at most one row; `many` = zero or more rows; `none` = no result set
`columns`	map	Column names and their types (only present when `cardinality` is `one` or `many`)

Each column entry has the same type and not_null fields as a parameter.

Type Names¶

The type field uses PostgreSQL's built-in type names for primitive types:

PostgreSQL type	`type` value
`boolean`	`bool`
`smallint`	`int2`
`integer`	`int4`
`bigint`	`int8`
`real`	`float4`
`double precision`	`float8`
`numeric`	`numeric`
`text`	`text`
`date`	`date`
`timestamp`	`timestamp`
`timestamp with time zone`	`timestamptz`
`uuid`	`uuid`
`bytea`	`bytea`
`jsonb`	`jsonb`

Custom enumerations and composite types use their declared names (e.g. album_format, recording_info).

Array types are expressed as the element type with a [] suffix (e.g. text[], track_info[]).

Versioning¶

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