Postgres返回UpSert模棱两可

Question

我正在使用wath with block编写一个upsert函数以进行更新，然后有条件地执行插入。然后，我想从整个功能中返回PK，以及是否完成了插入或更新。我认为这会起作用，但是我遇到了返回语句的歧义错误。我想念什么？

CREATE OR REPLACE FUNCTION eai.upsert_manufacturer(
    p_user_tag_ident text
    , p_manuf_trade_name text
    , p_company_name text
    , p_manuf_code bigint default null
    , p_mf_db_site text default '0000045000000100'
    , p_mf_db_id bigint default 1
    , p_phys_addr text default null
    , p_phys_city_name text default null
    , p_phys_state_abbr text default null
    , p_phys_postal_code text default null
    , p_phys_country_abbr text default null
    , p_rstat_type_code smallint default 1
)
 RETURNS TABLE(manuf_code bigint, mf_db_site text, mf_db_id bigint, action text)
 LANGUAGE plpgsql
AS $function$
declare
begin
    return query
    with update_outcome as (
        update eai.manufacturer as m
            set
                user_tag_ident = p_user_tag_ident::text
                , manuf_trade_name = p_manuf_trade_name::text
                , company_name = p_company_name::text
                , phys_addr = p_phys_addr::text
                , phys_city_name = p_phys_city_name::text
                , phys_state_abbr = p_phys_state_abbr::text
                , phys_postal_code = p_phys_postal_code::text
                , phys_country_abbr = p_phys_country_abbr::text
                , rstat_type_code = p_rstat_type_code::smallint
                , gmt_last_updated = now()
        where (m.manuf_code = p_manuf_code::bigint
            and m.mf_db_site = p_mf_db_site::text
            and m.mf_db_id = p_mf_db_id::bigint)
            /* Since this table is unique on user_tag_ident */
            or m.user_tag_ident = p_user_tag_ident
        returning manuf_code, mf_db_site, mf_db_id, 'update'::text as action
    )
    , insert_outcome as (
        insert into eai.manufacturer(
            user_tag_ident
            , manuf_trade_name
            , company_name
            , manuf_code
            , mf_db_site
            , mf_db_id
            , phys_addr
            , phys_city_name
            , phys_state_abbr
            , phys_postal_code
            , phys_country_abbr
            , rstat_type_code
            , gmt_last_updated
        )
        select
            p_user_tag_ident::text
            , p_manuf_trade_name::text
            , p_company_name::text
            , coalesce(p_manuf_code::bigint, (select max(mf.manuf_code)+1 from eai.manufacturer mf where mf.mf_db_site = p_mf_db_site and mf.mf_db_id = p_mf_db_id))::bigint as manuf_code
            , p_mf_db_site::text
            , p_mf_db_id::int
            , p_phys_addr::text
            , p_phys_city_name::text
            , p_phys_state_abbr::text
            , p_phys_postal_code::text
            , p_phys_country_abbr::text
            , p_rstat_type_code::smallint
            , now() as now
        where not exists (select 1 from update_outcome u)
        returning manuf_code, mf_db_site, mf_db_id, 'insert'::text as action
    )
    select 
        x.manuf_code
        , x.mf_db_site
        , x.mf_db_id
        , x.action
    from (
        SELECT
            manuf_code
            , mf_db_site
            , mf_db_id
            , action
        FROM update_outcome u
        UNION ALL SELECT
            manuf_code
            , mf_db_site
            , mf_db_id
            , action
        FROM insert_outcome i
    ) x;
end;
$function$;

执行：

select * from eai.upsert_manufacturer(
    p_user_tag_ident:='Sent'
    , p_manuf_trade_name:='Sent'
    , p_company_name:='Sent'
    , p_phys_addr:= '672'
    , p_phys_city_name:= 'Blargh'
);

和错误：

Kernel error: ERROR:  column reference "manuf_code" is ambiguous
LINE 19:         returning manuf_code, mf_db_site, mf_db_id, 'update'...
                           ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

Answer 1

您必须在那里manuf_code。第一个来自update_outcome，第二个来自insert部分。