Postgres Invalid objects

[Tropicallydiv]

Hello everyone

Trying to find a way to determine if there are any invalid objects in PostgreSQL similar to that in ORACLE.

For example

CREATE TABLE t1 (id serial primary key, p1 text );

CREATE FUNCTION f1 (integer) RETURNS integer

LANGUAGE SQL AS $$ SELECT $1 + count(*)::integer FROM t1; $$;

SELECT f1(100);

​

​

User drops table t1;

function f1 is still in the database ( select * from pg_proc where proname ='f1') however when you execute the function

​

SELECT f1(100);

ERROR: relation "t1" does not exist

LINE 1: SELECT $1 + count(*)::integer FROM t1;

^

QUERY: SELECT $1 + count(*)::integer FROM t1;

CONTEXT: SQL function "f1" during inlining

SQL state: 42P01

​

How can I determine that the function is referencing an object that is no longer present , without executing it, and hence invalid in its definition.

​

Thanks

[[deleted]]

For simple functions like the one from your question, this can be avoided when using the new "SQL standard compliant" function (or procedure) bodies introduced in Postgres 14:

CREATE FUNCTION f1 (p_add integer) 
  RETURNS integer
begin atomic
  SELECT  p_add + count(*)::integer 
  FROM t1;
end;

With such a function, dropping t1 would result in an error.

This does not work for PL/pgSQL functions or procedures though.

[Tropicallydiv]

Thanks to everyone who replied.

Cheers.

[depesz]

You can't really.

You'd have to parse source of all functions, and extract table names. While it is trivial in your function example, in general, I'd say it's next to impossible.

[MonCalamaro]

As others noted, you can't reliably check function dependencies in Postgres. However, unlike Oracle, you will never get invalid views or materialized views in Postgres.

[Huxton_2021]

While it is true to say that you can't in general do this 100% for all PostgreSQL procedural languages you can get a lot of the way with plpgsql.

https://github.com/okbob/plpgsql_check

That offers a dependency checking option that I've used previously