How To Return A Table From A Function In PostgreSQL || PostgreSQL Tips

Only 3.9% of viewers are subscribing to my channel 😓.

I request you to please give click on Subscribe button.

It really helps me grow 😢.

RETURNING A TABLE FROM A FUNCTION IN POSTGRESQL

Sometimes for our needs, we may need to return a table from a user-defined function a table. In this blog, we will see how to accomplish this using the PostgreSQL database.

Before going to function and it’s implementation, let’s create a table and store several dummy records in it.
After insertion, let’s check the table values by executing the below statement.

select * from public.functiontest;

CREATE TABLE public.FunctionTest
(
id numeric,
name character varying
);

ALTER TABLE public.FunctionTest
OWNER to postgres;


We have created a table named FunctionTest having 2 columns only.

Let’s put records in it.

INSERT INTO public.functiontest(
id, name)
VALUES (1, 'Akram');

INSERT INTO public.functiontest(
id, name)
VALUES (2, 'Sohail');

The output is shown in the above snapshot.

Now suppose we want to retrieve all the data available in the table by using a user-defined function.

To do it, let’s create a function.

CREATE OR REPLACE FUNCTION public.get_data(
)
RETURNS TABLE(v_id numeric, v_name character varying)
LANGUAGE 'plpgsql'

COST 100
VOLATILE
ROWS 1000

AS $BODY$
DECLARE

BEGIN
RETURN QUERY
SELECT
id,name
FROM
public.functiontest;
END;
$BODY$;

ALTER FUNCTION public.get_data()
OWNER TO postgres;


We have created a simple function get_data() that will return us all the data present in the table functiontest.

To return a table from the function, we use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (,).

In the function, we return a query that is a result of a SELECT statement. We can see that the columns in the SELECT statement must match with the columns of the table that we want to return.


To call the function, we use the following statement.

SELECT public.get_data();


As we know, the function is returning a table, so here is the output for the called function.
We can also call the function as below, that gives better tabular form output.

SELECT * from get_data();

The above snapshot are the outcome of the function get_data().
So in this way we return a table from a function in PostgreSQL.