Function body table/view references not tracked for dependency ordering

[da77a]

Function body table/view references not tracked for dependency ordering

pg-schema-diff version

v1.0.5

Problem

Functions that reference tables or views in their body (via FROM/JOIN clauses, or %ROWTYPE/%TYPE declarations) have no dependency edge to those objects. This causes functions to be ordered before the tables/views they reference:

ERROR: relation "myschema.mytable" does not exist (SQLSTATE 42P01)

This affects:

• LANGUAGE sql functions — body is opaque text ($function$...$function$), PostgreSQL does not record table deps in pg_depend
• LANGUAGE plpgsql functions — same, plus %ROWTYPE and type[] declarations in DECLARE blocks are resolved at runtime, not tracked in pg_depend

The HAS_UNTRACKABLE_DEPENDENCIES hazard is emitted for plpgsql functions, but no ordering attempt is made even when the information is available in the function definition text.

Patterns not tracked

1. Schema-qualified FROM/JOIN: FROM schema.table_name or JOIN schema.view_name
2. %ROWTYPE declarations: DECLARE r schema.view_name%ROWTYPE
3. Array-of-composite declarations: DECLARE batch schema.view_name[]

All of these are visible in pg_get_functiondef() output.

Suggested fix

Something better than this - which I'm using as "good enough for now":

Supplementing pg_depend-based detection with text-based parsing of the function definition. Regex patterns:

(?i)\b(\w+)\.(\w+)(?:%ROWTYPE|\[\])     -- schema.name%ROWTYPE or schema.name[]
(?i)(?:FROM|JOIN)\s+(\w+)\.(\w+)\b      -- FROM/JOIN schema.name

Matches are added as TableDependencies on the function, which the existing vertex generator already wires correctly.

Reproducer

CREATE TABLE public.orders (id int PRIMARY KEY, total numeric);
CREATE VIEW public.order_summary AS SELECT id, total FROM public.orders WHERE total > 0;

CREATE FUNCTION public.process() RETURNS void LANGUAGE plpgsql AS $$
DECLARE r order_summary%ROWTYPE;
BEGIN
  FOR r IN SELECT * FROM order_summary LOOP NULL; END LOOP;
END; $$;

Plan from empty → target: function is ordered before view → apply fails with type "order_summary" does not exist.

Related

• [BUG] Functions with knowable dependencies are created before the tables they depend on #248 (composite type deps — covers RETURNS type but not body references)
• PR Account relation dependencies used by routines as composite types #268 (attempted fix for [BUG] Functions with knowable dependencies are created before the tables they depend on #248, added pg_depend-based detection but doesn't cover body text)