Schema

WITH
  schemas AS (
    -- Select schemas to include (excluding system schemas)
    SELECT
      oid,
      nspname
    FROM
      pg_catalog.pg_namespace
    WHERE
      nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
      AND nspname NOT LIKE 'pg_temp_%%'
      AND nspname NOT LIKE 'pg_toast_temp_%%'
  ),
  tables AS (
    -- Generate CREATE TABLE statements
    SELECT
      1 AS obj_type, -- For ordering: 1=Table
      n.nspname AS schema_name,
      c.relname AS table_name,
      -- Start CREATE TABLE statement
      'CREATE TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' (' || chr(10) ||
      -- Column Definitions
      array_to_string(
        array_agg(
          '  ' || quote_ident(a.attname) || ' ' || -- Column Name
          pg_catalog.format_type(a.atttypid, a.atttypmod) || -- Data Type
          CASE
            WHEN a.attnotnull THEN ' NOT NULL'
            ELSE ''
          END || -- NOT NULL constraint
          CASE
            WHEN a.atthasdef THEN ' DEFAULT ' || pg_catalog.pg_get_expr(ad.adbin, ad.adrelid)
            ELSE ''
          END -- DEFAULT value
          ORDER BY
            a.attnum
        ),
        ',' || chr(10)
      ) ||
      -- Constraint Definitions (Primary Key, Unique, Check, Foreign Key)
      (
        SELECT
          CASE
            WHEN count(*) > 0 THEN ',' || chr(10) || array_to_string(
              array_agg('  CONSTRAINT ' || quote_ident(con.conname) || ' ' || pg_catalog.pg_get_constraintdef(con.oid, TRUE)),
              ',' || chr(10)
            )
            ELSE ''
          END
        FROM
          pg_catalog.pg_constraint con
        WHERE
          con.conrelid = c.oid -- Constraint is on this table
          AND con.contype IN ('p', 'u', 'c', 'f') -- PK, Unique, Check, FK
      ) || chr(10) || ')' || chr(59) AS ddl
    FROM
      pg_catalog.pg_class c
      JOIN schemas n ON c.relnamespace = n.oid
      LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
      LEFT JOIN pg_catalog.pg_attrdef ad ON a.attrelid = ad.adrelid
      AND a.attnum = ad.adnum
    WHERE
      c.relkind = 'r' -- 'r' = ordinary table
      AND a.attnum > 0 -- Exclude system columns
      AND NOT a.attisdropped -- Exclude dropped columns
    GROUP BY
      n.nspname,
      c.relname,
      c.oid -- Include c.oid for constraint subquery correlation
  ),
  views AS (
    -- Generate CREATE VIEW statements
    SELECT
      2 AS obj_type, -- For ordering: 2=View
      n.nspname AS schema_name,
      c.relname AS view_name,
      'CREATE OR REPLACE VIEW ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' AS' || chr(10) || pg_catalog.pg_get_viewdef(c.oid, TRUE) || chr(59) AS ddl
    FROM
      pg_catalog.pg_class c
      JOIN schemas n ON c.relnamespace = n.oid
    WHERE
      c.relkind = 'v' -- 'v' = view
  ),
  indexes AS (
    -- Generate CREATE INDEX statements (excluding indexes for constraints)
    SELECT
      3 AS obj_type, -- For ordering: 3=Index
      n.nspname AS schema_name,
      c_idx.relname AS index_name,
      pg_catalog.pg_get_indexdef(c_idx.oid) || chr(59) AS ddl
    FROM
      pg_catalog.pg_class c_idx
      JOIN schemas n ON c_idx.relnamespace = n.oid
      LEFT JOIN pg_catalog.pg_constraint con ON con.conindid = c_idx.oid
        AND con.contype IN ('p', 'u') -- Exclude indexes backing PK/Unique constraints handled by CREATE TABLE
    WHERE 
      c_idx.relkind = 'i' -- 'i' = index
      AND con.oid IS NULL -- Only include indexes not directly backing a PK or UNIQUE constraint
    )
-- Final query that returns a single text value with all DDL statements
SELECT string_agg(ddl, chr(10) || chr(10)) as schema
FROM (
  SELECT 1 as sort_order, schema_name, table_name, ddl FROM tables
  UNION ALL
  SELECT 2 as sort_order, schema_name, view_name as table_name, ddl FROM views
  UNION ALL
  SELECT 3 as sort_order, schema_name, index_name as table_name, ddl FROM indexes
  ORDER BY sort_order, schema_name, table_name
) AS combined_ddl