Class PostgreSQLCodeGenProvider

The SQL dialect instance for this provider.

PostgreSQL does not require view refresh after creation. Unlike SQL Server's sp_refreshview, PostgreSQL views automatically reflect column changes, so this always returns false.

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 $$

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.

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

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().

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 via RETURN QUERY SELECT. Also generates the __mj_UpdatedAt timestamp trigger for the entity and emits GRANT EXECUTE permissions.

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 a complete PostgreSQL full-text search infrastructure for an entity. This includes:

1. A tsvector column (__mj_fts_vector) added via conditional ALTER TABLE
2. A PL/pgSQL trigger function that concatenates search fields into a tsvector
3. A BEFORE INSERT OR UPDATE trigger to keep the vector column in sync
4. A GIN index on the tsvector column for fast lookups
5. A SQL STABLE search function that joins the base view with a plainto_tsquery match
6. A backfill UPDATE to populate existing rows where the vector is NULL

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

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 SQL STABLE function that walks a self-referencing hierarchy (e.g., ParentCategoryID) using a recursive CTE to find the root ancestor record. The CTE starts from COALESCE(p_parent_id, p_record_id) as the anchor and follows the parent FK upward, capped at 100 levels to prevent infinite loops. Returns the root record's primary key value.

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

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

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).

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.

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 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 SQL string for PostgreSQL compatibility. Uses a tokenizer approach to skip string literals, already-quoted identifiers, dollar-quoted blocks, and SQL keywords. Any remaining PascalCase word gets double-quoted 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.

User-loading asymmetry — SQL Server uses UserCache.Instance.Refresh(pool) which is hard-typed to mssql.ConnectionPool in @memberjunction/sqlserver-dataprovider. Refactoring it to be cross-platform would touch that package's public API; until then PG hand-queries vwUsers/vwUserRoles here. Same audit-user semantics (find Owner, else first user), just a different load path. Tracked for follow-up: unify behind a platform-agnostic cache that takes a CodeGenConnection.

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).