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

Question

这个函数…

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');

之后，我得到:

ERROR: relation "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$;

Answer 1

默认情况下它们保留在会话中。提交时保留行-默认情况下。如果你想在提交后删除表，你应该使用on commit drop来创建它。