 -- returns deleted IDs (set of int)
 CREATE OR REPLACE FUNCTION citydb_pkg.create_array_delete_function(
   table_name TEXT,
-  table_short_name TEXT,
   schema_name TEXT DEFAULT 'citydb'
   ) RETURNS SETOF VOID AS 
 $$
 DECLARE
-  ddl_command TEXT := 'CREATE OR REPLACE FUNCTION gen.delete_' ||$2|| E'(int[]) RETURNS SETOF int AS\n$body$';
+  ddl_command TEXT := 'CREATE OR REPLACE FUNCTION gen.delete_' ||citydb_pkg.get_short_name($1)|| E'(int[]) RETURNS SETOF int AS\n$body$';
   declare_block TEXT := E'\nDECLARE\n  deleted_ids int[] := ''{}'';';
   pre_block TEXT := '';
   post_block TEXT := '';
   delete_agg_start TEXT := E'\n  WITH delete_objects AS (\n  ';
-  delete_block TEXT := E'  DELETE FROM\n      '||$1||E' t\n    USING\n      unnest($1) a(a_id)\n    WHERE\n      t.id = a.a_id\n    RETURNING\n      id';
+  delete_block TEXT := '';
   delete_agg_end TEXT := E'\n  )\n  SELECT\n    array_agg(id)';
   return_block TEXT := E'\n  RETURN QUERY\n    SELECT unnest(deleted_ids);';
 BEGIN
-  -- SELF-REFERENCES
-  pre_block := citydb_pkg.delete_self_ref_by_ids($1, $2, $3);
+  -- SELF REFERENCES
+  pre_block := citydb_pkg.create_selfref_array_delete($1, $2);
 
   -- REFERENCING TABLES
   SELECT

  rec RECORD;
BEGIN
  FOR rec IN (
    SELECT
      n_table::regclass::text,
      n_table::regclass::text AS n_table_short,
      fk_n_column_name,
      ref_depth,
      (clean_n_parent IS NOT NULL) AS cleanup_n_table,
      m_table::regclass::text,
      m_table::regclass::text AS m_table_short,
      fk_m_column_name,
      (clean_m_parent IS NOT NULL) AS cleanup_m_table
    FROM (
      SELECT
        c.confrelid AS root_table,
        c.conrelid AS n_table,
        a.attname AS fk_n_column_name,
        n.ref_depth,
        citydb_pkg.do_cleanup(c.conrelid) AS clean_n_parent,
        m.m_table,
        m.fk_m_column_name,
        citydb_pkg.do_cleanup(m_table) AS clean_m_parent
      FROM
        pg_constraint c
      JOIN
        pg_attribute a
        ON a.attrelid = c.conrelid
       AND a.attnum = ANY (c.conkey)
      LEFT JOIN (
        -- count references of referencing tables
        -- > 1 ref = extra delete function
        WITH RECURSIVE ref_table_depth(parent_table, ref_table, depth) AS (
          SELECT
            confrelid AS parent_table,
            conrelid AS ref_table,
            1 AS depth
          FROM
            pg_constraint
          WHERE
            confrelid = ($2 || '.' || $1)::regclass::oid
            AND conrelid <> confrelid
            AND contype = 'f'
            AND confdeltype = 'a'
          UNION ALL
            SELECT
              r.confrelid AS parent_table,
              r.conrelid AS ref_table,
              d.depth + 1 AS depth
            FROM
              pg_constraint r,
              ref_table_depth d
            WHERE
              d.ref_table = r.confrelid
              AND d.ref_table <> r.conrelid
              AND r.contype = 'f'
              AND r.confdeltype = 'a'
        )
        SELECT
          parent_table,
          max(depth) AS ref_depth
        FROM
          ref_table_depth
        GROUP BY
          parent_table
        ) n
        ON n.parent_table = c.conrelid
      -- get n:m tables which are the NULL candidates from the n block
      -- the FK has to be confdeltype = 'c' to decide for cleanup
      -- also count references and check for cleanup to find out if it needs its own delete function
      LEFT JOIN LATERAL (
        SELECT
          mn.confrelid AS m_table,
          mna.attname AS fk_m_column_name
        FROM
          pg_constraint mn
        JOIN
          pg_attribute mna
          ON mna.attrelid = mn.conrelid
         AND mna.attnum = ANY (mn.conkey)
        WHERE
          mn.conrelid = c.conrelid
          AND n.parent_table IS NULL
          AND mn.confrelid <> c.conrelid
          AND mn.contype = 'f'
          AND mn.confdeltype = 'c'
      ) m ON (true)
      WHERE
        c.confrelid = ($2 || '.' || $1)::regclass::oid
        AND c.conrelid <> c.confrelid
        AND c.contype = 'f'
        AND c.confdeltype = 'a'
    ) pre
    WHERE
      (clean_n_parent IS NULL OR clean_n_parent <> root_table)
      AND (clean_m_parent IS NULL OR clean_m_parent <> root_table)
    ORDER BY
      ref_depth DESC NULLS FIRST,
      n_table,
      m_table
  )
  LOOP
    IF vars IS NULL THEN

$$
LANGUAGE plpgsql STRICT;


-- SINGLE CASE

-- creates code block to delete referenced entries in n table
CREATE OR REPLACE FUNCTION citydb_pkg.generate_delete_ref_by_id_stmt(
  table_name TEXT,
  fk_column_name TEXT
  ) RETURNS TEXT AS 
$$
SELECT
     E'\n  -- delete '||$1||'s'
  || E'\n  DELETE FROM'
  || E'\n    '||$1
  || E'\n  WHERE'
