Invocation of PL/pgSQL functions from the subqueries of derived tables

Copy SQL

WITH plpgsql AS (SELECT 
  pg_proc.oid,
  pg_namespace.nspname AS specific_schema, 
  pg_proc.proname AS routine_name, 
  pg_proc.proname || '_' || pg_proc.oid AS specific_name,
  pg_get_function_identity_arguments(pg_proc.oid) AS parameters,
  CASE WHEN pg_proc.prokind='f' THEN 'FUNCTION'
  WHEN pg_proc.prokind='p' THEN 'PROCEDURE'
  WHEN pg_proc.prokind='w' THEN 'WINDOW FUNCTION' END AS routine_type,
 regexp_replace(pg_get_functiondef(pg_proc.oid),'[\r\n]','<br>','g')  AS routine_src
FROM 
  pg_catalog.pg_proc, 
  pg_catalog.pg_namespace,
  pg_catalog.pg_language
WHERE 
  pg_proc.pronamespace = pg_namespace.oid 
  AND pg_proc.prolang = pg_language.oid  
  AND pg_proc.prokind<>'a' 
  AND pg_language.lanname='plpgsql'    
  AND pg_namespace.nspname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT table_schema AS schema, 
table_name AS view, 
'VIEW' AS type, 
regexp_replace(view_definition,'[\r\n]','<br>','g') AS view_definition, 
specific_schema AS routine_schema, 
routine_name, 
parameters, 
routine_type, 
routine_src
FROM Information_schema.views INNER JOIN Information_schema.view_routine_usage USING (table_schema, table_name)
INNER JOIN plpgsql USING (specific_schema, specific_name)
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT schemaname, 
matviewname, 
'MATERIALIZED VIEW' AS type, 
regexp_replace(definition,'[\r\n]','<br>','g') AS definition, 
specific_schema, 
routine_name,
pg_get_function_identity_arguments(oid) AS parameters, 
routine_type,
routine_src
FROM pg_catalog.pg_matviews, plpgsql
WHERE definition LIKE '% ' || routine_name || '(%'
ORDER BY schema, view;