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";
execute()