Returning Tables from PostgreSQL Functions in SQL and PL/pgSQL

PostgreSQL Functions make more demands when you want to return tabular data than, say, SQL Server Stored Procedures.  In SQL Server you can get away with

CREATE PROCEDURE [dbo].[users_get]
    SELECT * from users

Hell, you can even omit the “RETURN” if you want.  Not so in PostgreSQL, but we’ll look at how to return the same results in PostgreSQL using both SQL and PL/pgSQL.

Here’s our user table.

Our logic for the two functions is the same. We want to return all users with an ID greater than our input parameter, which will be “1”. Here’s our function written in SQL.

Notice the RETURNS SETOF “public”.”jangle_user”. That’s because in PostgreSQL we’re returning a set of a Composite Type, which represents the structure of rows or records. We’ll call the function with

select * from jangle_users_get_sql(1);

and bam! Tabular data!

That’s great but we want to work it in PL/pgSQL.

Notice again that we’re returning a SETOF the jangle_users composite type, but in PL/pgSQL we use a RETURN QUERY SELECT to return the table.

select * from jangle_users_get_plpgsql(1);

returns the identical result.

One more thing to do is use a SELECT * FROM FUNCTION() rather than SELECT FUNCTION(); as we typically do for scalar data results.

select jangle_users_get_plpgsql(1);