Re: postgres 9.6: insert into select finishes only in pgadmin not psql - Mailing list pgsql-general

Of course.  This was added to pull some data from legacy db tables into something we could query while converting the process that populates the legacy db. These are rather long.  I'll spare you the details of the other tables it joins as they are basically id <=> name pairs for the most part.

This isn't meant to run often, but it does need to be run every time the legacy db data is pulled over.

Import Function:

CREATE OR REPLACE FUNCTION import_wss()
RETURNS void AS $$
    INSERT INTO wss_entries (
        is_historical,
        historical_path,
        wss_import_row_index,
        service_date,
        original_sid,
        client_id,
        client_site_id,
        material_group_id,
        material_category_id,
        material_id,
        material_outcome_id,
        targeted,
        goal_percent,
        weight_pounds,
        old_vendor_name,
        new_vendor_name,
        vendor_id,
        vendor_site_id,
        old_service_type,
        new_service_type,
        old_quantity,
        old_size,
        old_frequency,
        old_price,
        old_market_index,
        old_service_per_month,
        old_units_per_haul,
        old_avg_monthly_cost,
        new_quantity,
        new_size,
        new_frequency,
        new_price,
        new_market_index,
        new_service_per_month,
        new_units_per_haul,
        new_avg_monthly_cost,
        is_haul,
        haul_unit_id,
        service_conversion_id,
        num_hauls_per_weight,
        compaction_ratio,
        unit_weight,
        full_percent,
        benchmark_hauling_cost,
        total_monthly_cost,
        gross_savings,
        month_of_shared_savings,
        wr_fees,
        net_savings,
        new_account_number,
        contract_expiration,
        scheduled_service_days
    ) SELECT
        true,
        w."Path",
        w."RowNum",
        w."WSSDate"::date,
        CASE
            WHEN client_sites.id IS null THEN TRIM(w."SID")
            ELSE null
        END,
        client_sites.client_id,
        client_sites.id,
        material_groups.id,
        material_categories.id,
        materials.id,
        material_outcomes.id,
        w."Targeted" = 'True',
        w."Goal Percentage",
        w."Total Pounds",
        NULLIF(w."Old Vendor ", ''),
        NULLIF(w."New Vendor", ''),
        vendor_sites.vendor_id,
        vendor_sites.id,
        NULLIF(w."Old Service Description", ''),
        NULLIF(w."New Service Description", ''),
        NULLIF(w."Old Quan", ''),
        NULLIF(w."Old Size", ''),
        NULLIF(w."Old Freq", ''),
        CASE
            WHEN w."Old Price " ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Old Price "::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Old Market Index" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Old Market Index"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Old Service Per Month" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Old Service Per Month"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Old Tons Per Haul" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Old Tons Per Haul"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Old Monthly Cost" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Old Monthly Cost"::float
            ELSE 0::float
        END,
        NULLIF(w."New Quan", ''),
        NULLIF(w."New Size", ''),
        NULLIF(w."New Freq", ''),
        CASE
            WHEN w."New Price " ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."New Price "::float
            ELSE 0::float
        END,
        CASE
            WHEN w."New Market Index" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."New Market Index"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."New Service Per Month" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."New Service Per Month"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."New Tons Per haul" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."New Tons Per haul"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."New Monthly Cost" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."New Monthly Cost"::float
            ELSE 0::float
        END,
        r."LineType" = 'Haul',
        haul_units.id,
        service_conversions.id,
        CASE
            WHEN w."hauls coorelating to this weight" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."hauls coorelating to this weight"::float
            ELSE 0::float
        END,
        NULLIF(w."Estimated Compaction Ratio", ''),
        CASE
            WHEN w."Unit weight assigned to waste" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Unit weight assigned to waste"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Pct Full based on last survey" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Pct Full based on last survey"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Benchmark Costs" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Benchmark Costs"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Total Monthly Costs" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Total Monthly Costs"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Gross Savings" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Gross Savings"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Month of Shared Savings" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Month of Shared Savings"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."WR Fees" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."WR Fees"::float
            ELSE 0::float
        END,
        CASE
            WHEN w."Net Savings" ~ '^[0-9\-]+[0-9\.\-E]*$' THEN w."Net Savings"::float
            ELSE 0::float
        END,
        NULLIF(w."New Account Number", ''),
        NULLIF(w."Current Contract Expiration Date", ''),
        NULLIF(w."Current Scheduled Service Days", '')
    FROM wss."WSSData" w
    LEFT OUTER JOIN wss."RowCoding" r ON r."Path" = w."Path" AND
r."RowNum" = w."RowNum" AND r."WSSDate" = w."WSSDate"
    LEFT OUTER JOIN client_sites ON client_sites.original_sid = TRIM(w."SID")
    LEFT OUTER JOIN material_groups ON material_groups.name = w."Material Group"
    LEFT OUTER JOIN material_categories ON material_categories.name = w."Material Category"
        AND material_categories.material_group_id = material_groups.id
    LEFT OUTER JOIN materials ON materials.name = w."Material Description"
        AND materials.material_category_id = material_categories.id
LEFT OUTER JOIN material_outcomes ON material_outcomes.name = w."Material Outcome"
    LEFT OUTER JOIN haul_units ON haul_units.name = w."New Units Description"
    LEFT OUTER JOIN service_conversions ON service_conversions.name = w."Conversion Code"
    LEFT OUTER JOIN vendor_sites ON vendor_sites.id = w."vid"
    WHERE w."SID" IS NOT null;

$$ LANGUAGE SQL;

Source Table:

CREATE TABLE wss."WSSData"