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