sql - 如何在过程中使用临时表，Postgresql

Question

我有一个临时表

   CREATE TEMPORARY TABLE temp_location
   (
      city VARCHAR(30),
      street VARCHAR(30)
   ) 
   ON COMMIT DELETE ROWS;

我想在我的程序中使用这个表

CREATE OR REPLACE FUNCTION fexample(
    pcity character varying)
  RETURNS void AS
$BODY$
DECLARE 
BEGIN

    select * from temp_location where city = pcity;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

此方法在 oracle 中有效，但在 postgresql 中无效

Answer 1

你可以使用:

CREATE OR REPLACE FUNCTION fexample(pcity VARCHAR(30))
  RETURNS TABLE(c VARCHAR(30),a VARCHAR(30)) AS
$$
    select * from temp_location where city = pcity;
$$
  LANGUAGE sql;

DBFiddle Demo