How To Return A Table From A Function In PostgreSQL || PostgreSQL Tips & Tricks 2020 Knowledge 360

#knowledge360 #akramsohail #akramsohailproject
You Are Hearty Welcomed To My Channel Knowledge 360.
Here I post technical videos, mainly related to computer science and programming.
I am posting project videos with coding explanations. I make tutorial videos on Technical Topics.
Stay Connected, Stay Tuned, Study Smart.

– Knowledge 360 ( Akram Sohail )

Follow me on Social Media
————————————————–
Facebook – https://www.facebook.com/sonu.babu.5872682/
Instagram – https://www.instagram.com/akkubakku007/
google+ – https://plus.google.com/u/0/116728183002228924227
Blog – https://knowledge360blog.blogspot.in/

Topics
———
Oracle
MySQL
PostgreSQL
SQL Server
PHP
C
C++
Python
Java
JavaScript
HTML/CSS
jQuery
Ajax
Bootstrap
Angular
Linux
Ubuntu
Windows
Miscellaneous

Description
——————

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 below, which gives better tabular form output.

SELECT * from get_data();

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