0

Is there any other way to use a table name as a function parameter in PostgreSQL than with Execute?

The problem is the existing statement (taken from a report in openMaint) is long and has a lot of existing variables.

I want to be able to make a general function that can be used for any similar report where all I need to do is pass the different table parameters instead of making a new function for each report. However things will get exceedingly complicated using Execute. Does anyone have any alternatives for me?

CREATE TEMP TABLE "ReportData" AS 
SELECT  lang,
    coalesce((SELECT "Description" FROM "Site" WHERE "Id" = site_id), '')::varchar site_descr,
    coalesce((SELECT "Description" FROM "Division" WHERE "Id" = divis_id), '')::varchar div_descr,
    coalesce(cons."Description", '')::varchar consb_descr,
    coalesce(cat."Code", '')::varchar cat_code,
    coalesce(_cm3_translation_lookup_get(cat."Id", lang), '')::varchar cat_descr,
    coalesce(_cm3_translation_lookup_get(scat."Id", lang), '')::varchar scat_descr,
    coalesce(ssite."Description", '')::varchar srcsite_descr,
    coalesce(sdiv."Description", '')::varchar srcdiv_descr,
    coalesce(dsite."Description", '')::varchar dstsite_descr,
    coalesce(ddiv."Description", '')::varchar dstdiv_descr,
    coalesce(to_char(wrmov."Date", 'DD TMMon YYYY'), '')::varchar wm_date,
    coalesce(wmrow."Quantity", 0.00)::numeric wmr_quantity,
    (CASE WHEN site_id IS null THEN
        CASE cat."Code"
            WHEN 'Load' THEN '+1'
            WHEN 'Relocation' THEN '0'
            WHEN 'Unload' THEN '-1'
            ELSE null
        END
    ELSE
        CASE    WHEN (wrmov."SrcSite" IS null OR wrmov."SrcSite" != ALL(sites_set)) AND wrmov."DstSite" = ANY(sites_set) THEN '+1'
            WHEN wrmov."SrcSite" = ANY(sites_set) AND wrmov."DstSite" = ANY(sites_set) THEN '0'
            WHEN wrmov."SrcSite" = ANY(sites_set) AND (wrmov."DstSite" IS null OR wrmov."DstSite" != ALL(sites_set)) THEN '-1'
            ELSE null
        END
    END)::varchar qt_symbol
FROM "GAWrhMovement" wrmov
LEFT JOIN "LookUp" cat ON cat."Id" = wrmov."Category"
LEFT JOIN "LookUp" scat ON scat."Id" = wrmov."Subcategory"
LEFT JOIN "Site" ssite ON ssite."Id" = wrmov."SrcSite"
LEFT JOIN "Division" sdiv ON sdiv."Id" = wrmov."SrcDivision"
LEFT JOIN "Site" dsite ON dsite."Id" = wrmov."DstSite"
LEFT JOIN "Division" ddiv ON ddiv."Id" = wrmov."DstDivision"
JOIN "GAWrhMovementRow" wmrow ON wmrow."WrhMovement" = wrmov."Id" AND wmrow."Status" = 'A'
LEFT JOIN "GAConsumable" cons ON cons."Id" = wmrow."Consumable"
WHERE CASE WHEN site_id IS null THEN true ELSE (wrmov."SrcSite" = ANY(sites_set) OR wrmov."DstSite" = ANY(sites_set)) END 
AND CASE WHEN divis_id IS null THEN true ELSE (wrmov."SrcDivision" = ANY(divis_set) OR wrmov."DstDivision" = ANY(divis_set)) END 
AND wrmov."Status" = 'A'
ORDER BY consb_descr, wrmov."Date";
1
  • If you want a dynamic table name, you need dynamic SQL (and thus PL/pgSQL and execute() Commented Mar 22, 2021 at 15:11

1 Answer 1

0

No, there is no other way than to use dynamic SQL with EXECUTE.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.