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.

For signature files of PostgreSQL enums and composite types under types/, see Type Signature File.

---

Purpose

Signature files serve several purposes:

1. Type contract visibility: Signatures are human-readable YAML and appear in pull request diffs, making schema-driven type changes visible to reviewers.
2. 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.
3. 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).

idempotent

States that executing the query multiple times with the same parameters has the same effect as executing it once. By default this is false for all queries. However when you know a query is idempotent, you can set this to true and the code generators will be able to generate automatic retry logic for transient errors on such queries without risking unintended side effects.

---

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
• ltree
• 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.