Class PostgreSQLCodeGenProvider

The SQL dialect instance for this provider.

PostgreSQL has no sp_refreshview. Generated views are re-issued with CREATE OR REPLACE (or the 42P16 capture/DROP CASCADE path) every run, so they do not need a separate refresh step. Layered outer views are rebound via generateLayeredOuterRebindSQL, not this flag.

PostgreSQL requires a nullability fix for virtual (computed) fields in views. View columns derived from expressions may report incorrect nullability in information_schema.columns, so CodeGen must correct these after view creation.

The database platform key (e.g., 'sqlserver', 'postgresql').

Returns the native timestamp-with-timezone type name for this platform. SQL Server: DATETIMEOFFSET PostgreSQL: TIMESTAMPTZ

Generates ALTER TABLE ... ADD COLUMN SQL. SQL Server: ALTER TABLE [schema].[table] ADD colName TYPE [NOT] NULL [DEFAULT expr] PostgreSQL: ALTER TABLE schema."table" ADD COLUMN "colName" TYPE [NOT] NULL [DEFAULT expr]

Generates SQL to add a default constraint/value to a column. SQL Server: ALTER TABLE ... ADD CONSTRAINT DF_name DEFAULT expr FOR [col] PostgreSQL: ALTER TABLE ... ALTER COLUMN "col" SET DEFAULT expr

Generates ALTER TABLE ... ALTER COLUMN to change type and nullability. SQL Server: ALTER TABLE ... ALTER COLUMN col TYPE NULL|NOT NULL PostgreSQL: ALTER TABLE ... ALTER COLUMN "col" TYPE type, ALTER COLUMN "col" SET|DROP NOT NULL

Builds a set of PL/pgSQL components for working with an entity's primary key(s) in cascade operations: variable declarations, SELECT field list, FETCH INTO variable list, and named routine parameter assignments. Used by cascade delete and update-to-NULL generators to construct cursor-based loops.

Generates SQL to invoke a stored procedure or function. SQL Server: EXEC [schema].[spName] @Param1='val1', @Param2='val2' PostgreSQL: SELECT * FROM schema."spName"('val1', 'val2')

Compares two PostgreSQL data type strings for equivalence, accounting for common aliases. For example, 'timestamptz' and 'timestamp with time zone' are considered equal. Returns true if the types match directly or via alias lookup.

Generates a conditional INSERT statement (insert only if not exists). SQL Server: IF NOT EXISTS (checkQuery) BEGIN insertSQL END PostgreSQL: DO $$ BEGIN IF NOT EXISTS (checkQuery) THEN insertSQL; END IF; END $$

Both arguments must already be identifier-quoted by the caller (qi()/qs()). On PostgreSQL the result is a DO $$ ... $$ block, and the identifier auto-quoter (quoteSQLForExecution, applied later by runQuery/LogSQLAndExecute) skips dollar-quoted blocks wholesale — it cannot know whether their contents are SQL or literal text. So this is the one SQL-building path where the usual "write it bare, the quoter handles it" convention does not hold: a bare ID survives to PG folded as id and the statement fails every run.

Generates a PL/pgSQL DO $$ block that drops both a named CHECK constraint (if one exists on the column, found via pg_catalog.pg_constraint) and the column's default value. Uses dynamic SQL (EXECUTE format(...)) to drop the constraint by name, then unconditionally runs ALTER COLUMN ... DROP DEFAULT.

Generates a DROP statement for a database object (view/procedure/function). SQL Server: IF OBJECT_ID('...', 'P') IS NOT NULL DROP PROCEDURE ... PostgreSQL: DROP FUNCTION IF EXISTS ... CASCADE

Note: This differs from generateDropGuard() which is used for CREATE OR REPLACE patterns. This method is used for cleanup operations.

Phased per-entity execution for PG. Runs view → CRUD functions → view permissions against the target DB, guaranteeing phase 2 is skipped if phase 1 failed (so we never leave fn_create_* functions pointing at a missing or stale view's rowtype).

Phase 1 routes through executeWithFallback so a 42P16 triggers the capture/drop/recreate/restore flow rather than blowing up. Phase 2 runs each CRUD function's CREATE individually — we do NOT concatenate them because node-pg's simple query protocol would then abort the whole batch on the first failure; running them separately gives a per-routine error signal. Phase 3 applies view-level GRANTs.

Executes a SQL file against the PostgreSQL database using an in-process pg client. Mirrors the SQL Server provider's in-process approach so CodeGen does not depend on the psql CLI being installed on the host. Reads connection parameters from environment variables (PG_HOST, PG_PORT, PG_DATABASE, PG_USERNAME, PG_PASSWORD) with fallback to configInfo values.

SQL Server built-in functions (e.g., NEWID() to gen_random_uuid(), GETUTCDATE() to NOW() AT TIME ZONE 'UTC'), strips outer parentheses and surrounding single quotes, and re-applies quoting based on the needsQuotes flag. Returns 'NULL' for empty or whitespace-only input.

PostgreSQL charges full-text search on the WRITE path, not the read path: the emitted objects include a row-level BEFORE INSERT OR UPDATE OF <fields> trigger that recomputes to_tsvector for the row, a GIN index that is maintained on every one of those writes, and a one-off backfill UPDATE across the existing table at apply time. None of that is reversed by clearing the entity's full-text flags later — the column, trigger and index stay until someone drops them.

Generates a comment header for the combined all-entities SQL file.

Generates a PostgreSQL table-valued function returning all ancestors of a node walking upward.

Generates a PostgreSQL view-regeneration block for an entity's base view.

Includes all base table columns, parent/related field joins, and root field lateral joins. Applies a soft-delete WHERE filter when the entity uses soft deletes.

Two-path emission (try-then-fallback). The output wraps CREATE OR REPLACE VIEW in a DO $$ ... EXCEPTION WHEN invalid_table_definition THEN DROP VIEW ... CASCADE; EXECUTE vsql; END $$ block. Why:

• Happy path: CREATE OR REPLACE VIEW succeeds (new column list is a prefix of the existing one plus optional trailing additions). Zero destruction. No dependent views, functions, triggers, or grants are touched.

• Sad path: PG raises SQLSTATE 42P16 invalid_table_definition for any column rename / reorder / type change / removal. The exception handler runs DROP VIEW ... CASCADE and re-executes the CREATE. Dependent codegen-managed functions (spCreate/spUpdate/spDelete returning SETOF vwFoo) and dependent views are CASCADE-dropped — they are regenerated later in the same codegen output stream, so by the end of the run all dependents are restored to the new shape. GRANTs on the view itself are also lost on the CASCADE; codegen always re-emits permissions immediately after the view, so they come back too.

The runtime-apply path also calls this, so the live DB applies the same DO block. executeWithFallback (the runtime helper) becomes a no-op for these statements because the DO block handles 42P16 internally — but it still runs as a safety net for any other failure modes.

What this DOES NOT preserve on the sad path: non-codegen-managed dependent objects (e.g. a hand-written sproc against this view that codegen doesn't know about). Those would be CASCADE-dropped and not restored. MJ codegen-generated sprocs cover all standard CRUD pathways; bespoke sprocs against base views are extremely rare in practice. If a project does have them, they need to be re-applied after a 42P16 fallback fires.

This pattern matches the v5.30.x fix migration V202604282300 — which used the same DO/EXCEPTION construct to recreate vwEntityPermissions after the unquoted RoleName alias bug — proving the pattern is production-tested.

Permissions are handled separately by sql_codegen.ts via generateViewPermissions().

PostgreSQL create-or-replace for a config-declared view.

CREATE OR REPLACE VIEW alone is not a full equivalent of SQL Server's CREATE OR ALTER VIEW: PG raises SQLSTATE 42P16 invalid_table_definition when the new body renames, reorders, retypes or drops an existing column. So the statement runs inside a DO block that, on 42P16 only, drops the view and re-runs the create.

The drop is deliberately not CASCADE. Unlike a base view, nothing CodeGen generates depends on a config-declared view, so any dependent is user-owned and CodeGen could not restore it — PG refuses the drop (2BP01) and the run fails loudly rather than silently destroying it. Grants on the view are lost on that path (a NOTICE names the view so an operator can re-apply them); the happy path keeps them.

The body travels inside dollar quotes, which quoteSQLForExecution skips — it must be valid PostgreSQL as written, with mixed-case identifiers already double-quoted.

Generates a PostgreSQL CREATE OR REPLACE FUNCTION for inserting a new record. The function accepts typed parameters for each writable field, performs an INSERT into the base table, and returns the newly created row from the base view via RETURN QUERY SELECT. Handles auto-increment PKs (using RETURNING ... INTO), UUID PKs (with COALESCE to gen_random_uuid()), and composite PKs. Also emits GRANT EXECUTE permissions for authorized roles.

Prepends a DROP-all-overloads block (see generateDropAllOverloadsBlock) so adding/removing a column doesn't trigger PG's overload-ambiguity error.

Wide entities (where useJsonArgShape returns true) emit a JSON-arg variant via generateCRUDCreateJsonArg — single p_data JSONB parameter, dynamic INSERT built from keys present in the payload. Same semantics; different wire shape needed because of PostgreSQL's 100-arg function ceiling.

Generates a PostgreSQL CREATE OR REPLACE FUNCTION for deleting a record. Supports both hard deletes (DELETE FROM) and soft deletes (UPDATE ... SET __mj_DeletedAt). Prepends any cascade SQL for dependent records, uses #variable_conflict use_column to avoid PL/pgSQL naming conflicts, and returns the affected primary key(s) or NULLs if no row was found. Emits GRANT EXECUTE permissions for authorized roles.

PostgreSQL override: same tolerant-SP shape as the base class, but with PG's "all params after the first DEFAULT must also have DEFAULTs" rule enforced. Once any parameter becomes optional (or a _Clear companion is emitted), every subsequent parameter is forced to DEFAULT NULL even if it would otherwise be required, because PG function signatures don't allow gaps between defaulted params.

All decision logic and dialect-syntax bits route through the same base-class helpers / dialect methods used by SQL Server — the only thing that differs is the sticky-defaults walk.

Generates GRANT EXECUTE ON FUNCTION statements for the given CRUD function, granting access to each role that has the corresponding permission (Create, Update, or Delete) on the entity.

Generates a PostgreSQL CREATE OR REPLACE FUNCTION for updating an existing record. The function accepts typed parameters for all updatable fields plus primary key(s), performs an UPDATE ... SET ... WHERE PK = param, checks ROW_COUNT to detect missing rows, and returns the updated record from the base view (or PK + datestamp columns from the base table when entity.SaveReturnMode === 'datestamps_only') via RETURN QUERY SELECT. Also generates the __mj_UpdatedAt timestamp trigger for the entity and emits GRANT EXECUTE permissions.

Generates a PostgreSQL table-valued function returning all descendant records of a root node.

Generates a PostgreSQL DROP ... IF EXISTS ... CASCADE statement as a guard before creating or replacing a database object. For triggers, PostgreSQL relies on CREATE OR REPLACE on the trigger function, so a comment is emitted instead.

Generates full-text search infrastructure for an entity: SQL Server: FULLTEXT CATALOG + INDEX + inline TVF PostgreSQL: tsvector column + GIN index + trigger + search function

Generates GRANT EXECUTE permission for a full-text search function.

Generates a LEFT JOIN LATERAL clause that invokes the hierarchy metadata function for a self-referencing field.

Generates the SELECT expressions for hierarchy fields in a base view. Projects: Root, Depth, Path, IsLeaf, ChildCount.

Generates a PostgreSQL function returning a table of hierarchy metadata (RootID, Depth, Path, IsLeaf, ChildCount) for a self-referencing foreign key.

Wraps innerSQL so it runs only when the named view already exists.

Used for objects CodeGen refreshes or grants on but does NOT create — specifically the application-owned outer view of a layered entity. On the first CodeGen pass after layering is enabled, that view legitimately does not exist yet: it selects from the inner view that this very pass is creating, so it cannot have been created earlier. Emitting an unguarded sp_refreshview/GRANT against it fails the run and blocks the only path to setting layering up. Every later pass finds the view present and behaves identically to the unguarded form.

Restar g.* on the application-owned outer view. Invokes the catalog function shipped by the PG layered-views migration; no-op if the function is not installed yet (bootstrap before that migration).

PostgreSQL materialized-table DDL. The create is conditional (CREATE TABLE IF NOT EXISTS) so a migration-provided materialized_<name> table with bespoke indexing is detected and reused rather than clobbered (plan §12). Emits the single-column surrogate PRIMARY KEY, which is itself the required unique index.

PG dialect vs. SQL Server: double-quoted identifiers, CREATE TABLE IF NOT EXISTS in place of the IF OBJECT_ID(...) IS NULL guard, and the surrogate column carries PG's GENERATED ALWAYS AS IDENTITY clause (see getMaterializedSurrogateColumnType).

PostgreSQL wrapper-view DDL — the stable read contract over the materialized table. Uses CREATE OR REPLACE VIEW so the same statement both creates the view and atomically repoints it at a freshly-built table during refresh (plan §11.2). The body is always SELECT * FROM <table> and the shadow table shares the column shape, so PG's CREATE OR REPLACE column-compatibility rule is satisfied on the swap.

Generates a LEFT JOIN LATERAL clause that invokes the root ID function for a self-referencing field. PostgreSQL uses LATERAL joins (rather than SQL Server's OUTER APPLY) to call scalar functions inline within a view definition.

Generates the SELECT expression for a root ID field in a base view. SQL Server: rootFn.RootID AS [FieldRoot...] PostgreSQL: root_fn.root_id AS "FieldRoot..."

Generates a PostgreSQL function that recursively calculates the root record ID for a self-referencing foreign key.

Generates the cascade delete/update SQL for a single related entity. Called by the orchestrator for each FK relationship when CascadeDeletes is true.

Generates a comment header for a generated SQL file.

Generates a PL/pgSQL DO $$ block that conditionally adds __mj_CreatedAt and __mj_UpdatedAt columns to a table using TIMESTAMPTZ type with a UTC default. Uses information_schema checks to skip columns that already exist.

Generates a PL/pgSQL trigger function and a BEFORE UPDATE trigger that automatically sets the __mj_UpdatedAt column to the current UTC time on every row update. Uses CREATE OR REPLACE FUNCTION for the trigger function and DROP TRIGGER IF EXISTS + CREATE TRIGGER for idempotent trigger creation. Returns an empty string if the entity has no __mj_UpdatedAt field.

Generates GRANT SELECT permission for a base view.

Generates SQL to refresh/recompile a view. SQL Server: EXEC sp_refreshview 'schema.viewName'; PostgreSQL: returns empty string (no-op).

Generates a simple test query to validate a view is functional. SQL Server: SELECT TOP 1 * FROM [schema].[viewName] PostgreSQL: SELECT * FROM "schema"."viewName" LIMIT 1

Produces the canonical database function name for the ancestors traversal helper function.

Returns an additional WHERE clause fragment for the check-constraints query. SQL Server: WHERE SchemaName NOT IN (...) when excludeSchemas is provided. PostgreSQL: empty string (the PG view already handles schema filtering).

Generates a query against pg_index, pg_class, pg_namespace, and pg_attribute to check whether a column participates in a multi-column unique constraint. Returns rows only when the unique index contains more than one column and includes the specified column.

Returns the name of the CRUD routine for an entity. SQL Server: spCreateEntityName PostgreSQL: fn_create_entity_name

Produces the canonical database function name for the descendants traversal helper function.

Returns an additional WHERE clause fragment for the missing-base-tables query. SQL Server: WHERE VirtualEntity=0 PostgreSQL: empty string (PG query doesn't need this filter).

Generates SQL to fix virtual field nullability after metadata sync. PostgreSQL: Updates AllowsNull for virtual fields based on the FK column's nullability. SQL Server: returns empty string (no fix needed).

Returns a SQL query string to check if a foreign key index already exists. Used by the orchestrator to conditionally create FK indexes. SQL Server: queries sys.indexes with OBJECT_ID PostgreSQL: queries pg_indexes

The result set should return rows if the index exists (length > 0 means exists).

Produces the canonical database function name for the hierarchy metadata helper function.

PostgreSQL KEYED surrogate key type: the combined-key SHA-256 hash the refresh writes comes from encode(digest(…), 'hex'), whose type is text, and the full rebuild's CREATE TABLE AS infers exactly that. Typing the minted entity's PK column text MATCHES the post-refresh physical column, avoiding the int-vs-hash divergence between CodeGen metadata and the rebuilt table.

PostgreSQL synthetic surrogate key: a SQL-standard auto-assigned identity column (GENERATED ALWAYS AS IDENTITY), bigint for headroom on large materialized sets. v1 full-rebuild only; the deterministic combined-key hashing in §5 replaces this in Phase 3.

Returns DDL that creates/replaces the platform's metadata-management support objects (introspection views and the routines manage-metadata invokes via callRoutineSQL), or null when the platform ships them through migrations instead.

These objects are CodeGen's own machinery — only CodeGen calls them — so platforms that return DDL here get it executed (idempotently) at the start of every manageMetadata run. That guarantees the objects can never be missing or version-skewed relative to the CodeGenLib code that calls them.

SQL Server: returns null (objects ship in the baseline migrations). PostgreSQL: returns the full support-object DDL.

Generates the SQL to retrieve pending entity fields that exist in the database but not yet in the MJ metadata. This is a large, platform-specific query.

Generates a query against pg_index, pg_class, and pg_namespace to retrieve the index name for a table's primary key constraint. Used by CodeGen to reference the PK index in full-text search and other operations.

Produces the canonical name for the recursive root-finder helper function generated for self-referencing fields. The function definition and the view's LATERAL-JOIN reference must agree on this name (caller side calls via generateRootFieldJoin, definition via generateRootIDFunction).

Note: this intentionally does NOT match the baseline-shipped PascalCase fn{Table}{Field}_GetRootID form, because the baseline returns TABLE("RootID" type) and the view callers expect a scalar — using the baseline name would clash with cannot change return type of existing function. The snake_case scalar form is codegen's own naming space and is consistent with how downstream views are emitted.

Returns a SQL query that lists every stored procedure / function name in the given schemas. Used by the post-run CRUD validator to diff expected vs actual routine presence in one round trip.

The result set must contain exactly two columns: schema_name — the schema the routine lives in routine_name — the proc/function name as stored in the catalog

SQL Server: queries sys.objects for procedures and functions. PostgreSQL: queries pg_proc joined to pg_namespace.

Default: returns empty string. Providers that don't implement this opt out of the post-run CRUD validator (the validator returns no missing when this returns empty), preserving backwards compatibility for downstream subclasses that haven't been updated.

Returns an array of system schema names that should be excluded from metadata synchronization. Empty array if the platform has no system schemas that need excluding. SQL Server: [] (no system schemas need excluding) PostgreSQL: ['information_schema', 'pg_catalog', 'pg_toast', ...]

Returns a query listing every non-primary-key unique index (or unique constraint) that contains a column, one row per index, with two columns:

• KeyColumnCount: the number of key columns in the index, not counting INCLUDE columns
• IsIncludedColumn: true when the column is only an INCLUDE column of that index

A column that is unique on its own has a row with KeyColumnCount = 1 and IsIncludedColumn = false. EntityField.IsUnique cannot tell that apart: schema sync sets it on every column of a composite unique constraint.

The default returns null, meaning the platform cannot answer; callers then keep relying on EntityField.IsUnique.

Returns SQL to get column metadata for a view or table. Result columns: FieldName, Type, Length, Precision, Scale, AllowsNull.

Returns a SQL query string to retrieve the current view definition from the database. SQL Server: SELECT OBJECT_DEFINITION(OBJECT_ID('[schema].[viewName]')) AS ViewDefinition PostgreSQL: SELECT pg_get_viewdef('"schema"."viewName"'::regclass, true) AS "ViewDefinition"

The result set must include a column named ViewDefinition.

Returns SQL to check if a view exists. The query uses @ViewName and @SchemaName as named parameters. Returns 1 row if the view exists.

Parses a PostgreSQL column default value by stripping PG-specific type cast syntax (e.g., '2024-01-01'::timestamp becomes '2024-01-01'). Returns null for auto-increment sequences (nextval(...)) and for null/undefined input, indicating no meaningful default.

Quotes mixed-case identifiers in a raw SQL string for execution. SQL Server: returns the SQL unchanged (case-insensitive identifiers). PostgreSQL: double-quotes PascalCase identifiers to preserve case.

PG-specific base-view regeneration with 42P16 recovery.

Runs the provided CREATE OR REPLACE VIEW SQL through executeWithFallback on a dedicated connection: happy path issues the CREATE OR REPLACE directly, and only on SQLSTATE 42P16 does the capture/drop/recreate/restore dance fire inside a transaction that preserves every dependent view, function, grant, comment, and owner. See viewFallback.ts for the contract.

willRegenerate is passed through so dependents CodeGen is about to rebuild in the same run are skipped at restore time (avoids restoring a stale captured definition against a newly-regenerated target).

PostgreSQL implementation of CodeGenDatabaseProvider.SetupDataSource.

Acquires the module-cached pg.Pool via PGConnection (so repeated CodeGen operations reuse the same pool — matching SQL Server's behavior), wires up PostgreSQLDataProvider, registers it as the active provider, and loads the audit user.

Env var resolution — PG_HOST / PG_PORT / PG_DATABASE / PG_USERNAME / PG_PASSWORD now flow through configInfo.{dbHost,dbPort,dbDatabase,codeGenLogin,codeGenPassword} via DEFAULT_CODEGEN_CONFIG (see Config/config.ts). The provider just reads configInfo.

Wraps an INSERT statement with a conditional existence check at the statement level. SQL Server: Adds IF NOT EXISTS (...) BEGIN prefix and END suffix. PostgreSQL: Adds ON CONFLICT DO NOTHING suffix (no prefix needed).