postgresql - 使用模式名称变量创建 'insert into' 函数

Question

我正在尝试创建一个函数来将数据插入表中。我正在使用的查询不会改变，除了模式名称需要是变量。例如，我的架构名称之一是 bscu.members，还有 35 个非常相似的架构名称(wea.members、pcu.members 等)。我找不到任何有关如何使用变量在 Postgresql 中创建函数的帮助。

这是我到目前为止想出的，但它不起作用

create or replace function attsummary(varchar)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
 BEGIN
insert into dwh.attribution_summary
select
m.source,
m.name,
m.partner_id,
d.ucic,
b.acct_type_desc as acct_desc,
a.begin_mo_balance as opening_balance,
c.date,
h.campaignname,
g.description as banner_desc,
f.create_time::timestamp as time_served,
'd' as dep_or_loan,
'h' as home_or_nonhome  
   from
$1.fact_deposits a
join $1.dim_acct_type b on a.acct_type_id = b.acct_type_id
join $1.dim_date c on a.date_id = c.date_id
join $1.dim_members d on a.ucic = d.ucic
join ad_delivery.sgmt_adic e on d.adic::varchar = e.adic
join ad_delivery.sgmt_user_tracker f on e.cookie_id = f.id
join ad_delivery.ox_banners g on g.bannerid = f.banner_id
join ad_delivery.ox_campaigns h on h.campaignid = f.campaign_id
join ad_delivery.sgmt_kli_adic i on e.adic = i.adic
join dwh.sgmt_clients m on m.partner_id = i.sgmt_partner_id
   where
  i.kli=8616208
   and m.partner_id::integer != 909909

然后是我的 select 语句。我使用 $1 作为模式名称通常所在的变量。

Answer 1

必须动态生成

create or replace function attsummary
(schema text)
returns void as
$body$
begin
execute format('
insert into dwh.attribution_summary
select
    m.source,
    m.name,
    m.partner_id,
    d.ucic,
    b.acct_type_desc as acct_desc,
    a.begin_mo_balance as opening_balance,
    c.date,
    h.campaignname,
    g.description as banner_desc,
    f.create_time::timestamp as time_served,
    ''d'' as dep_or_loan,
    ''h'' as home_or_nonhome  
from
%1$s.fact_deposits a
join %1$s.dim_acct_type b on a.acct_type_id = b.acct_type_id
join %1$s.dim_date c on a.date_id = c.date_id
join %1$s.dim_members d on a.ucic = d.ucic
join ad_delivery.sgmt_adic e on d.adic::varchar = e.adic
join ad_delivery.sgmt_user_tracker f on e.cookie_id = f.id
join ad_delivery.ox_banners g on g.bannerid = f.banner_id
join ad_delivery.ox_campaigns h on h.campaignid = f.campaign_id
join ad_delivery.sgmt_kli_adic i on e.adic = i.adic
join dwh.sgmt_clients m on m.partner_id = i.sgmt_partner_id
where
    i.kli=8616208
    and m.partner_id::integer != 909909
', $1);
end;
$body$
language plpgsql volatile
;