union all by name postgres

query.sql

-- This is a PL/pgSQL block for dynamically combining two tables from potentially different schemas.
-- It generates and executes a SQL query that correctly handles differing data types by casting all columns to TEXT.

DO $$
DECLARE
    --------------------------------------------------------------------------
    -- Step 1: Configuration
    -- Specify the schema and table names for the two tables to be combined.
    --------------------------------------------------------------------------
    _schema1_name TEXT := 'schema_a';
    _table1_name  TEXT := 'table_a';
    _schema2_name TEXT := 'schema_b';
    _table2_name  TEXT := 'table_b';

    --------------------------------------------------------------------------
    -- Internal Script Variables
    -- Each variable must be declared only once within this section.
    --------------------------------------------------------------------------
    _select_list1 TEXT;
    _select_list2 TEXT;
    _final_query  TEXT;

BEGIN
    -- Step 2: Build the column lists for each SELECT statement.
    -- This version casts every column to TEXT to ensure the UNION operation never fails due to type mismatches.
    WITH all_unique_columns AS (
        -- First, get a single list of all unique column names from both tables.
        -- Using DISTINCT is simpler and sufficient here.
        SELECT DISTINCT column_name
        FROM information_schema.columns
        WHERE (table_schema = _schema1_name AND table_name = _table1_name)
           OR (table_schema = _schema2_name AND table_name = _table2_name)
    )
    SELECT
        -- Build the SELECT list for the first table (schema_a.table_a)
        string_agg(
            CASE
                -- If the column exists in table 1, cast it to TEXT.
                WHEN t1.column_name IS NOT NULL THEN 'CAST("' || all_cols.column_name || '" AS TEXT) AS "' || all_cols.column_name || '"'
                -- If not, select a NULL value that is also cast to TEXT.
                ELSE 'CAST(NULL AS TEXT) AS "' || all_cols.column_name || '"'
            END,
            ', ' ORDER BY all_cols.column_name
        ),

        -- Build the SELECT list for the second table (schema_b.table_b)
        string_agg(
            CASE
                -- If the column exists in table 2, cast it to TEXT.
                WHEN t2.column_name IS NOT NULL THEN 'CAST("' || all_cols.column_name || '" AS TEXT) AS "' || all_cols.column_name || '"'
                -- If not, select a NULL value cast to TEXT.
                ELSE 'CAST(NULL AS TEXT) AS "' || all_cols.column_name || '"'
            END,
            ', ' ORDER BY all_cols.column_name
        )
    INTO
        -- Store the generated comma-separated lists into our variables
        _select_list1,
        _select_list2
    FROM
        all_unique_columns all_cols
        -- Use LEFT JOINs to check for the existence of each column in each table.
        LEFT JOIN (SELECT column_name FROM information_schema.columns WHERE table_schema = _schema1_name AND table_name = _table1_name) AS t1
            ON all_cols.column_name = t1.column_name
        LEFT JOIN (SELECT column_name FROM information_schema.columns WHERE table_schema = _schema2_name AND table_name = _table2_name) AS t2
            ON all_cols.column_name = t2.column_name;


    -- Step 3: Construct the final query string, qualifying table names with their schemas.
    _final_query := 'SELECT ' || _select_list1 || ' FROM ' || quote_ident(_schema1_name) || '.' || quote_ident(_table1_name) ||
                    ' UNION ALL ' ||
                    'SELECT ' || _select_list2 || ' FROM ' || quote_ident(_schema2_name) || '.' || quote_ident(_table2_name);

    -- Optional: Display the generated query for debugging or verification.
    RAISE NOTICE 'Generated Query: %', _final_query;

    -- Step 4: Execute the dynamic query and store the results in a temporary view.
    -- The view is named 'combined_view' and is automatically dropped when your session ends.
    EXECUTE 'CREATE OR REPLACE TEMP VIEW combined_view AS ' || _final_query;

END $$;

-- Step 5: Get your final results.
-- After running the DO block above, you can query the temporary view.
SELECT * FROM combined_view;