json - PostgreSQL 函数工作一次然后给出错误

问题描述

这个功能...

CREATE OR REPLACE FUNCTION public.find_locations(
    search_text character varying,
    record_offset integer DEFAULT 0,
    fetch_quantity integer DEFAULT 20,
    sort_by character varying DEFAULT 'name',
    direction character varying DEFAULT 'ASC')
    RETURNS json
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
  CREATE TEMP TABLE locations_found AS
  SELECT
    locations._id AS id, locations.name, locations.address, locations.city, locations.state, locations."zipCode", locations.phone,
    locations.map, locations.directions, locations.review, locations.parking, locations.active
  FROM locations
  WHERE search_text IS NULL OR search_text = '' OR LOWER(locations.name) LIKE LOWER(search_text) || '%'
  ORDER BY
    CASE 
      WHEN direction = 'ASC' THEN
        CASE
          WHEN sort_by = 'name' THEN locations.name
          WHEN sort_by = 'address' THEN locations.address
          WHEN sort_by = 'active' THEN locations.active::varchar
        END
    END ASC,
    CASE
      WHEN direction = 'DESC' THEN
        CASE
          WHEN sort_by = 'name' THEN locations.name
          WHEN sort_by = 'address' THEN locations.address
          WHEN sort_by = 'active' THEN locations.active::varchar
        END
    END DESC;

  RETURN  
   json_build_object(
      'count',(SELECT COUNT(*) FROM locations_found),
      'offset', record_offset,
      'limit', fetch_quantity,
      'results', (SELECT json_agg(locations_found.*) FROM locations_found OFFSET record_offset LIMIT fetch_quantity)
    );
END;
$BODY$;

我第一次调用它时效果很好。例如：

SELECT * FROM find_locations('North',0,2,'name','ASC');

之后，我得到：

错误：关系“locations_found”已经存在

最初，我什至没有明确删除临时表，因为我认为它会在事务执行后消失。

为什么临时表存在于第二个事务中？

更新：根据a_horse_with_no_name的指导，我更新了函数以使用公用表表达式（CTE）：

CREATE OR REPLACE FUNCTION public.find_locations(
    search_text character varying,
    record_offset integer DEFAULT 0,
    fetch_quantity integer DEFAULT 20,
    sort_by character varying DEFAULT 'name'::character varying,
    direction character varying DEFAULT 'ASC'::character varying)
    RETURNS json
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
  response json;
BEGIN
  WITH locations_found AS (
    SELECT
      locations._id AS id, locations.name, locations.address, locations.city, locations.state, locations."zipCode", locations.phone,
      locations.map, locations.directions, locations.review, locations.parking, locations.active
    FROM locations
    WHERE search_text IS NULL OR search_text = '' OR LOWER(locations.name) LIKE LOWER(search_text) || '%'
    ORDER BY
      CASE 
        WHEN direction = 'ASC' THEN
          CASE
            WHEN sort_by = 'name' THEN locations.name
            WHEN sort_by = 'address' THEN locations.address
            WHEN sort_by = 'active' THEN locations.active::varchar
          END
      END ASC,
      CASE
        WHEN direction = 'DESC' THEN
          CASE
            WHEN sort_by = 'name' THEN locations.name
            WHEN sort_by = 'address' THEN locations.address
            WHEN sort_by = 'active' THEN locations.active::varchar
          END
      END DESC
  )
  SELECT json_build_object(
      'count',(SELECT COUNT(*) FROM locations_found),
      'offset', record_offset,
      'limit', fetch_quantity,
      'results', (SELECT json_agg(locations_found.*) FROM locations_found OFFSET record_offset LIMIT fetch_quantity)
    ) INTO response;
    RETURN response;
END;
$BODY$;

标签： jsonpostgresqlfunctiontemp-tables

json - PostgreSQL 函数工作一次然后给出错误

问题描述

解决方案

推荐阅读