Return a table that already exist in postgresql function-CodePudding

I have a bunch of functions that return the same table schema, so I have to repeat the same table schema over and over between those functions' declarations, to make this example simple let's say we have two functions that return the same table schema:

Table: people

CREATE TABLE people(full_name TEXT, age integer);

Functions:

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

Is there a way to refer to the existing table within the function declarations? I imagine something like this:

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

Where instead of declaring the same schema in every function I refer to a table that already exists, is it possible?

CodePudding user response：

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

Or a bit simpler as a SQL function:

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