postgresql - plpgsql 返回一行或另一行

Question

我想在一个函数中以两种不同的方式查找数据，并在找到结果后立即返回。

首先我想运行一个查询；

select * from company where company.id = x

然后，如果没有返回结果，请尝试这样的查询

select company.*
  from
    company
  join
    company_alias on company.id = company_alias.company_id
  where
    company_alias.company_alias_id = x;

目前我正在使用union all

create or replace function get_payer(x int) returns company as $$
  select * from company where company.id = x
  union all
  select company.*
  from
    company
  join
    company_alias on company.id = company_alias.company_id
  where
    company_alias.company_alias_id = x;
$$ language sql stable
set search_path from current;

这似乎效率不高，因为我总是运行两个查询。但我不确定如何在 plpgsql 函数中构造一个条件来处理这个问题。

我尝试了以下变体，但没有任何运气

create or replace function payment_claim_payer(x int) returns company as $$
declare found_company company;
begin

  select * from company where company.id = x into found_company;

  if not exists found_company then
    select
      company.*
    from
      company
    join
      company_alias on company.id = company_alias.company_id
    where
      company_alias.company_alias_id = x into found_company;
  end if;

  return found_company;

end;
$$ language plpgsql stable
set search_path from current;

Answer 1

您的最后一次尝试几乎成功。您需要一个 plpgsql(不是 sql)函数并且应该检查特殊变量 found :

create or replace function payment_claim_payer(x int) 
returns company language plpgsql as $$
declare found_company company;
begin
    select * 
    from company 
    where company.id = x 
    into found_company;

    if not found then
        select company.*
        from company
        join company_alias on company.id = company_alias.company_id
        where company_alias.company_alias_id = x 
        into found_company;
    end if;

    return found_company;
end;
$$;