pgxspecial

README

pgxspecial

pgxspecial is a Go library that provides an API to execute PostgreSQL meta-commands (a.k.a. “special” or “backslash” commands), modeled on the behavior of tools like psql and inspired by the Python library pgspecial.

Features

• Execute psql-style backslash commands directly from Go code
• Get structured metadata about databases: tables, types, functions, schemas, roles — not just raw SQL results
• Works with pgx/v5 and pgxpool (or any adapter implementing the included DB interface)

Installation

go get github.com/balaji01-4d/pgxspecial

Basic Usage (Go API)

import (
    "context"
    "fmt"
    "log"

    "github.com/balaji01-4d/pgxspecial"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres://user:password@localhost:5432/database?sslmode=disable")
    if err != nil {
        log.Fatalf("Unable to connect: %v\n", err)
    }
    defer pool.Close()

    // Execute a special command
    // Returns a SpecialCommandResult interface
    res, isSpecial, err := pgxspecial.ExecuteSpecialCommand(ctx, pool, "\l")
    if err != nil {
        log.Fatalf("Special command error: %v\n", err)
    }

    if isSpecial {
        // Handle the result based on its type
        switch r := res.(type) {
        case pgxspecial.RowResult:
            // Standard result (like \l, \dt) - wraps pgx.Rows
            fmt.Println("Rows returned:")
            // Iterate r.Rows ...
            r.Rows.Close()

        case pgxspecial.DescribeTableListResult:
            // Complex result (like \d my_table)
            for _, table := range r.Results {
                fmt.Printf("Table: %s\n", table.TableMetaData.TypedTableOf) // Example
                // Access Columns, Data, and TableMetaData
            }
        }
    }
}

Supported Commands

Cmd	Syntax	Description
\l (\list)	\l[+] [pattern]	List databases
\d	\d[+] [pattern]	List or describe tables, views and sequences
DESCRIBE	DESCRIBE [pattern]	List or describe tables, views and sequences
\dT	\dT[+] [pattern]	List data types
\ddp	\ddp [pattern]	List default access privilege settings
\dD	\dD[+] [pattern]	List or describe domains
\dx	\dx[+] [pattern]	List extensions
\dE	\dE[+] [pattern]	List foreign tables
\df	\df[+] [pattern]	List functions
\dt	\dt[+] [pattern]	List tables
\dv	\dv[+] [pattern]	List views
\dm	\dm[+] [pattern]	List materialized views
\ds	\ds[+] [pattern]	List sequences
\di	\di[+] [pattern]	List indexes
\dp (\z)	\dp [pattern]	List privileges
\du	\du[+] [pattern]	List roles
\dn	\dn[+] [pattern]	List schemas
\db	\db[+] [pattern]	List tablespaces
\!	\! command	Execute a shell command
\sf	\sf[+] FUNCNAME	Show a function's definition

Result Types

The library now uses a polymorphic result type SpecialCommandResult to handle different kinds of output:

1. RowResult: Wraps standard pgx.Rows. Used by list commands like \l, \dt, \du.
2. DescribeTableListResult: Returned by \d [pattern]. Contains a list of DescribeTableResult structs, each with:
   • Columns: Header names
   • Data: Grid data (rows)
   • TableMetaData: Footer info (Indexes, Constraints, Triggers, etc.)
3. ExtensionVerboseListResult: Returned by \dx+ [pattern, Contains a list of ExtensionVerboseResult structs, each with:
   • Name: Extension name
   • Description: Extension's description

Contributing

Contributions are welcome! Feel free to open issues or submit pull requests for bug fixes, new commands, improved tests, or documentation enhancements.

License

This project is licensed under the MIT License. See the LICENSE file for details.

Documentation

Overview

this package contains special command types

Index

• func RegisterCommand(cmdRegistry SpecialCommandRegistry)
• type DescribeTableListResult
  • func (DescribeTableListResult) ResultKind() SpecialResultKind
• type DescribeTableResult
• type ExtensionVerboseListResult
  • func (ExtensionVerboseListResult) ResultKind() SpecialResultKind
• type ExtensionVerboseResult
• type RowResult
  • func (r RowResult) ResultKind() SpecialResultKind
• type SpecialCommand
• type SpecialCommandRegistry
• type SpecialCommandResult
  • func ExecuteSpecialCommand(ctx context.Context, queryer database.Queryer, specialCommand string) (SpecialCommandResult, bool, error)
• type SpecialHandler
• type SpecialResultKind
• type TableFooterMeta

Functions

func RegisterCommand

func RegisterCommand(cmdRegistry SpecialCommandRegistry)

RegisterCommand registers a special command and its aliases in the command registry.

The command name and aliases are normalized based on the CaseSensitive flag. If CaseSensitive is false, command keys are stored in lowercase, making lookup case-insensitive.

If multiple aliases are provided, each alias is registered to reference the same command definition.

RegisterCommand does not perform validation for duplicate command names or aliases; later registrations will overwrite existing entries with the same key.

Types

type DescribeTableListResult

type DescribeTableListResult struct {
	Results []DescribeTableResult
}

DescribeTableListResult holds multiple DescribeTableResult entries. This is used when multiple tables are described in a single command.

func (DescribeTableListResult) ResultKind

func (DescribeTableListResult) ResultKind() SpecialResultKind

type DescribeTableResult

type DescribeTableResult struct {
	Columns       []string
	Data          [][]string
	TableMetaData TableFooterMeta
}

DescribeTableResult holds the result of a describe table command. this is not used in any return types directly, but is embedded in DescribeTableListResult.

syntax: \d table_name

type ExtensionVerboseListResult

type ExtensionVerboseListResult struct {
	Results []ExtensionVerboseResult
}

ExtensionVerboseListResult holds multiple ExtensionVerboseResult entries. This is used when multiple extensions are described in a single command.

syntax: \dx+ extension_pattern**

func (ExtensionVerboseListResult) ResultKind

func (ExtensionVerboseListResult) ResultKind() SpecialResultKind

type ExtensionVerboseResult

type ExtensionVerboseResult struct {
	Name        string
	Description []string
}

ExtensionVerboseResult holds the result of a single extension verbose command. This is not used in any return types directly, but is embedded in ExtensionVerboseListResult.

type RowResult

type RowResult struct {
	Rows pgx.Rows
}

RowResult is a wrapper around pgx.Rows to implement SpecialCommandResult. It is used for commands that return a set of rows. For example, \dt to list tables. The caller is responsible for closing the Rows when done.

func (RowResult) ResultKind

func (r RowResult) ResultKind() SpecialResultKind

type SpecialCommand

type SpecialCommand struct {
	Cmd           string
	Syntax        string
	Description   string
	Handler       SpecialHandler
	CaseSensitive bool
}

SpecialCommand represents a parsed and executable special command.

It contains the normalized command name, descriptive metadata, and the handler function invoked during execution. SpecialCommand values are stored internally

type SpecialCommandRegistry

type SpecialCommandRegistry struct {
	Cmd           string
	Alias         []string
	Syntax        string
	Description   string
	Handler       SpecialHandler
	CaseSensitive bool
}

SpecialCommandRegistry describes a special command registration.

It defines the command name, optional aliases, documentation metadata, and execution handler used when registering commands via RegisterCommand.

type SpecialCommandResult

type SpecialCommandResult interface {
	// ResultKind indicates the kind of special result.
	ResultKind() SpecialResultKind
}

func ExecuteSpecialCommand

func ExecuteSpecialCommand(ctx context.Context, queryer database.Queryer, specialCommand string) (SpecialCommandResult, bool, error)

ExecuteSpecialCommand parses and executes a special command using the command registry. Syntax: \command[+] [args]

\command - actual special command

\command[+] - actual special command with verbose mode

A special command is identified by a leading backslash (`\`). If the input does not start with a backslash, ExecuteSpecialCommand returns (nil, false, nil) to indicate that the input should be treated as a normal query.

The first whitespace-delimited token is treated as the command name. If the command name ends with a plus sign (`+`), verbose mode is enabled and the suffix is removed before command lookup. The remaining input is passed to the command handler as arguments.

The provided Queryer is used by the command handler to execute any required queries. Return values:

• SpecialCommandResult: the result returned by the command handler, if any
• bool: true if the input was recognized as a special command, even if execution failed
• error: non-nil if the command is unknown or execution fails

An error is returned if the command is not found in the registry or if the command handler returns an error.

type SpecialHandler

type SpecialHandler func(ctx context.Context, db database.Queryer, args string, verbose bool) (SpecialCommandResult, error)

SpecialHandler defines the signature for a special command handler.

A SpecialHandler is invoked with the parsed command arguments and an optional verbose flag. The provided db is used to execute queries as needed.

The returned pgx.Rows, if non-nil, is passed back to the caller for consumption. Any error returned indicates command execution failure.

type SpecialResultKind

type SpecialResultKind int

const (
	ResultKindRows SpecialResultKind = iota
	ResultKindDescribeTable
	ResultKindExtensionVerbose
)

type TableFooterMeta

type TableFooterMeta struct {
	Indexes          []string // lines under "Indexes:"
	CheckConstraints []string // "Check constraints:"
	ForeignKeys      []string // "Foreign-key constraints:"
	ReferencedBy     []string // "Referenced by:"
	ViewDefinition   *string  // "View definition:"

	RulesEnabled  []string // under "Rules:"
	RulesDisabled []string // "Disabled rules:"
	RulesAlways   []string // "Rules firing always:"
	RulesReplica  []string // "Rules firing on replica only:"

	TriggersEnabled  []string // "Triggers:"
	TriggersDisabled []string // "Disabled triggers:"
	TriggersAlways   []string // "Triggers firing always:"
	TriggersReplica  []string // "Triggers firing on replica only:"

	PartitionOf          []string // "Partition of:"
	PartitionConstraints []string // "Partition constraint:"
	PartitionKey         *string  // "Partition key:"
	Partitions           []string // "Partitions:" (or leave empty)
	PartitionsSummary    *string  // "Number of partitions ..." (non-verbose form)

	Inherits           []string // "Inherits"
	ChildTables        []string // "Child tables" (verbose)
	ChildTablesSummary *string  // "Number of child tables..."
	TypedTableOf       *string  // "Typed table of type:"
	HasOIDs            *bool    // "Has OIDs: yes|no"
	Options            *string  // "Options: ..."
	Server             *string  // "Server: ..."  (foreign tables)
	FDWOptions         *string  // "FDW Options: (...)" (foreign tables)
	OwnedBy            *string  // "Owned by:" (sequences)
}

TableFooterMeta holds the metadata found at the footer of a \d table description this is not used in any return types directly, but is embedded in DescribeTableResult.