Writing a Postgresql Stored Procedure Returning Resultset

programming, snippet

Coming from Microsoft SQL Server, I keep on forgetting how to return a resultset from a stored procedure in postgresql. Here is a small sample of how to do it. Needs a bit more code than SQL Server.

Note that postgresql does not have stored procedure, they have function. They are equivalent.

Say we have a table called person with the following structure

---------------------------
column| datatype
---------------------------
 _id  | autonumber
 name | text
 age  | integer
---------------------------

We want to write a stored procedure to return records with age greater than a specified age.

CREATE FUNCTION get_person(_age integer)
  RETURNS TABLE(name character varying, age integer)
    LANGUAGE sql
    AS $$
    Select name, age from person where age > _age;
$$;

Other postgresql articles