Overview
PL/pgSQL is PostgreSQL's procedural extension to SQL, used for functions, procedures, and triggers in Postgres itself and in Postgres-derived platforms like Redshift. It is one of the source dialects 3XCode's conversion pipeline recognizes natively, represented internally as the plpgsql value of the pipeline's SQLDialect enum, alongside tsql (SQL Server), plsql (Oracle), mysql, ansi (generic ANSI SQL), and spark_sql.
When you run 3xcode pyspark convert against a .sql file written in PL/pgSQL, the Discovery phase classifies it as plpgsql, inventories every function, procedure, view, and trigger it contains, and hands that inventory to Planning, which decides conversion order and pulls in only the PL/pgSQL-relevant knowledge (JSON/JSONB handling, array operators, upsert patterns, and so on) needed to convert it correctly.
How PL/pgSQL Is Detected
Detection happens in two separate layers during the Discovery phase, and neither one requires you to tell 3XCode what dialect a file is in.
Dialect Classification (LLM)
Claude reads the file during Discovery and classifies it into the SQLDialect enum. A field validator normalizes dozens of possible spellings ('Postgres' and 'Redshift' both normalize to plpgsql), so variation in how the source labels itself doesn't affect classification.
Platform Fingerprinting (deterministic)
A separate, regex-only pass scores the file against syntax markers for 10 vendor platforms (including PostgreSQL and Redshift) with zero LLM cost. It returns the winning platform, a confidence label, and the exact markers that fired, so platform detection is fully explainable rather than a black box.
Every generated PySpark file gets a one-line header comment naming the detected source platform, confidence level, and matched markers, so you can always see how a given .py output was classified. Discovery also builds a full object inventory for the file, covering name, type, exact line range, parameters, cross-object references, and flags for cursors, dynamic SQL, and transactions, which downstream phases use to plan and convert.
What Gets Converted
The Planning phase topologically sorts discovered objects and converts them in dependency order (views and standalone functions first, then procedures, then triggers), so a procedure that references a view or function always sees that object's converted signature rather than a guess.
Functions & Procedures
Both PL/pgSQL FUNCTION and PROCEDURE objects are inventoried and converted into PySpark modules with private step/helper functions and a run_pipeline(spark, params) orchestrator, preserving the original logical flow.
Views
Converted first in the dependency order, since procedures and triggers that reference them need real, converted signatures (not placeholders) to convert correctly.
Triggers
Inventoried and converted with their referenced tables and cross-object dependencies resolved, following the same Views → Functions → Procedures → Triggers precedence.
Cursors
Detected via a has_cursor flag during Discovery and flagged for the conversion agent to rewrite as native PySpark window functions rather than row-by-row Python loops, since Python UDF loops are dramatically slower than native F.xxx operations at scale.
Known Behavioral Differences
3XCode's validation and auto-fix phases check generated PySpark against a knowledge base of known PL/pgSQL-to-Spark behavioral gaps. The most relevant ones for PostgreSQL sources:
PL/pgSQL → PySpark gotchas the pipeline checks for
| Construct | Gotcha | Handling |
|---|---|---|
date_sub with a negative interval | Passing a negative value to date_sub during translation moves the date forward, not backward. That is the opposite of what the original PL/pgSQL logic intended. | Flagged during Validation; targeted by Auto-Fix when detected. |
MERGE / upsert (INSERT ... ON CONFLICT) | PostgreSQL's ON CONFLICT upsert pattern has no single direct PySpark equivalent. | Classified as a manual-review item, with prescriptive guidance toward a Delta Lake MERGE INTO or filter-and-union pattern, never auto-fixed. |
| Cursors | PL/pgSQL cursors have no direct PySpark equivalent. Row-by-row Python loops are 50–100x slower than native operations. | Discovery sets has_cursor; conversion is instructed to rewrite as window functions where possible, otherwise flagged for manual review. |
| Dynamic SQL (`EXECUTE`) | No safe automatic transform exists for dynamically constructed SQL text. | Classified as a manual-review item, never auto-fixed. |
| Transactions (`BEGIN` / `COMMIT` / `ROLLBACK`) | PL/pgSQL transaction control has no direct PySpark equivalent. | Discovery sets has_transaction; classified as a manual-review item. |
| NULL semantics with `COUNT(*)` vs `COUNT(col)` | The two behave differently with NULLs, and it's easy to carry the wrong one over in translation. | Checked during Validation's NULL-semantics review. |
| String comparisons (`ILIKE` and collation) | Spark is case-sensitive by default, which can differ from PL/pgSQL's ILIKE or the source collation's comparison behavior. | Checked during Validation. |
| Array and JSON/JSONB operators | Postgres array operators (`@>`, `&&`) and JSONB operators (`->`, `->>`, `#>`) don't have a mechanical one-to-one Spark equivalent and need to be mapped to the right F.array_*/F.json/F.get_json_object function for the case. | Detected via construct tagging during Discovery; relevant knowledge-base sections are injected into Planning and Conversion only when these operators are present. |
| Decimal arithmetic | Precision can be lost across certain arithmetic operations when types are mapped. | Checked during Validation; flagged as a TODO if unresolved. |
Issues the pipeline can't safely auto-fix (cursors requiring a rewrite, dynamic SQL, upsert logic, and transaction control) are surfaced as prioritized, developer-facing action items after conversion, each with prescriptive guidance on how to resolve it by hand.
Converting a PL/pgSQL File
No flag is needed to tell 3XCode a file is PL/pgSQL. Dialect and platform are detected automatically during Discovery. Just point the pyspark agent at your file or directory:
# Convert a single PL/pgSQL / PostgreSQL script 3xcode pyspark convert functions.sql # Dry-run: discovery + planning only, no conversion 3xcode pyspark analyze functions.sql # Convert every .sql file in a directory (bulk mode) 3xcode pyspark convert --dir ./sql/ --session "postgres-migration" -w 4 # Resume an interrupted bulk session 3xcode pyspark convert --resume postgres-migration
After conversion, check <stem>_audit.md in your output directory for the per-file confidence/completion grade and the list of items that need manual review: upsert (ON CONFLICT) logic, cursors, and dynamic SQL in particular are common PL/pgSQL constructs that land there rather than being silently auto-converted.
FAQ
Do I need to specify that my file is PL/pgSQL?
No. Dialect classification and platform fingerprinting both run automatically during the Discovery phase. There is no --dialect flag to set.
Does 3XCode support Redshift too?
Yes, Redshift is built on PostgreSQL and its procedural syntax normalizes to the same plpgsql dialect, and it's one of the 10 vendor platforms the deterministic fingerprinting layer scores against directly.
How does 3XCode handle JSONB and array columns?
Discovery tags files that use JSON/JSONB or array operators so Planning and Conversion pull in the matching knowledge-base guidance for mapping them to the right PySpark functions. This selective injection is also why simple SELECT/JOIN files convert with a much smaller prompt than files that use every Postgres-specific construct.
What happens to ON CONFLICT upserts and cursors that can't be auto-converted?
They're never silently dropped or guessed at. Auto-Fix classifies them as manual-review items with a revert-on-regression safety net, and they appear in the audit report's developer action items with specific guidance on how to rewrite them by hand.
Is my PL/pgSQL source code sent to the 3XDE backend?
No. Source SQL is never sent to the 3XDE backend: the CLI talks to your configured LLM provider directly for conversion. The backend only handles auth, licensing, and provider-key metadata.