Copy SQL

WITH routines AS (
  SELECT
    n.nspname AS routine_schema,
    p.proname AS routine_name,
    pg_get_function_identity_arguments(p.oid) AS parameters,
    CASE p.prokind
      WHEN 'f' THEN 'FUNCTION'
      WHEN 'p' THEN 'PROCEDURE'
      WHEN 'w' THEN 'WINDOW FUNCTION'
    END AS routine_type,
    pg_get_functiondef(p.oid) AS src
  FROM pg_catalog.pg_proc p
  JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
  WHERE p.prokind <> 'a'
    AND p.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match')
    AND n.nspname NOT IN (
      SELECT schema_name
      FROM information_schema.schemata
      WHERE schema_name <> 'public'
        AND schema_owner = 'postgres'
        AND schema_name IS NOT NULL
    )
    AND pg_get_functiondef(p.oid) !~* '\bxmin\b'
),
cte_names AS (
  SELECT
    r.*,
    COALESCE(array_agg(lower(m[1])) FILTER (WHERE m[1] IS NOT NULL), ARRAY[]::text[]) AS ctes
  FROM routines r
  LEFT JOIN LATERAL regexp_matches(
    r.src,
    '\b(?:WITH|,)\s*([a-z_][a-z0-9_]*)\s+AS\b',
    'gi'
  ) AS m ON TRUE
  GROUP BY r.routine_schema, r.routine_name, r.parameters, r.routine_type, r.src
),
hits AS (
  SELECT
    c.*,
    h[1] AS subselect_text,
    lower(h[2]) AS from_name
  FROM cte_names c
  JOIN LATERAL regexp_matches(
    c.src,
    '\(\s*(SELECT\b[^)]*\bFROM\s+([a-z_][a-z0-9_]*)(?:\s+[a-z_][a-z0-9_]*)?[^)]*)\)',
    'gi'
  ) AS h ON TRUE
)
SELECT DISTINCT
  routine_schema,
  routine_name,
  parameters,
  routine_type,
  regexp_replace(
    regexp_replace(
      regexp_replace(
        regexp_replace(src,'<','<','g'),
      '[\r\n]','
','g'),
    'SELECT','SELECT','gi'),
  'FOR[[:space:]]+(SHARE|UPDATE|NO[[:space:]]+KEY[[:space:]]+UPDATE|KEY[[:space:]]+SHARE)',
  'FOR \1',
  'gi'
  ) AS routine_src
FROM hits
WHERE
  -- ära raporteeri, kui subquery loeb CTE nime pealt
  from_name <> ALL (ctes)
  -- raporteeri, kui subquery sees puudub ridade lukustamine
  AND subselect_text !~* '\bFOR\s+(SHARE|UPDATE|NO\s+KEY\s+UPDATE|KEY\s+SHARE)\b'
ORDER BY routine_schema, routine_name, parameters;