Views that call functions are not ordered after those functions

[da77a]

Views that call functions are not ordered after those functions

pg-schema-diff version

v1.0.5

Problem

A view definition can call a function:

CREATE FUNCTION fn_compute(id integer) RETURNS TABLE(result text) ...;
CREATE VIEW v_computed AS SELECT * FROM fn_compute(42);

pg-schema-diff does not track this dependency. The view may be created before the function, causing:

ERROR: function fn_compute(integer) does not exist (SQLSTATE 42883)

Root cause

The view vertex generator (view_sql_generator.go) only wires dependencies on tables (via TableDependencies). It has no awareness of function dependencies.

PostgreSQL does track view→function dependencies in pg_depend (via the view's rewrite rule), so the information is available:

SELECT pg_proc.proname, proc_ns.nspname,
       pg_catalog.pg_get_function_identity_arguments(pg_proc.oid)
FROM pg_catalog.pg_depend AS d
JOIN pg_catalog.pg_rewrite AS r ON d.objid = r.oid AND r.ev_class = <view_oid>
JOIN pg_catalog.pg_depend AS d2 ON r.oid = d2.objid
JOIN pg_catalog.pg_proc ON d2.refobjid = pg_proc.oid AND d2.refclassid = 'pg_proc'::REGCLASS
JOIN pg_catalog.pg_namespace AS proc_ns ON pg_proc.pronamespace = proc_ns.oid
WHERE d.refobjid = <view_oid> AND d2.deptype = 'n'
  AND proc_ns.nspname NOT IN ('pg_catalog', 'information_schema');

Suggested fix

1. Add a function_dependencies column to GetViews (similar query to above)
2. Add DependsOnFunctions []SchemaQualifiedName to the View struct
3. In view_sql_generator.go Add(), wire: mustRun(addVertexId).after(buildFunctionVertexId(f, diffTypeAddAlter))

Reproducer

CREATE FUNCTION public.fn_helper() RETURNS TABLE(x int) LANGUAGE sql AS $$ SELECT 1; $$;
CREATE VIEW public.v_uses_fn AS SELECT * FROM public.fn_helper();

Plan from empty → target: view is ordered before function → apply fails.

Related

• [BUG] Functions with knowable dependencies are created before the tables they depend on #248 (function dependency ordering)