postgresql - 将 PostgreSQL 查询存储到变量中

Question

我正在实现搜索功能，它将为过滤功能提供结果。

是否可以将中间执行结果存储到变量中并根据提供的函数参数从中选择？

我目前坚持这个实现：

CREATE FUNCTION public.search_companies4(
    head_country_id bigint DEFAULT NULL::bigint,
    name character varying DEFAULT NULL::character varying,
    "categoryIds" integer[] DEFAULT NULL::integer[],
    "cityIds" integer[] DEFAULT NULL::integer[],
    departments public.department[] DEFAULT NULL::public.department[],
    "stockTemperatures" public.stock_temperature[] DEFAULT NULL::public.stock_temperature[],
    verified boolean DEFAULT NULL::boolean,
    with_comments boolean DEFAULT NULL::boolean
)
RETURNS SETOF public.company
LANGUAGE plpgsql STABLE
AS $$
    declare
        "companies" public.company;
    begin
      if ("search_companies4"."cityIds" is not null) then
          select into "companies" from "companies" AS c
          left join "companyCities" cC ON c.id = cC."companyId"
          where "search_companies4"."cityIds" @> array[cC."cityId"];
      end if;

      if ("search_companies4"."categoryIds" is not null) then
          select into "companies" from "companies" AS c
          left join "companyCategories" cCat ON c.id = cCat."companyId"
          where (
              "search_companies4"."categoryIds" @> array[cCat."categoryId"]
          ) AND (
              "search_companies4"."departments" is null OR
              "search_companies4"."departments" @> array[cCat.department]
          );
      end if;

      if ("search_companies4"."name" is not null) then
          select into "companies" from "companies" AS c
          where c.name ILIKE "search_companies4"."name";
      end if;

      if ("search_companies4"."head_country_id" is not null) then
          select into "companies" from "companies" AS c
          where c.head_country_id = "search_companies4"."head_country_id";
      end if;

      if ("search_companies4"."stockTemperatures" is not null) then
          select into "companies" from "companies" AS c
          where c."stockTemperatures" && "search_companies4"."stockTemperatures";
      end if;

      if ("search_companies4"."verified" is not null) then
          select into "companies" from "companies" AS c
          left join company_import ci ON c.id = ci.company_id
          where (
              CASE WHEN "search_companies4"."verified" = true THEN
              -- Созданные вручную и проверенные импортированные
              ci.company_id is null OR ci.verified_at is not null
              ELSE true END -- И созданные вручную и все импортированные
          );
      end if;

      if ("search_companies4"."with_comments" is not null) then
          select into "companies" from "companies" AS c
          where (
              CASE WHEN "search_companies4"."with_comments" = true THEN
              -- Только компании с комментариями
              c."lastCommentAt" IS NOT NULL
              ELSE true END
          );
      end if;

      return query select from "companies" order by id;
  end;
$$;

它不起作用，因为 PostgreSQL 抱怨companies变量：

ERROR:  relation "companies" does not exist at character 30
QUERY:  select                  from "companies" AS c
left join "companyCategories" cCat ON c.id = cCat."companyId"
where (
    "search_companies4"."categoryIds" @> array[cCat."categoryId"]
) AND (
    "search_companies4"."departments" is null OR
    "search_companies4"."departments" @> array[cCat.department]
)

如何使用这个变量来存储查询，可以逐步与select语句一起使用？

或者，有没有更好的实现方式……</p>

标签： postgresql