pgdesign v0.26.0 /internal/sql
Updated
On this page

Package sql provides shared SQL builder functions for PostgreSQL DDL generation including partman setup, the central SQL construction point.

#internal/sql

#internal/sql

Package sql provides shared SQL builder functions for PostgreSQL DDL generation, the single place where SQL text is constructed across all packages.

#QuoteIdent

Go go
func QuoteIdent(name string) string

QuoteIdent quotes a PostgreSQL identifier with double-quotes if needed. Quoting is applied when the name is a reserved word, contains special characters, has uppercase letters, or starts with a digit.

#QualifiedName

Go go
func QualifiedName(schema, name string) string

QualifiedName returns a schema-qualified name with proper quoting. An empty schema yields the UNQUALIFIED name: quoting an empty string produces "", which would render the invalid "".table — so the guard lives here, centrally, killing that class for every caller.

#LiteralValue

Go go
func LiteralValue(value string, pgType string) string

LiteralValue formats a value as a SQL literal based on its PG type. Strings get single quotes (with escaping), numbers are bare, booleans are bare, and empty values return "NULL".

#ExprValue

Go go
func ExprValue(expr string) string

ExprValue returns an expression verbatim (for DEFAULT expressions like now()).

#ConstraintName

Go go
func ConstraintName(table, kind string, refs ...string) string

ConstraintName generates a constraint name following the convention: pk_

, fk_
_, idx_
_, uq_
_, ck_
_. Kind must be one of: "pk", "fk", "idx", "uq", "ck", "excl".

#CreateSchema

Go go
func CreateSchema(name string, idempotent bool) string

CreateSchema generates a CREATE SCHEMA statement.

#CreateExtension

Go go
func CreateExtension(name string, idempotent bool) string

CreateExtension generates a CREATE EXTENSION statement.

#CreateExtensionInSchema

Go go
func CreateExtensionInSchema(name, schema string, idempotent bool) string

CreateExtensionInSchema generates a CREATE EXTENSION ... SCHEMA statement that installs the extension into a specific schema.

#CreateEnum

Go go
func CreateEnum(schema, name string, values []string, idempotent bool) string

CreateEnum generates a CREATE TYPE ... AS ENUM statement. PostgreSQL does not support CREATE TYPE IF NOT EXISTS, so when idempotent is true, the statement is wrapped in a DO $$ block that checks pg_type before creating (same approach as CreateDomain and CreateCompositeType).

#CreateDomain

Go go
func CreateDomain(schemaName string, d model.Domain, idempotent bool) string

CreateDomain generates a CREATE DOMAIN statement. Emits: CREATE DOMAIN [schema.]name AS basetype [NOT NULL] [DEFAULT ...] [CHECK (...)]. When idempotent is true, wraps in a DO $$ block that checks pg_type before creating.

#CreateCompositeType

Go go
func CreateCompositeType(schemaName string, ct model.CompositeType, idempotent bool) string

CreateCompositeType generates a CREATE TYPE ... AS statement for a composite type. Emits: CREATE TYPE [schema.]name AS (field1 type1, field2 type2, ...) When idempotent is true, wraps in a DO $$ block that checks pg_type before creating.

#DropCompositeType

Go go
func DropCompositeType(schemaName, name string, cascade bool) string

DropCompositeType generates a DROP TYPE statement for a composite type.

#DropDomain

Go go
func DropDomain(schemaName, name string, cascade bool) string

DropDomain generates a DROP DOMAIN statement.

#CreateTable

Go go
func CreateTable(table *model.Table, schemaName string, idempotent bool, pgVersion int, enums []model.Enum, domains []model.Domain) string

CreateTable generates a CREATE TABLE statement with columns, inline PK, and PARTITION BY. Foreign keys are NOT included (they use ALTER TABLE for cycle safety). pgVersion controls version-specific DDL: when the target version lacks identity column support (pre-PG10), identity columns fall back to bigserial. enums and domains are the enum and domain types defined in the schema; when a column's PG type matches an enum or domain name, the type is emitted with its schema prefix so the DDL works without relying on search_path.

#GeneratedStorageKeyword

Go go
func GeneratedStorageKeyword(stored bool, pgVersion int) string

GeneratedStorageKeyword returns "STORED" or "VIRTUAL" for a generated column based on the stored flag and target PostgreSQL version.

#AlterTableAddColumnIfNotExists

Go go
func AlterTableAddColumnIfNotExists(tableName, schemaName string, col model.Column, pgVersion int, enums []model.Enum, domains []model.Domain) string

AlterTableAddColumnIfNotExists generates an ALTER TABLE ... ADD COLUMN IF NOT EXISTS statement. This is used in idempotent mode to ensure columns exist without erroring if they already do. The column definition reuses the same columnDef logic as CreateTable.

#AlterTableAddFK

Go go
func AlterTableAddFK(schemaName string, table *model.Table, fk *model.FK, idempotent bool) string

AlterTableAddFK generates an ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY statement. When idempotent is true, wraps the statement in a DO $$ block that checks pg_constraint before adding.

#AlterTableAddUnique

Go go
func AlterTableAddUnique(schemaName, tableName string, uq *model.UniqueConstraint, idempotent bool) string

AlterTableAddUnique generates an ALTER TABLE ... ADD CONSTRAINT ... UNIQUE statement. When idempotent is true, wraps the statement in a DO $$ block that checks pg_constraint before adding.

#AlterTableAddCheck

Go go
func AlterTableAddCheck(schemaName, tableName string, ck *model.CheckConstraint, idempotent bool) string

AlterTableAddCheck generates an ALTER TABLE ... ADD CONSTRAINT ... CHECK statement. When idempotent is true, wraps the statement in a DO $$ block that checks pg_constraint before adding.

#AlterTableAddExclusion

Go go
func AlterTableAddExclusion(schemaName, tableName string, exc *model.ExclusionConstraint, idempotent bool) string

AlterTableAddExclusion generates an ALTER TABLE ... ADD CONSTRAINT ... EXCLUDE statement.

#CreateIndex

Go go
func CreateIndex(schemaName string, index *model.Index, tableName string, idempotent bool, concurrently bool) string

CreateIndex generates a CREATE INDEX statement. Handles Method (default btree), per-column Opclasses, WHERE, INCLUDE, and CONCURRENTLY. When concurrently is true, IF NOT EXISTS is DELIBERATELY omitted (not for version reasons — CREATE INDEX CONCURRENTLY IF NOT EXISTS is valid since PG 9.5): an interrupted CREATE INDEX CONCURRENTLY leaves an INVALID index of the target name, and IF NOT EXISTS would skip it forever. The resume protocol instead checks pg_index.indisvalid and DROP-rebuilds (roadmap L8), so the bare form is correct here.

#CommentOn

Go go
func CommentOn(objectType, qualifiedName, comment string) string

CommentOn generates a COMMENT ON statement.

#CommentsOnTable

Go go
func CommentsOnTable(schemaName string, t *model.Table) []string

CommentsOnTable returns the COMMENT ON statements for a table and its columns (table comment first, then per-column comments in column order). It is empty when the table and all its columns are commentless. Callers that render a table's DDL standalone — the migrate chain create_table render path — append these so a chain-created table carries its mandatory comment (and any column comments) live, instead of relying on the full-DDL generate path that emits comments in a separate section.

#CreatePartitionOf

Go go
func CreatePartitionOf(schemaName string, childSpec *model.PartitionSpec, parentTable string, idempotent bool) string

CreatePartitionOf generates a CREATE TABLE ... PARTITION OF statement for a child partition. The bound expression is emitted verbatim (e.g. "FROM ('2024-01-01') TO ('2024-02-01')").

#CreatePartmanParent

Go go
func CreatePartmanParent(schemaName, tableName, column, interval string, premake int) string

CreatePartmanParent generates a SELECT partman.create_parent() call to register a table with pg_partman for automatic partition management.

#UpdatePartmanConfig

Go go
func UpdatePartmanConfig(schemaName, tableName, retention string, keepTable bool) string

UpdatePartmanConfig generates an UPDATE partman.part_config statement to configure retention settings for a pg_partman-managed table.

#PartmanRunMaintenanceCron

Go go
func PartmanRunMaintenanceCron(schedule string) string

PartmanRunMaintenanceCron generates a SELECT cron.schedule() call to create a pg_cron job that runs partman.run_maintenance_proc() on the given cron schedule (e.g. "/30 *"). Requires the pg_cron extension.

#AlterTableOwner

Go go
func AlterTableOwner(schemaName, tableName, owner string) string

AlterTableOwner generates an ALTER TABLE ... OWNER TO statement.

#AlterTableEnableRLS

Go go
func AlterTableEnableRLS(schemaName, tableName string) string

AlterTableEnableRLS generates an ALTER TABLE ... ENABLE ROW LEVEL SECURITY statement.

#AlterTableForceRLS

Go go
func AlterTableForceRLS(schemaName, tableName string) string

AlterTableForceRLS generates an ALTER TABLE ... FORCE ROW LEVEL SECURITY statement. This causes RLS policies to apply even to table owners.

#CreatePolicy

Go go
func CreatePolicy(schemaName, tableName string, p model.Policy, idempotent bool, pgVersion int) string

CreatePolicy generates a CREATE POLICY statement for row-level security. The FOR clause is omitted when operation is "ALL" (the PostgreSQL default). The TO clause is omitted when role is empty (defaults to PUBLIC). USING and WITH CHECK are wrapped in parentheses when present.

When idempotent is true and pgVersion >= 15, uses CREATE OR REPLACE POLICY. When idempotent is true and pgVersion < 15, wraps in a DO $$ block that checks pg_policy before executing.

#DropPolicy

Go go
func DropPolicy(schemaName, tableName, policyName string) string

DropPolicy generates a DROP POLICY statement.

#AlterTableDisableRLS

Go go
func AlterTableDisableRLS(schemaName, tableName string) string

AlterTableDisableRLS generates an ALTER TABLE ... DISABLE ROW LEVEL SECURITY statement.

#AlterTableNoForceRLS

Go go
func AlterTableNoForceRLS(schemaName, tableName string) string

AlterTableNoForceRLS generates an ALTER TABLE ... NO FORCE ROW LEVEL SECURITY statement.

#CreateView

Go go
func CreateView(schemaName string, view *model.View, idempotent bool) string

CreateView generates a CREATE VIEW statement. When idempotent is true, uses CREATE OR REPLACE VIEW instead of CREATE VIEW.

#DropView

Go go
func DropView(schemaName, viewName string, idempotent bool) string

DropView generates a DROP VIEW statement. When idempotent is true, includes IF EXISTS.

#CreateMaterializedView

Go go
func CreateMaterializedView(schemaName string, mv *model.MaterializedView, idempotent bool) string

CreateMaterializedView generates a CREATE MATERIALIZED VIEW statement. PostgreSQL does not support CREATE OR REPLACE or IF NOT EXISTS for materialized views. When idempotent is true, wraps in a DO $$ block that checks pg_matviews before creating.

#DropMaterializedView

Go go
func DropMaterializedView(schemaName, viewName string, idempotent bool) string

DropMaterializedView generates a DROP MATERIALIZED VIEW statement. When idempotent is true, includes IF EXISTS.

#RefreshMaterializedView

Go go
func RefreshMaterializedView(schemaName, name string, concurrently bool) string

RefreshMaterializedView generates a REFRESH MATERIALIZED VIEW statement. When concurrently is true, adds CONCURRENTLY (requires a unique index to exist).

#CreateSequence

Go go
func CreateSequence(schemaName string, seq *model.Sequence, idempotent bool) string

CreateSequence generates a CREATE SEQUENCE statement. When idempotent is true, IF NOT EXISTS is included.

#DropSequence

Go go
func DropSequence(schemaName, name string, cascade bool) string

DropSequence generates a DROP SEQUENCE statement.

#AlterSequence

Go go
func AlterSequence(schemaName string, seq *model.Sequence) string

AlterSequence generates an ALTER SEQUENCE statement for changing parameters.

#CreateDenyMutationFunction

Go go
func CreateDenyMutationFunction(schemaName string) string

CreateDenyMutationFunction generates a CREATE OR REPLACE FUNCTION statement for the shared pgdesign_deny_mutation trigger function. This function raises an exception when UPDATE or DELETE is attempted on an append-only table.

#CreateAppendOnlyTrigger

Go go
func CreateAppendOnlyTrigger(schemaName, tableName string, idempotent bool, pgVersion int) string

CreateAppendOnlyTrigger generates a CREATE TRIGGER statement that fires BEFORE UPDATE OR DELETE to enforce append-only behavior on a table. When idempotent is true and pgVersion supports CREATE OR REPLACE TRIGGER (PG 14+), it emits CREATE OR REPLACE TRIGGER; otherwise it emits DROP TRIGGER IF EXISTS followed by CREATE TRIGGER.

#StateMachineTriggerFuncName

Go go
func StateMachineTriggerFuncName(tableName, colName string) string

StateMachineTriggerFuncName returns the reserved function name for a state machine enforcement trigger: _pgdesign_sm_

_.

#CreateStateMachineTriggerFunction

Go go
func CreateStateMachineTriggerFunction(schemaName, tableName, colName string, transitions []semtype.SMTransitionDef) string

CreateStateMachineTriggerFunction generates a PL/pgSQL function that enforces state machine transitions on a column. It checks that each UPDATE of the column follows a valid transition and that any required columns are non-null.

#CreateStateMachineTrigger

Go go
func CreateStateMachineTrigger(schemaName, tableName, colName string, idempotent bool, pgVersion int) string

CreateStateMachineTrigger generates a CREATE TRIGGER statement that fires BEFORE UPDATE OF

to enforce state machine transitions. When idempotent is true and pgVersion supports CREATE OR REPLACE TRIGGER (PG 14+), it emits CREATE OR REPLACE TRIGGER; otherwise it emits DROP TRIGGER IF EXISTS followed by CREATE TRIGGER.

#CreateTrigger

Go go
func CreateTrigger(schemaName, tableName string, t model.Trigger, idempotent bool, pgVersion int) string

CreateTrigger generates a CREATE [CONSTRAINT] TRIGGER statement for a user-defined trigger. Emits: CREATE [OR REPLACE] [CONSTRAINT] TRIGGER name timing events ON [schema.]table

[REFERENCING OLD TABLE AS x NEW TABLE AS y] FOR EACH ROW|STATEMENT [WHEN (condition)] EXECUTE FUNCTION [schema.]func_name()

When idempotent is true and pgVersion supports CREATE OR REPLACE TRIGGER (PG 14+), it emits CREATE OR REPLACE [CONSTRAINT] TRIGGER; otherwise it emits DROP TRIGGER IF EXISTS followed by CREATE [CONSTRAINT] TRIGGER.

#DropTrigger

Go go
func DropTrigger(schemaName, tableName, triggerName string) string

DropTrigger generates a DROP TRIGGER statement.

#CreateFunction

Go go
func CreateFunction(schemaName string, f model.Function) string

CreateFunction generates a CREATE OR REPLACE FUNCTION/PROCEDURE statement. For procedures (f.IsProc), RETURNS and volatility/parallel/cost/rows are omitted.

#DropFunction

Go go
func DropFunction(schemaName string, f model.Function, cascade bool) string

DropFunction generates a DROP FUNCTION/PROCEDURE statement. Includes argument types for overload resolution.

Search