Render PostGIS geometry/geography columns as WKT (PostgreSQL plugin)

[shubhank-saxena]

First off, I really love what you're building with TablePro. The native-multi-DB-open-source niche has been empty for a long time, and the polish on v0.45 is great. Happy to take a crack at implementing this one if you'd be open to a PR; flagging it as an issue first so we can align on approach before I write code.

Problem

When querying a PostgreSQL table with PostGIS geometry or geography columns, the data grid displays the raw EWKB hex string (e.g. 0101000020E61000000000000000008052C0F853E3A59B5C4440) instead of a human-readable representation.

Other native clients (DBeaver, Postico, TablePlus) decode these client-side and show WKT like POINT(-73 40.7237), optionally with a "view on map" affordance. The current behavior in TablePro forces users to wrap every query in ST_AsText(...) manually, which is awkward for SELECT * and breaks ad-hoc table browsing.

Repro:

CREATE EXTENSION postgis;
CREATE TABLE places (id int, name text, point geometry(Point, 4326));
INSERT INTO places VALUES (1, 'a', ST_SetSRID(ST_MakePoint(-73, 40.7237), 4326));
SELECT * FROM places;

Expected: point column shows POINT(-73 40.7237) (or EWKT with SRID).
Actual: point column shows 0101000020E6100000....

Proposed solution

Mirror the approach already shipped for DuckDB in #1329 / v0.45.0: detect spatial column types in result metadata and rewrite the query to wrap matching columns with ST_AsEWKT(col) (EWKT preserves SRID, which plain WKT drops).

One PostgreSQL-specific wrinkle worth flagging up front: PostGIS is an extension, so the type OIDs for geometry and geography are assigned at CREATE EXTENSION time and differ per database. The DuckDB fix could key off the fixed DUCKDB_TYPE_GEOMETRY constant; the PostgreSQL plugin will need to look the OIDs up per connection, e.g.

SELECT oid, typname FROM pg_type WHERE typname IN ('geometry','geography');

cached for the lifetime of the connection. The _tp_cast query-rewrite mechanism from #1329 should otherwise port over directly.

Out of scope for a first pass (but natural follow-ups): map preview panel for geometry cells, raster type support, geography(...) distance display niceties.

Alternatives considered

• Manual ST_AsText(col) in every query, works but defeats SELECT * and ad-hoc table browsing.
• Client-side EWKB parsing in Swift without a query rewrite, possible but duplicates logic PostGIS already does correctly server-side, and would need to track the full WKB spec including curved geometries.

Related database type

PostgreSQL

[datlechin]

Thanks for the detailed write-up and for offering to PR this. The approach is sound and aligns with how DBeaver and pgAdmin handle the same problem. A few decisions to align on before you start, so the PR lands cleanly against the codebase.

Where the code lives

Mirror the DuckDB precedent: keep everything inside Plugins/PostgreSQLDriverPlugin/. No TableProPluginKit changes (so no currentPluginKitVersion bump). Suggested layout:

• PostgreSQLPluginDriver.swift: add a postgisOids: Set<UInt32> stored property and populate it from connect() alongside probeCatalogPresence().
• New PostgreSQLPluginDriver+Spatial.swift: pure functions for OID probe, query rewrite, identifier quoting, and the "is this query safe to wrap" predicate.
• LibPQPluginConnection.swift: columnOids is already captured during fetch but discarded at the PluginQueryResult boundary. Do the spatial check inside fetchResults where OIDs are still in scope, and re-execute the wrapped query before returning.

OID lookup query

The minimum query is enough:

SELECT oid FROM pg_type WHERE typname IN ('geometry', 'geography')

No pg_namespace join needed. PostGIS uses unique type names regardless of schema (Supabase puts it in extensions, AWS RDS puts it in public; the lookup works either way). For v1, skip array OIDs (geometry[] / geography[]), raster, box2d, and box3d. Box types already format as readable text; raster needs a different rewrite; arrays are rare enough in real schemas to leave as a follow-up.

Query rewrite

Wrap as a subquery, not a column-list edit:

SELECT "id", ST_AsEWKT("geom") AS "geom", "name"
FROM (<original query>) AS _tp_rewrite

This handles SELECT *, CTEs, joins, and computed columns uniformly because we project off the result set, not the source tables. Use ST_AsEWKT (not ST_AsText) to preserve SRID, and don't pass maxdecimaldigits (PostGIS 3.1+ only, and the docs warn it can produce invalid geometry). ST_AsEWKT is STRICT, so NULL stays NULL automatically; POINT EMPTY round-trips as text.

Skip rewrite when not viable (fall back to raw HEXEWKB, don't error):

• Multi-statement scripts (top-level ; outside string literals).
• Duplicate output column names (the subquery wrap would produce ambiguous-column errors).
• Non-SELECT / WITH / VALUES shapes. INSERT ... RETURNING is technically wrappable but I'd defer to v1.5.

Identifier quoting

Mirror DuckDB's quoteIdentifier: always wrap output names in "", double any embedded ". Same for the column alias.

Preserve original column metadata

The rewritten column's libpq-reported type becomes text, not geometry. Stash the original OID (or a wasRewrittenAsSpatial flag) on the column metadata so a future edit-back PR can wrap UPDATE ... SET geom = ST_GeomFromEWKT($1) and inject the original SRID if the user's edited text drops it. v1 doesn't need to use this flag; it just shouldn't repaint into a corner.

Cache invalidation

The cache lives on the driver instance. DatabaseManager.reconnectDriver creates a fresh driver and re-runs connect(), so external reconnects naturally repopulate. The internal LibPQDriverCore.reconnect() path doesn't reach back to the outer driver's caches; this is a pre-existing gap shared by catalogPresence. Document the limitation in the PR, don't try to solve it as part of this work.

Tests

• Pure Swift, no live server: identifier quoting, wrapped-query SQL output for single / multiple / zero spatial columns, and the safe-to-wrap predicate.
• Live-server integration: gate behind an env var (POSTGIS_TEST_URL or similar) so CI skips unless explicitly run.

CHANGELOG and docs

• CHANGELOG.md under [Unreleased] → Added, one line: PostgreSQL: PostGIS geometry and geography columns now render as WKT with SRID instead of raw hex. (#1458)
• Short paragraph in docs/databases/postgresql.mdx.

Out of scope for this PR (good follow-ups, separate issues):

• Edit-back via ST_GeomFromEWKT with SRID injection.
• Map preview cell for geometry values.
• raster, box2d, box3d, and array-of-geometry support.

If anything above doesn't match what you had in mind, let's hash it out here before code. Looking forward to the PR.

[shubhank-saxena]

Opened #1467 with the implementation, following the design review above. Quick recap of what's in it:

• Pure helpers (probe query, identifier quoting, safe-to-wrap predicate, wrapped-query builder) in a new PostgreSQLPluginDriver+Spatial.swift as enum PostGISSpatialRewrite.
• probePostgisOids() runs in PostgreSQLPluginDriver.connect() right after probeCatalogPresence() and pushes a [UInt32: String] map through LibPQDriverCore into LibPQPluginConnection.
• Spatial check happens inside fetchResults where column OIDs are still in scope; on a match and a safe-to-wrap query the wrapped SELECT ST_AsEWKT(...) FROM (<original>) AS _tp_rewrite is re-executed and its rows replace the original. Original geometry / geography is preserved in columnTypeNames so a future edit-back PR has the same signal without needing a PluginKit ABI bump.
• Falls back to raw hex (no error) for: non-SELECT / WITH / VALUES shapes, top-level ; outside string literals / identifiers / line and block comments / dollar-quoted strings, duplicate output column names, and queries that could re-execute side effects (INSERT / UPDATE / DELETE / MERGE / TRUNCATE / COPY keywords, or nextval / setval calls — the last set added in response to a P1 from codex's review on the PR).
• No TableProPluginKit change, no currentPluginKitVersion bump. Array / raster / box / edit-back / streaming path / client-side EWKB decoder all left as follow-ups.

60+ pure unit tests covering the predicate stack. Verified end-to-end against PostgreSQL 18 + PostGIS for geometry(Point), geometry(Polygon), geography(Point), NULL, and POINT EMPTY; screenshots in the PR body. Happy to iterate on naming, file layout, or anything else from the review.