PostgreSQL Function works once then gives error

This function...

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$;

works great the first time I call it. For example:

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

After that, I get:

ERROR: relation "locations_found" already exists

Originally, I didn't even explicitly drop the temp table as I assumed it would go away after the transaction executes.

Why does the temp table exist on the second transaction?

UPDATE: Per guidance from a_horse_with_no_name, I updated the function to use Common Table Expressions (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$;

Solution

They stay within the session by default. On commit preserve rows - by default. If you want to drop the table after commit you should create it with on commit drop.