 $$
 LANGUAGE plpgsql STRICT;
 
+
+/*****************************
+* 3. Main delete
+*
+* statements are not closed because more columns might be returned
+*****************************/
+-- ARRAY CASE
+
+-- creates code block to delete entries
+CREATE OR REPLACE FUNCTION citydb_pkg.generate_delete_by_ids_stmt(
+  table_name TEXT
+  ) RETURNS TEXT AS 
+$$
+SELECT  '  DELETE FROM'
+  || E'\n      '||$1||' t'
+  || E'\n    USING'
+  || E'\n      unnest($1) a(a_id)'
+  || E'\n    WHERE'
+  || E'\n      t.id = a.a_id'
+  || E'\n    RETURNING'
+  || E'\n      id';
+$$
+LANGUAGE sql STRICT;
+
+-- SINGLE CASE
+
+-- creates code block to delete entry
+CREATE OR REPLACE FUNCTION citydb_pkg.generate_delete_by_id_stmt(
+  table_name TEXT
+  ) RETURNS TEXT AS 
+$$
+SELECT
+     E'\n  DELETE FROM'
+  || E'\n    '||$1
+  || E'\n  WHERE'
+  || E'\n    id = $1'
+  || E'\n  RETURNING'
+  || E'\n    id';
+$$
+LANGUAGE sql STRICT;
+
+
+/*****************************
+* 4. FKs in table
+*****************************/
+-- SYSTEM QUERIES
+CREATE OR REPLACE FUNCTION citydb_pkg.query_ref_to_fk(
+  table_name TEXT,
+  schema_name TEXT DEFAULT 'citydb',
+  OUT ref_table TEXT,
+  OUT ref_column TEXT,
+  OUT fk_columns TEXT,
+  OUT concat_id_arrays TEXT,
+  OUT column_count INTEGER,
+  OUT cleanup_ref_table BOOLEAN
+  ) RETURNS SETOF RECORD AS
+$$
+SELECT
+  ref_table_oid::regclass::text AS ref_table,
+  ref_column::text,
+  fk_columns,
+  concat_id_arrays,
+  column_count::int,
+  (cleanup_ref_table IS NOT NULL) AS cleanup_ref_table
+FROM (
+  SELECT
+    c.confrelid AS ref_table_oid,
+    a_ref.attname AS ref_column,
+    string_agg(a.attname, E',\n      ' ORDER BY a.attnum) AS fk_columns,
+    string_agg('array_agg('||a.attname||')', E' ||\n    ' ORDER BY a.attnum) AS concat_id_arrays,
+    count(a.attname) AS column_count,
+    citydb_pkg.check_for_cleanup(c.confrelid) AS cleanup_ref_table
+  FROM
+    pg_constraint c
+  JOIN
+    pg_attribute a
+    ON a.attrelid = c.conrelid
+   AND a.attnum = ANY (c.conkey)
+  JOIN
+    pg_attribute a_ref
+    ON a_ref.attrelid = c.confrelid
+   AND a_ref.attnum = ANY (c.confkey)
+  WHERE
+    c.conrelid = ($2 || '.' || $1)::regclass::oid
+    AND c.conrelid <> c.confrelid
+    AND c.contype = 'f'
+    AND c.confdeltype = 'n'
+    AND (c.confrelid::regclass::text NOT LIKE '%surface_geometry'
+     OR c.conrelid::regclass::text LIKE '%implicit_geometry'
+     OR c.conrelid::regclass::text LIKE '%cityobject_genericattrib'
+     OR c.conrelid::regclass::text LIKE '%cityobjectgroup')
+    AND NOT a.attnotnull
+  GROUP BY
+    c.confrelid,
+    a_ref.attname
+) fk;
+$$
+LANGUAGE sql STRICT;
+
+
+-- ARRAY CASE
+
 -- creates code block to delete referenced entries by foreign keys
-CREATE OR REPLACE FUNCTION citydb_pkg.delete_fkeys_by_id(
+CREATE OR REPLACE FUNCTION citydb_pkg.create_ref_to_array_delete(
+  table_name TEXT,
+  schema_name TEXT DEFAULT 'citydb',
+  OUT vars TEXT,
+  OUT returning_block TEXT,
+  OUT collect_block TEXT,
+  OUT into_block TEXT,
+  OUT fk_block TEXT
+  ) RETURNS RECORD AS
+$$
+DECLARE
+  rec RECORD;
+BEGIN
+  FOR rec IN (
+    SELECT * FROM citydb_pkg.query_ref_to_fk($1, $2)
+  )
+  LOOP
+    IF vars IS NULL THEN
+      vars := '';
+      returning_block := '';
+      collect_block := '';
+      into_block := '';
+      fk_block := '';
+    END IF;
+
+    vars := vars || E'\n  '||citydb_pkg.get_short_name(rec.ref_table)||'_ids int[] := ''{}'';';
+    returning_block := returning_block || E',\n      ' || rec.fk_columns;
+    collect_block := collect_block || E',\n    ' || rec.concat_id_arrays;
+    into_block := into_block || E',\n    '||citydb_pkg.get_short_name(rec.ref_table)||'_ids';
+
+    IF rec.cleanup_ref_table THEN
+      -- function call required, so create function first
+      PERFORM citydb_pkg.create_array_delete_function(rec.ref_table, $2);
+      fk_block := fk_block || citydb_pkg.generate_delete_m_ref_by_id_call(rec.ref_table, rec.ref_column, rec.ref_table);
+    ELSE
+      fk_block := fk_block || citydb_pkg.generate_delete_m_ref_by_id_stmt(rec.ref_table, rec.ref_column, rec.ref_table);
+    END IF;
+  END LOOP;
+
+  RETURN;
+END;
+$$
+LANGUAGE plpgsql STRICT;
+
+
+-- SINGLE CASE
+
+-- creates code block to delete referenced entries by foreign keys
+CREATE OR REPLACE FUNCTION citydb_pkg.create_ref_to_delete(
   table_name TEXT,
   schema_name TEXT DEFAULT 'citydb',
   OUT vars TEXT,

$$
LANGUAGE plpgsql STRICT;

-- creates code block to delete referenced entries by foreign keys
CREATE OR REPLACE FUNCTION citydb_pkg.delete_fkeys_by_ids(
  table_name TEXT,
  schema_name TEXT DEFAULT 'citydb',
  OUT vars TEXT,
  OUT returning_block TEXT,
  OUT collect_block TEXT,
  OUT into_block TEXT,
  OUT fk_block TEXT
  ) RETURNS RECORD AS
$$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN (
    SELECT
      fk.fk_table_oid::regclass::text AS fk_table,
      fk.fk_table_oid::regclass::text AS fk_table_short,
      fk.fk_column,
      fk.ref_columns,
      fk.concat_id_arrays,
      rf.has_ref
    FROM (
      SELECT
        c.confrelid AS fk_table_oid,
        a_ref.attname AS fk_column,
        string_agg(a.attname, E',\n      ' ORDER BY a.attnum) AS ref_columns,
        string_agg('array_agg('||a.attname||')', E' ||\n    ' ORDER BY a.attnum) AS concat_id_arrays
      FROM
        pg_constraint c
      JOIN
        pg_attribute a
        ON a.attrelid = c.conrelid
       AND a.attnum = ANY (c.conkey)
      JOIN
        pg_attribute a_ref
        ON a_ref.attrelid = c.confrelid
       AND a_ref.attnum = ANY (c.confkey)
       WHERE
         c.conrelid = ($2 || '.' || $1)::regclass::oid
         AND c.conrelid <> c.confrelid
         AND c.contype = 'f'
         AND c.confdeltype = 'n'
         AND (c.confrelid::regclass::text NOT LIKE '%surface_geometry'
          OR c.conrelid::regclass::text LIKE '%implicit_geometry'
          OR c.conrelid::regclass::text LIKE '%cityobject_genericattrib'
          OR c.conrelid::regclass::text LIKE '%cityobjectgroup')
         AND NOT a.attnotnull
       GROUP BY
         c.confrelid,
         a_ref.attname
    ) fk
    LEFT JOIN LATERAL (
      SELECT
        TRUE AS has_ref
      FROM
        pg_constraint
      WHERE
        conrelid = fk.fk_table_oid
        AND confrelid <> fk.fk_table_oid
        AND contype = 'f'
        AND (confdeltype = 'a' OR confdeltype = 'n')
    ) rf ON (true)
  )
  LOOP
    IF vars IS NULL THEN
      vars := '';
      returning_block := '';
      collect_block := '';
      into_block := '';
      fk_block := '';
    END IF;

    vars := vars || E'\n  '||rec.fk_table||'_ids int[] := ''{}'';';
    returning_block := returning_block || E',\n      ' || rec.ref_columns;
    collect_block := collect_block || E',\n    ' || rec.concat_id_arrays;
    into_block := into_block || E',\n    '||rec.fk_table||'_ids';

    IF rec.has_ref THEN
      -- function call required, so create function first
      PERFORM citydb_pkg.create_array_delete_function(rec.fk_table, rec.fk_table_short, $2);
      fk_block := fk_block || citydb_pkg.delete_m_table_by_ids(rec.fk_table, rec.fk_table_short, rec.fk_column, rec.fk_table);
    ELSE
      fk_block := fk_block || citydb_pkg.delete_m_table_by_ids(rec.fk_table, rec.fk_column, rec.fk_table);
    END IF;
  END LOOP;

  RETURN;
END;
$$
LANGUAGE plpgsql STRICT;


-- creates code block to delete referenced parent entries
CREATE OR REPLACE FUNCTION citydb_pkg.delete_parent_by_ids(
  table_name TEXT,
  schema_name TEXT DEFAULT 'citydb'
  ) RETURNS TEXT AS
$$
DECLARE
  rec RECORD;
  parent_block TEXT := '';
BEGIN
  FOR rec IN (
    SELECT 
      f.confrelid::regclass::text AS parent_table,
      f.confrelid::regclass::text AS parent_table_short
    FROM
      pg_constraint f,
      pg_constraint p
    WHERE
      f.conrelid = ($2 || '.' || $1)::regclass::oid
      AND p.conrelid = ($2 || '.' || $1)::regclass::oid
      AND f.conkey = p.conkey
      AND f.contype = 'f'
      AND p.contype = 'p'
      AND f.confdeltype = 'a'
  )
  LOOP
    -- create array delete function for parent table
    PERFORM citydb_pkg.create_array_delete_function(rec.parent_table, rec.parent_table_short, $2);

    -- add delete call for parent table
    parent_block := parent_block
      || E'\n  -- delete '||rec.parent_table
      || E'\n  PERFORM gen.delete_'||rec.parent_table_short||E'(deleted_ids);\n';
  END LOOP;

  RETURN COALESCE(parent_block,'');
END;
$$
LANGUAGE plpgsql;


/***********************
* DELETE BY ID
***********************/
-- creates code block to delete referenced entries in same table
CREATE OR REPLACE FUNCTION citydb_pkg.delete_self_ref_by_id(
  table_name TEXT,
  table_short_name TEXT,
  schema_name TEXT DEFAULT 'citydb'
  ) RETURNS TEXT AS
$$
DECLARE
  self_block TEXT;
BEGIN
  SELECT string_agg(
    E'\n  --delete referenced parts'
    || E'\n  PERFORM'
    || E'\n    gen.delete_'||$2||'(array_agg(id))'
    || E'\n  FROM'
    || E'\n    '||$1
    || E'\n  WHERE'
    || E'\n    ' ||a.attname|| ' = $1'
    || E'\n    AND id != $1;\n', '')
  INTO
    self_block
  FROM
    pg_constraint c
  JOIN
    pg_attribute a
    ON a.attrelid = c.conrelid
   AND a.attnum = ANY (c.conkey)
  WHERE
    c.conrelid = ($3 || '.' || $1)::regclass::oid
    AND c.conrelid = c.confrelid
    AND c.contype = 'f'
    AND c.confdeltype = 'a'
    AND a.attnotnull = FALSE;

  IF self_block IS NOT NULL THEN
    -- create a array delete function
    PERFORM citydb_pkg.create_array_delete_function($1, $2, $3);
  END IF;

  RETURN COALESCE(self_block,'');
END;
$$
LANGUAGE plpgsql;

-- creates code block to delete referenced entries in n table
CREATE OR REPLACE FUNCTION citydb_pkg.delete_n_table_by_id(
  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'

  rec RECORD;
BEGIN
  FOR rec IN (
    SELECT * FROM citydb_pkg.query_ref_fk($1, $2)
  )
  LOOP
    IF vars IS NULL THEN
