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 )

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 😢.

Please Like, Comment, and Subscribe to my channel. ❤

Follow me on Social Media
--------------------------------------------------

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.

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.

id, name)
VALUES (1, 'Akram');

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.

)
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
END;
$BODY$;

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.

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.

[KnowledgeChannel]

*Please help me get that beautiful YouTube Silver button. Do subscribe to the channel if my video was helpful.* ❤

[KnowledgeChannel]

Please Like, Comment, and Subscribe to my channel.

[pepepriest]

Perfect men, i was stuck at the record part and could loop through the result in python...

[veronica.gimenez]

My dude you're a fucking blessing. I'd been looking for explanations to turn queries into functions easily and i couldn't find anything until this video. Understood perfectly 5 mins. Thank you so much

[MADAHAKO]

Thank you! I am from Russia and India is our friend!

[praveencharan]

HI Akram, very informative and detailed! thanks for the video, though i have couple of questions, will be grateful if you could answer them
1) what if we have to return a select statement based on many joins and not a simple one as you mentioned above,
2)i tired to return by mentioning all the columns in RETURNS TABLE(), but there is one column with type enum, and i am unable to define that
how can we handle this situation, hope you understood my question.
Thanks

[abhishrivastava]

Hi sir,
I have a function X which performs insert and delete operation and this function X I am want to execute from another function Y. Please provide the detail video how to do that

[mohammedibrahim]

Hi, can you plz explain how to use a store procedure in postgressql version 12

[antonybabu]

I wnat return json format of all record can you pls.tell me about that function

[ricardoperez]

I must to say that the best developer are from India

[RaviRajVerma-oqkt]

what is the difference b/w SETOF and table return in postgresql please reply...

[narayanan]

Hi Bro, Why we are using the alter function command,