返回PostgreSQL函数中已经存在的表

Question

我有一堆返回同一表模式的函数，因此我必须在这些函数声明之间一遍又一遍地重复同一表模式，以使此示例简单，例如，我们有两个函数返回相同的表格架：

<强>表：People

CREATE TABLE people(full_name TEXT, age integer);

函数：

CREATE OR REPLACE FUNCTION get_people_by_age(_age integer)
RETURNS TABLE(full_name TEXT, age integer)
LANGUAGE PLPGSQL
AS
$$
BEGIN 
    RETURN QUERY SELECT * FROM people  WHERE people.age = $1;
END
$$

CREATE OR REPLACE FUNCTION get_people_by_name(_full_name text)
RETURNS TABLE(full_name TEXT, age integer)
LANGUAGE PLPGSQL
AS
$$
BEGIN 
    RETURN QUERY SELECT * FROM people  WHERE people.full_name = $1;
END
$$

是否有办法参考函数声明中的现有表？我想像这样的事情：

CREATE OR REPLACE FUNCTION get_people_by_age(_age integer)
RETURNS TABLE(people)
LANGUAGE PLPGSQL
AS
$$
BEGIN 
    RETURN QUERY SELECT * FROM people  WHERE people.age = $1;
END
$$

CREATE OR REPLACE FUNCTION get_people_by_name(_full_name text)
RETURNS TABLE(people)
LANGUAGE PLPGSQL
AS
$$
BEGIN 
    RETURN QUERY SELECT * FROM people  WHERE people.full_name = $1;
END
$$

在我所指的每个函数中都没有在已经存在的表中声明相同的架构，那是可能的吗？

Answer 1

使用返回setof

CREATE OR REPLACE FUNCTION get_people_by_name(_full_name text)
RETURNS setof people
LANGUAGE PLPGSQL
AS
$
BEGIN 
    RETURN QUERY SELECT * FROM people  WHERE people.full_name = _full_name;
END
$;

或作为SQL函数更简单：

CREATE OR REPLACE FUNCTION get_people_by_name(_full_name text)
RETURNS setof people
LANGUAGE sql
AS
$
  SELECT * FROM people  WHERE people.full_name = _full_name;
$;