pgtle.sh: support marker-based code substitution for install-method-dependent SQL

[jnasbyupgrade]

pgtle.sh generates pg_tle registration SQL from an extension's normal SQL files, so the extension can be deployed to environments without filesystem access (e.g. AWS RDS/Aurora) by registering into pg_tle's database-backed catalog instead of installing files to disk. Some extension code legitimately needs to behave differently depending on which path was used — e.g. reporting how it was installed. There is no reliable way to detect this at runtime: PostgreSQL doesn't expose a catalog flag for "did this extension's control file come from disk or pg_tle", and filesystem-probing functions like pg_stat_file require superuser/pg_read_server_files, which conflicts with pg_tle's whole point of letting less-privileged roles install extensions. The only point where this can be decided reliably is at pgtle.sh generation time.

Proposal

Support marker-based code substitution in an extension's raw SQL source, scanned and applied only when pgtle.sh generates the pg_tle registration SQL (never during a normal filesystem make install). For example:

CREATE FUNCTION install_method(
) RETURNS text LANGUAGE sql AS $$
-- PGTLE: Start replace
SELECT 'native'
-- PGTLE: Replace with
SELECT 'pgtle'
-- PGTLE: End replace
$$;

For the normal filesystem install, the file is used as-is (code between "Start replace" and "Replace with" is what ships). When pgtle.sh builds the pg_tle registration SQL, it would instead substitute in the code between "Replace with" and "End replace", dropping all three markers and the native-path code.

Validation (design precedent)

pgtle.sh already does an analogous "fail loudly if malformed" pass before wrapping each SQL file: validate_delimiter/wrap_sql_content check the file doesn't contain the reserved $_pgtle_wrap_delimiter_$ wrapping delimiter and die with a clear error if it does. The marker-scanning pass for this feature should follow the same pattern — e.g. an unmatched or unclosed marker set (a "Start replace" with no matching "Replace with"/"End replace", or vice versa) must be a hard error during generation, not silently ignored or silently left as literal comments in the output.

Not the same as cat_tools' -- SED: markers

cat_tools (a pgxntool-based extension) has its own, unrelated -- SED: PRIOR TO 9.5! / -- SED: REQUIRES 9.3! marker convention, implemented in cat_tools' own sql.mk via a safesed helper, for stripping/keeping code based on the target PostgreSQL major version. That's a different tool solving a different problem (PG-version conditionals at cat_tools' own build layer) and isn't affected by or related to this proposal beyond both being "marker in a SQL comment" conventions — worth a mention here only so the two aren't conflated.

If general SQL preprocessing lands first

I looked for an existing pgxntool issue about switching to a general "raw input files that get processed" pipeline (a preprocessing/templating mechanism broader than this one feature), since Jim recalled discussing something like that. I searched open and closed issues for terms like preprocess, template, raw input, generated files, pipeline, convention, standardize, boilerplate, substitution, and .sql.in, and also skimmed README.asc/CLAUDE.md for hints. I didn't find a confident match — the closest related issues are #43 ("Method for 'including' common code in build and version scripts") and #1 ("Support .control.in files"), but neither is actually a general preprocessing/templating pipeline proposal, so I'm not linking either as "the" issue.

If such a general preprocessing pipeline does get built (whether under a new issue or as part of #43), this feature could simplify substantially: instead of the three-marker block syntax above, the SQL source would just contain a single always-substituted placeholder token, e.g.:

SELECT '@pgxntool install method@'

and the general pipeline would replace the token with the actual install method (native or pgtle) during generation — no block-marker start/replace/end syntax needed at all. Worth keeping in mind if/when that broader mechanism is scoped, to avoid building two overlapping substitution mechanisms.

Related

Related to (but distinct from) #55, which is about a make install-skip override flag for testing — same investigation, different feature.