PostgreSQL power queries

On PostgreSQL:

• DISTINCT ON (boat_id) keeps the first row of each boat in ORDER BY order
• LEFT JOIN LATERAL (...) ON true lets a subquery see the outer row; its columns become pointers, as a boat may have no landings
• A TSVECTOR GENERATED ALWAYS AS (...) STORED column with a GIN index, matched with @@ websearch_to_tsquery and scored by ts_rank as *float32; :terms is used twice and bound once
• jsonb_each used as a table gives key as string and value as store.JSON
• A data-modifying CTE: DELETE ... RETURNING feeds INSERT ... SELECT, and the INSERT's RETURNING rows come back as one many query

PostgreSQL has SQL of its own that can replace a second round trip or a loop in Go: DISTINCT ON, LATERAL joins, full-text search, functions used as tables, and common table expressions that write. Seagull understands all of them, so these queries are written exactly as PostgreSQL wants them and still come back as typed rows, with nullability worked out for you.

The example keeps a week of landings at a small harbour: three boats, five landings, and one query for each feature.

The schema

boats.gear is JSONB. landings.search is a generated column: PostgreSQL fills it with a tsvector built from the species and the notes, and a GIN index makes searching it fast. landing_archive is where old landings are moved to.

db/migrations/0001_harbour.up.sql

CREATE TABLE boats (
    id   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT   NOT NULL,
    gear JSONB  NOT NULL DEFAULT '{}'
);

CREATE TABLE landings (
    id        BIGINT   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    boat_id   BIGINT   NOT NULL REFERENCES boats (id),
    species   TEXT     NOT NULL,
    kilos     INTEGER  NOT NULL,
    landed_on DATE     NOT NULL,
    notes     TEXT     NOT NULL,
    search    TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', species || ' ' || notes)) STORED
);

CREATE INDEX landings_search_idx ON landings USING GIN (search);

CREATE TABLE landing_archive (
    id        BIGINT  PRIMARY KEY,
    boat_id   BIGINT  NOT NULL REFERENCES boats (id),
    species   TEXT    NOT NULL,
    kilos     INTEGER NOT NULL,
    landed_on DATE    NOT NULL
);

The latest landing per boat: DISTINCT ON

DISTINCT ON (landings.boat_id) keeps the first row for each boat, in the order ORDER BY gives. Ordering each boat's landings newest first makes that row its latest landing: one row per boat, without a subquery or a window function.

db/queries/harbour.sql

-- sgl.query(LatestLandings, many)
SELECT DISTINCT ON (landings.boat_id) boats.name, landings.species, landings.landed_on
FROM landings
JOIN boats ON boats.id = landings.boat_id
ORDER BY landings.boat_id, landings.landed_on DESC, landings.id DESC;

The two biggest per boat: LEFT JOIN LATERAL

A LATERAL subquery can see the columns of the tables before it, so this one runs once per boat, using boats.id, and keeps its two biggest landings. LEFT JOIN ... ON true keeps a boat that has no landings at all.

db/queries/harbour.sql

-- sgl.query(BiggestLandings, many)
SELECT boats.name, biggest.species, biggest.kilos
FROM boats
LEFT JOIN LATERAL (
    SELECT species, kilos
    FROM landings
    WHERE landings.boat_id = boats.id
    ORDER BY kilos DESC
    LIMIT 2
) AS biggest ON true
ORDER BY boats.name, biggest.kilos DESC;

Seagull follows the LEFT JOIN: for a boat with nothing landed, the subquery's columns are NULL, so they are pointers, while name is not.

internal/store

type BiggestLandingsRow struct {
	Name    string  `json:"name"`
	Species *string `json:"species"`
	Kilos   *int32  `json:"kilos"`
}

func (queries *Queries) BiggestLandings(ctx context.Context) ([]BiggestLandingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, biggestLandingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BiggestLandingsRow
	for rows.Next() {
		var row BiggestLandingsRow
		if err := rows.Scan(&row.Name, &row.Species, &row.Kilos); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Searching the notes: full-text search

@@ matches the generated tsvector against a query, and websearch_to_tsquery accepts the syntax people type into search boxes, such as crab or pollock. ts_rank scores each match, typed as a *float32.

db/queries/harbour.sql

-- sgl.query(SearchNotes, many)
SELECT landings.id, landings.notes, ts_rank(landings.search, websearch_to_tsquery('english', :terms)) AS rank
FROM landings
WHERE landings.search @@ websearch_to_tsquery('english', :terms)
ORDER BY rank DESC, landings.id;

:terms appears twice but is one parameter: the method takes terms once, and the generated SQL uses $1 in both places.

Gear as rows: jsonb_each

jsonb_each turns each boat's gear object into one row per key. Used in FROM, a function like this behaves as a table, and Seagull knows its columns: key is a string, and value stays JSON, as the package's JSON type.

db/queries/harbour.sql

-- sgl.query(BoatGear, many)
SELECT boats.name, gear.key, gear.value
FROM boats
CROSS JOIN LATERAL jsonb_each(boats.gear) AS gear
ORDER BY boats.name, gear.key;

Archiving in one statement: a writing CTE

ArchiveLandings moves old landings to the archive in a single statement. The DELETE inside the WITH hands the rows it removed, through RETURNING, to the INSERT, and the INSERT's own RETURNING comes back to Go. Both happen or neither does, with no transaction of your own.

db/queries/harbour.sql

-- sgl.query(ArchiveLandings, many)
WITH archived AS (
    DELETE FROM landings
    WHERE landed_on < :before
    RETURNING id, boat_id, species, kilos, landed_on
)
INSERT INTO landing_archive (id, boat_id, species, kilos, landed_on)
SELECT id, boat_id, species, kilos, landed_on FROM archived
RETURNING id, species, landed_on;

A WITH that deletes makes the whole statement a write, so the method uses queries.writer.

internal/store

type ArchiveLandingsRow struct {
	ID       int64     `json:"id"`
	Species  string    `json:"species"`
	LandedOn time.Time `json:"landed_on"`
}

func (queries *Queries) ArchiveLandings(ctx context.Context, before time.Time) ([]ArchiveLandingsRow, error) {
	rows, err := queries.writer.QueryContext(ctx, archiveLandingsSQL, before)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ArchiveLandingsRow
	if rows.Next() {
		var row ArchiveLandingsRow
		scanArgs := []any{&row.ID, &row.Species, &row.LandedOn}
		for more := true; more; more = rows.Next() {
			row = ArchiveLandingsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Using it

example.go

package main

import (
	"cmp"
	"context"
	"database/sql"
	"fmt"
	"io"
	"slices"
	"time"

	"seagull.sh/seagull/examples/postgres/031_postgres_power_queries/internal/store"
)

// run records a week of landings at a small harbour and reads them back with five queries
// that lean on PostgreSQL's own SQL. Seagull types each of them:
//
//   - LatestLandings uses DISTINCT ON to keep the first row of each boat in ORDER BY
//     order, which is its latest landing;
//   - BiggestLandings runs a LIMIT 2 subquery once per boat with LEFT JOIN LATERAL, so
//     the subquery can see boats.id; a boat with no landings still appears, which makes
//     the subquery's columns pointers;
//   - SearchNotes matches a generated tsvector column against websearch_to_tsquery, which
//     takes the syntax people type into search boxes, and ranks with ts_rank;
//   - BoatGear expands each boat's JSONB gear into key and value rows with jsonb_each, a
//     function used as a table; value stays store.JSON;
//   - ArchiveLandings moves old landings in one statement: the DELETE inside the CTE
//     hands its RETURNING rows to the INSERT, and the INSERT's RETURNING comes back.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each query's result.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	if err := recordWeek(ctx, queries); err != nil {
		return err
	}

	latest, err := queries.LatestLandings(ctx)
	if err != nil {
		return fmt.Errorf("finding the latest landings: %w", err)
	}
	fmt.Fprintln(output, "latest landing per boat (DISTINCT ON):")
	for _, landing := range latest {
		fmt.Fprintf(output, "  %-9s %-8s %s\n", landing.Name, landing.Species, landing.LandedOn.Format(time.DateOnly))
	}

	biggest, err := queries.BiggestLandings(ctx)
	if err != nil {
		return fmt.Errorf("finding the biggest landings: %w", err)
	}
	fmt.Fprintln(output, "two biggest landings per boat (LEFT JOIN LATERAL):")
	for _, landing := range biggest {
		if landing.Species == nil || landing.Kilos == nil {
			fmt.Fprintf(output, "  %-9s nothing landed\n", landing.Name)
			continue
		}
		fmt.Fprintf(output, "  %-9s %-8s %3dkg\n", landing.Name, *landing.Species, *landing.Kilos)
	}

	for _, terms := range []string{"mackerel", "crab or pollock"} {
		found, err := queries.SearchNotes(ctx, terms)
		if err != nil {
			return fmt.Errorf("searching for %q: %w", terms, err)
		}
		fmt.Fprintf(output, "search %q (full-text):\n", terms)
		for _, match := range found {
			fmt.Fprintf(output, "  #%d rank %s  %s\n", match.ID, formatRank(match.Rank), match.Notes)
		}
	}

	gear, err := queries.BoatGear(ctx)
	if err != nil {
		return fmt.Errorf("listing gear: %w", err)
	}
	fmt.Fprintln(output, "gear (jsonb_each):")
	for _, item := range gear {
		fmt.Fprintf(output, "  %-9s %-5s %s\n", item.Name, item.Key, item.Value)
	}

	cutoff := july(3)
	archived, err := queries.ArchiveLandings(ctx, cutoff)
	if err != nil {
		return fmt.Errorf("archiving landings: %w", err)
	}
	// RETURNING rows come back in no promised order, so sort them.
	slices.SortFunc(archived, func(a, b store.ArchiveLandingsRow) int { return cmp.Compare(a.ID, b.ID) })
	fmt.Fprintf(output, "archived before %s (DELETE and INSERT in one statement):\n", cutoff.Format(time.DateOnly))
	for _, landing := range archived {
		fmt.Fprintf(output, "  #%d %-8s %s\n", landing.ID, landing.Species, landing.LandedOn.Format(time.DateOnly))
	}
	return nil
}

// recordWeek adds three boats and five landings.
//
// Takes queries (*store.Queries) which add the rows.
//
// Returns error when a row cannot be added.
func recordWeek(ctx context.Context, queries *store.Queries) error {
	boats := []store.AddBoatParams{
		{Name: "Kittiwake", Gear: store.JSON(`{"nets": 2, "pots": 40}`)},
		{Name: "Puffin", Gear: store.JSON(`{"lines": 12}`)},
		{Name: "Gannet", Gear: store.JSON(`{}`)},
	}
	ids := make(map[string]int64, len(boats))
	for _, boat := range boats {
		added, err := queries.AddBoat(ctx, boat)
		if err != nil {
			return fmt.Errorf("adding %s: %w", boat.Name, err)
		}
		ids[boat.Name] = added.ID
	}
	landings := []store.RecordLandingParams{
		{BoatID: ids["Kittiwake"], Species: "mackerel", Kilos: 120, LandedOn: july(1), Notes: "a big shoal of mackerel off the point"},
		{BoatID: ids["Kittiwake"], Species: "crab", Kilos: 35, LandedOn: july(2), Notes: "pots hauled in a heavy swell"},
		{BoatID: ids["Puffin"], Species: "pollock", Kilos: 60, LandedOn: july(1), Notes: "lines over the wrecks"},
		{BoatID: ids["Kittiwake"], Species: "mackerel", Kilos: 80, LandedOn: july(4), Notes: "feathers out, gulls everywhere"},
		{BoatID: ids["Puffin"], Species: "mackerel", Kilos: 25, LandedOn: july(3), Notes: "a few mackerel by the harbour wall"},
	}
	for _, landing := range landings {
		if err := queries.RecordLanding(ctx, landing); err != nil {
			return fmt.Errorf("recording %s: %w", landing.Species, err)
		}
	}
	return nil
}

// july returns a day in July 2026, the week the landings happen in.
//
// Takes day (int) which is the day of the month.
//
// Returns time.Time which is midnight UTC on that day, as a DATE column holds it.
func july(day int) time.Time {
	return time.Date(2026, time.July, day, 0, 0, 0, 0, time.UTC)
}

// formatRank formats a ts_rank score to four places, or returns NULL when it is nil.
//
// Takes rank (*float32) which is the score.
//
// Returns string which is the text to print.
func formatRank(rank *float32) string {
	if rank == nil {
		return "NULL"
	}
	return fmt.Sprintf("%.4f", *rank)
}

What it prints

The search for mackerel also finds landing 4, whose notes never mention mackerel: the generated column indexes the species as well as the notes. The archived landings come back in no promised order, so the program sorts them.

latest landing per boat (DISTINCT ON):
  Kittiwake mackerel 2026-07-04
  Puffin    mackerel 2026-07-03
two biggest landings per boat (LEFT JOIN LATERAL):
  Gannet    nothing landed
  Kittiwake mackerel 120kg
  Kittiwake mackerel  80kg
  Puffin    pollock   60kg
  Puffin    mackerel  25kg
search "mackerel" (full-text):
  #1 rank 0.0760  a big shoal of mackerel off the point
  #5 rank 0.0760  a few mackerel by the harbour wall
  #4 rank 0.0608  feathers out, gulls everywhere
search "crab or pollock" (full-text):
  #2 rank 0.0304  pots hauled in a heavy swell
  #3 rank 0.0304  lines over the wrecks
gear (jsonb_each):
  Kittiwake nets  2
  Kittiwake pots  40
  Puffin    lines 12
archived before 2026-07-03 (DELETE and INSERT in one statement):
  #1 mackerel 2026-07-01
  #2 crab     2026-07-02
  #3 pollock  2026-07-01

Try it

Change LEFT JOIN LATERAL in BiggestLandings to JOIN LATERAL and run sgl generate. Species and Kilos stop being pointers, because a boat with no landings no longer appears, and Gannet drops out of the output.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./031_postgres_power_queries

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/postgres && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./031_postgres_power_queries

Needs a running PostgreSQL server: set DATABASE_URL, or pass -dsn, to the address of an empty database.

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: harbour
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql
    migrate:
      dsn: ${env:DATABASE_URL}

db/migrations/0001_harbour.up.sql

CREATE TABLE boats (
    id   BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT   NOT NULL,
    gear JSONB  NOT NULL DEFAULT '{}'
);

CREATE TABLE landings (
    id        BIGINT   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    boat_id   BIGINT   NOT NULL REFERENCES boats (id),
    species   TEXT     NOT NULL,
    kilos     INTEGER  NOT NULL,
    landed_on DATE     NOT NULL,
    notes     TEXT     NOT NULL,
    search    TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', species || ' ' || notes)) STORED
);

CREATE INDEX landings_search_idx ON landings USING GIN (search);

CREATE TABLE landing_archive (
    id        BIGINT  PRIMARY KEY,
    boat_id   BIGINT  NOT NULL REFERENCES boats (id),
    species   TEXT    NOT NULL,
    kilos     INTEGER NOT NULL,
    landed_on DATE    NOT NULL
);

db/migrations/0001_harbour.down.sql

DROP TABLE landing_archive;
DROP TABLE landings;
DROP TABLE boats;

db/queries/harbour.sql

-- sgl.query(AddBoat, one)
INSERT INTO boats (name, gear) VALUES ($1, $2) RETURNING id;

-- sgl.query(RecordLanding, exec)
INSERT INTO landings (boat_id, species, kilos, landed_on, notes) VALUES ($1, $2, $3, $4, $5);

-- sgl.query(LatestLandings, many)
SELECT DISTINCT ON (landings.boat_id) boats.name, landings.species, landings.landed_on
FROM landings
JOIN boats ON boats.id = landings.boat_id
ORDER BY landings.boat_id, landings.landed_on DESC, landings.id DESC;

-- sgl.query(BiggestLandings, many)
SELECT boats.name, biggest.species, biggest.kilos
FROM boats
LEFT JOIN LATERAL (
    SELECT species, kilos
    FROM landings
    WHERE landings.boat_id = boats.id
    ORDER BY kilos DESC
    LIMIT 2
) AS biggest ON true
ORDER BY boats.name, biggest.kilos DESC;

-- sgl.query(SearchNotes, many)
SELECT landings.id, landings.notes, ts_rank(landings.search, websearch_to_tsquery('english', :terms)) AS rank
FROM landings
WHERE landings.search @@ websearch_to_tsquery('english', :terms)
ORDER BY rank DESC, landings.id;

-- sgl.query(BoatGear, many)
SELECT boats.name, gear.key, gear.value
FROM boats
CROSS JOIN LATERAL jsonb_each(boats.gear) AS gear
ORDER BY boats.name, gear.key;

-- sgl.query(ArchiveLandings, many)
WITH archived AS (
    DELETE FROM landings
    WHERE landed_on < :before
    RETURNING id, boat_id, species, kilos, landed_on
)
INSERT INTO landing_archive (id, boat_id, species, kilos, landed_on)
SELECT id, boat_id, species, kilos, landed_on FROM archived
RETURNING id, species, landed_on;

example.go

package main

import (
	"cmp"
	"context"
	"database/sql"
	"fmt"
	"io"
	"slices"
	"time"

	"seagull.sh/seagull/examples/postgres/031_postgres_power_queries/internal/store"
)

// run records a week of landings at a small harbour and reads them back with five queries
// that lean on PostgreSQL's own SQL. Seagull types each of them:
//
//   - LatestLandings uses DISTINCT ON to keep the first row of each boat in ORDER BY
//     order, which is its latest landing;
//   - BiggestLandings runs a LIMIT 2 subquery once per boat with LEFT JOIN LATERAL, so
//     the subquery can see boats.id; a boat with no landings still appears, which makes
//     the subquery's columns pointers;
//   - SearchNotes matches a generated tsvector column against websearch_to_tsquery, which
//     takes the syntax people type into search boxes, and ranks with ts_rank;
//   - BoatGear expands each boat's JSONB gear into key and value rows with jsonb_each, a
//     function used as a table; value stays store.JSON;
//   - ArchiveLandings moves old landings in one statement: the DELETE inside the CTE
//     hands its RETURNING rows to the INSERT, and the INSERT's RETURNING comes back.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each query's result.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	if err := recordWeek(ctx, queries); err != nil {
		return err
	}

	latest, err := queries.LatestLandings(ctx)
	if err != nil {
		return fmt.Errorf("finding the latest landings: %w", err)
	}
	fmt.Fprintln(output, "latest landing per boat (DISTINCT ON):")
	for _, landing := range latest {
		fmt.Fprintf(output, "  %-9s %-8s %s\n", landing.Name, landing.Species, landing.LandedOn.Format(time.DateOnly))
	}

	biggest, err := queries.BiggestLandings(ctx)
	if err != nil {
		return fmt.Errorf("finding the biggest landings: %w", err)
	}
	fmt.Fprintln(output, "two biggest landings per boat (LEFT JOIN LATERAL):")
	for _, landing := range biggest {
		if landing.Species == nil || landing.Kilos == nil {
			fmt.Fprintf(output, "  %-9s nothing landed\n", landing.Name)
			continue
		}
		fmt.Fprintf(output, "  %-9s %-8s %3dkg\n", landing.Name, *landing.Species, *landing.Kilos)
	}

	for _, terms := range []string{"mackerel", "crab or pollock"} {
		found, err := queries.SearchNotes(ctx, terms)
		if err != nil {
			return fmt.Errorf("searching for %q: %w", terms, err)
		}
		fmt.Fprintf(output, "search %q (full-text):\n", terms)
		for _, match := range found {
			fmt.Fprintf(output, "  #%d rank %s  %s\n", match.ID, formatRank(match.Rank), match.Notes)
		}
	}

	gear, err := queries.BoatGear(ctx)
	if err != nil {
		return fmt.Errorf("listing gear: %w", err)
	}
	fmt.Fprintln(output, "gear (jsonb_each):")
	for _, item := range gear {
		fmt.Fprintf(output, "  %-9s %-5s %s\n", item.Name, item.Key, item.Value)
	}

	cutoff := july(3)
	archived, err := queries.ArchiveLandings(ctx, cutoff)
	if err != nil {
		return fmt.Errorf("archiving landings: %w", err)
	}
	// RETURNING rows come back in no promised order, so sort them.
	slices.SortFunc(archived, func(a, b store.ArchiveLandingsRow) int { return cmp.Compare(a.ID, b.ID) })
	fmt.Fprintf(output, "archived before %s (DELETE and INSERT in one statement):\n", cutoff.Format(time.DateOnly))
	for _, landing := range archived {
		fmt.Fprintf(output, "  #%d %-8s %s\n", landing.ID, landing.Species, landing.LandedOn.Format(time.DateOnly))
	}
	return nil
}

// recordWeek adds three boats and five landings.
//
// Takes queries (*store.Queries) which add the rows.
//
// Returns error when a row cannot be added.
func recordWeek(ctx context.Context, queries *store.Queries) error {
	boats := []store.AddBoatParams{
		{Name: "Kittiwake", Gear: store.JSON(`{"nets": 2, "pots": 40}`)},
		{Name: "Puffin", Gear: store.JSON(`{"lines": 12}`)},
		{Name: "Gannet", Gear: store.JSON(`{}`)},
	}
	ids := make(map[string]int64, len(boats))
	for _, boat := range boats {
		added, err := queries.AddBoat(ctx, boat)
		if err != nil {
			return fmt.Errorf("adding %s: %w", boat.Name, err)
		}
		ids[boat.Name] = added.ID
	}
	landings := []store.RecordLandingParams{
		{BoatID: ids["Kittiwake"], Species: "mackerel", Kilos: 120, LandedOn: july(1), Notes: "a big shoal of mackerel off the point"},
		{BoatID: ids["Kittiwake"], Species: "crab", Kilos: 35, LandedOn: july(2), Notes: "pots hauled in a heavy swell"},
		{BoatID: ids["Puffin"], Species: "pollock", Kilos: 60, LandedOn: july(1), Notes: "lines over the wrecks"},
		{BoatID: ids["Kittiwake"], Species: "mackerel", Kilos: 80, LandedOn: july(4), Notes: "feathers out, gulls everywhere"},
		{BoatID: ids["Puffin"], Species: "mackerel", Kilos: 25, LandedOn: july(3), Notes: "a few mackerel by the harbour wall"},
	}
	for _, landing := range landings {
		if err := queries.RecordLanding(ctx, landing); err != nil {
			return fmt.Errorf("recording %s: %w", landing.Species, err)
		}
	}
	return nil
}

// july returns a day in July 2026, the week the landings happen in.
//
// Takes day (int) which is the day of the month.
//
// Returns time.Time which is midnight UTC on that day, as a DATE column holds it.
func july(day int) time.Time {
	return time.Date(2026, time.July, day, 0, 0, 0, 0, time.UTC)
}

// formatRank formats a ts_rank score to four places, or returns NULL when it is nil.
//
// Takes rank (*float32) which is the score.
//
// Returns string which is the text to print.
func formatRank(rank *float32) string {
	if rank == nil {
		return "NULL"
	}
	return fmt.Sprintf("%.4f", *rank)
}

main.go

// Command 031_postgres_power_queries applies its migrations to a PostgreSQL database,
// then runs the example in example.go: a harbour's landings are read with DISTINCT ON, a
// LATERAL join, full-text search and jsonb_each, then archived with a data-modifying CTE.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, or set
// DATABASE_URL instead of passing -dsn. Give it an empty database of its own, as the
// migrations create its tables and the example adds rows each time it runs.
package main

import (
	"context"
	"database/sql"
	"embed"
	"errors"
	"flag"
	"fmt"
	"log"
	"os"

	_ "github.com/jackc/pgx/v5/stdlib"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	dsn := flag.String("dsn", os.Getenv("DATABASE_URL"), "PostgreSQL connection string; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	if dsn == "" {
		return errors.New("set -dsn or DATABASE_URL to a PostgreSQL connection string")
	}
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/harbour.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"
)

const addBoatSQL = `INSERT INTO boats (name, gear) VALUES ($1, $2) RETURNING id;`

type AddBoatParams struct {
	Name string
	Gear JSON
}

type AddBoatRow struct {
	ID int64 `json:"id"`
}

func (queries *Queries) AddBoat(ctx context.Context, params AddBoatParams) (AddBoatRow, error) {
	var row AddBoatRow
	err := queries.writer.QueryRowContext(ctx, addBoatSQL, params.Name, params.Gear).Scan(&row.ID)
	if err != nil {
		return AddBoatRow{}, err
	}
	return row, nil
}

const archiveLandingsSQL = `WITH archived AS (
    DELETE FROM landings
    WHERE landed_on < $1
    RETURNING id, boat_id, species, kilos, landed_on
)
INSERT INTO landing_archive (id, boat_id, species, kilos, landed_on)
SELECT id, boat_id, species, kilos, landed_on FROM archived
RETURNING id, species, landed_on;`

type ArchiveLandingsRow struct {
	ID       int64     `json:"id"`
	Species  string    `json:"species"`
	LandedOn time.Time `json:"landed_on"`
}

func (queries *Queries) ArchiveLandings(ctx context.Context, before time.Time) ([]ArchiveLandingsRow, error) {
	rows, err := queries.writer.QueryContext(ctx, archiveLandingsSQL, before)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ArchiveLandingsRow
	if rows.Next() {
		var row ArchiveLandingsRow
		scanArgs := []any{&row.ID, &row.Species, &row.LandedOn}
		for more := true; more; more = rows.Next() {
			row = ArchiveLandingsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const biggestLandingsSQL = `SELECT boats.name, biggest.species, biggest.kilos
FROM boats
LEFT JOIN LATERAL (
    SELECT species, kilos
    FROM landings
    WHERE landings.boat_id = boats.id
    ORDER BY kilos DESC
    LIMIT 2
) AS biggest ON true
ORDER BY boats.name, biggest.kilos DESC;`

type BiggestLandingsRow struct {
	Name    string  `json:"name"`
	Species *string `json:"species"`
	Kilos   *int32  `json:"kilos"`
}

func (queries *Queries) BiggestLandings(ctx context.Context) ([]BiggestLandingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, biggestLandingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BiggestLandingsRow
	for rows.Next() {
		var row BiggestLandingsRow
		if err := rows.Scan(&row.Name, &row.Species, &row.Kilos); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const boatGearSQL = `SELECT boats.name, gear.key, gear.value
FROM boats
CROSS JOIN LATERAL jsonb_each(boats.gear) AS gear
ORDER BY boats.name, gear.key;`

type BoatGearRow struct {
	Name  string `json:"name"`
	Key   string `json:"key"`
	Value JSON   `json:"value"`
}

func (queries *Queries) BoatGear(ctx context.Context) ([]BoatGearRow, error) {
	rows, err := queries.reader.QueryContext(ctx, boatGearSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []BoatGearRow
	for rows.Next() {
		var row BoatGearRow
		if err := rows.Scan(&row.Name, &row.Key, &row.Value); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const latestLandingsSQL = `SELECT DISTINCT ON (landings.boat_id) boats.name, landings.species, landings.landed_on
FROM landings
JOIN boats ON boats.id = landings.boat_id
ORDER BY landings.boat_id, landings.landed_on DESC, landings.id DESC;`

type LatestLandingsRow struct {
	Name     string    `json:"name"`
	Species  string    `json:"species"`
	LandedOn time.Time `json:"landed_on"`
}

func (queries *Queries) LatestLandings(ctx context.Context) ([]LatestLandingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, latestLandingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []LatestLandingsRow
	if rows.Next() {
		var row LatestLandingsRow
		scanArgs := []any{&row.Name, &row.Species, &row.LandedOn}
		for more := true; more; more = rows.Next() {
			row = LatestLandingsRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const recordLandingSQL = `INSERT INTO landings (boat_id, species, kilos, landed_on, notes) VALUES ($1, $2, $3, $4, $5);`

type RecordLandingParams struct {
	BoatID   int64
	Species  string
	Kilos    int32
	LandedOn time.Time
	Notes    string
}

func (queries *Queries) RecordLanding(ctx context.Context, params RecordLandingParams) error {
	_, err := queries.writer.ExecContext(ctx, recordLandingSQL, params.BoatID, params.Species, params.Kilos, params.LandedOn, params.Notes)
	return err
}

const searchNotesSQL = `SELECT landings.id, landings.notes, ts_rank(landings.search, websearch_to_tsquery('english', $1)) AS rank
FROM landings
WHERE landings.search @@ websearch_to_tsquery('english', $1)
ORDER BY rank DESC, landings.id;`

type SearchNotesRow struct {
	ID    int64    `json:"id"`
	Notes string   `json:"notes"`
	Rank  *float32 `json:"rank"`
}

func (queries *Queries) SearchNotes(ctx context.Context, terms string) ([]SearchNotesRow, error) {
	rows, err := queries.reader.QueryContext(ctx, searchNotesSQL, terms)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SearchNotesRow
	for rows.Next() {
		var row SearchNotesRow
		if err := rows.Scan(&row.ID, &row.Notes, &row.Rank); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

import "time"

type Boats struct {
	ID   int64  `json:"id"`
	Name string `json:"name"`
	Gear JSON   `json:"gear"`
}

type LandingArchive struct {
	ID       int64     `json:"id"`
	BoatID   int64     `json:"boat_id"`
	Species  string    `json:"species"`
	Kilos    int32     `json:"kilos"`
	LandedOn time.Time `json:"landed_on"`
}

type Landings struct {
	ID       int64     `json:"id"`
	BoatID   int64     `json:"boat_id"`
	Species  string    `json:"species"`
	Kilos    int32     `json:"kilos"`
	LandedOn time.Time `json:"landed_on"`
	Notes    string    `json:"notes"`
	Search   *string   `json:"search"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

internal/store/sgl_json.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"
)

type JSON []byte

func (j *JSON) Scan(source any) error {
	switch value := source.(type) {
	case nil:
		*j = nil
		return nil
	case []byte:
		clone := make(JSON, len(value))
		copy(clone, value)
		*j = clone
		return nil
	case string:
		*j = JSON(value)
		return nil
	default:
		encoded, err := json.Marshal(value)
		if err != nil {
			return fmt.Errorf("sgl: encode %T to JSON: %w", source, err)
		}
		*j = encoded
		return nil
	}
}

func (j JSON) Value() (driver.Value, error) {
	if j == nil {
		return nil, nil
	}
	return string(j), nil
}

func (j JSON) MarshalJSON() ([]byte, error) {
	if j == nil {
		return []byte("null"), nil
	}
	return j, nil
}

func (j *JSON) UnmarshalJSON(data []byte) error {
	clone := make(JSON, len(data))
	copy(clone, data)
	*j = clone
	return nil
}

output.txt

latest landing per boat (DISTINCT ON):
  Kittiwake mackerel 2026-07-04
  Puffin    mackerel 2026-07-03
two biggest landings per boat (LEFT JOIN LATERAL):
  Gannet    nothing landed
  Kittiwake mackerel 120kg
  Kittiwake mackerel  80kg
  Puffin    pollock   60kg
  Puffin    mackerel  25kg
search "mackerel" (full-text):
  #1 rank 0.0760  a big shoal of mackerel off the point
  #5 rank 0.0760  a few mackerel by the harbour wall
  #4 rank 0.0608  feathers out, gulls everywhere
search "crab or pollock" (full-text):
  #2 rank 0.0304  pots hauled in a heavy swell
  #3 rank 0.0304  lines over the wrecks
gear (jsonb_each):
  Kittiwake nets  2
  Kittiwake pots  40
  Puffin    lines 12
archived before 2026-07-03 (DELETE and INSERT in one statement):
  #1 mackerel 2026-07-01
  #2 crab     2026-07-02
  #3 pollock  2026-07-01