How to write SQL Functions in PostgreSQL

[@SoftwareNuggets]

Template Code for creating SQL Function
create or replace function func_name(fieldName datatype)
returns
as
$$
begin

end;
$$
language plpgsql;
--parameter type{in*|out|inout|VARIADIC**} *default **variable number of arguments
create or replace function func_name({parameter type} fieldName datatype)
as
$$
begin

end;
$$
language plpgsql;
if then

elsif then

else

end if;
create or replace function fn(field datatype)
returns table
(
field_name1 integer,
field_name2 character varying(60),
field_name3 varchar
)
as
$$
begin
-- table alias is mandatory, or use tablename as alias
RETURN QUERY
select alias.field1,
alias.field2,
alias.field3
from table alias
where alias.field1 = inputParameter;

end;
$$
Language plpgsql;

[@qinyu3910]

Thank you so much for sharing!

[@SoftwareNuggets]

thank you. I hope this video was helpful!

[@kvnagendra5354]

What a tutorial man, literally u r an ironman with full fledged knowledge

[@SoftwareNuggets]

Thanks the taking the time to leave this comment. Much appreciated.

[@anonymoussysadmin]

Top notch tutorial. I don't usually create functions in Postgres because I used to find it daunting. Now, there's a need to.
Declaring variables in T-SQL is so much easier.
By far the best short course I've found. Thank you for helping us out!

[@SoftwareNuggets]

Hey Sameer, glad to hear this video was helpful. I appreciate your kind words. Best of luck implementing a great solution.

[@monicabn3067]

One of the best tutorial for function which can be practiced via open source application ❤❤

[@SoftwareNuggets]

Thank you

[@senhordoobvio178]

The best tutorial about this I have seen, thanks for that

[@SoftwareNuggets]

Much appreciated

[@khizerhayyat549]

very informative and easy to understand teaching style.

[@SoftwareNuggets]

Hey @khizerhayyat549, thanks for the comment, much appreciated.

[@g13n4]

Very easy to understand and very helpful. Thank you!

[@SoftwareNuggets]

Glad to hear this video was helpful. Appreciate you leaving a comment.

[@dexvt1862]

Amazing lecture. Thanks Software Nuggets!

[@SoftwareNuggets]

Much appreciated!

[@NirojMeshram]

Very well explained. covered so many thing in such a easy way.

[@SoftwareNuggets]

Hey @NirojMeshram, much appreciated.

[@flyingzeppo]

Very useful and informative. Thank you.

[@SoftwareNuggets]

Glad to hear this video was useful. I appreciate you leaving a comment.

[@jingosf]

Excellent stuff! Very helpful and useful tutorial.

[@SoftwareNuggets]

Thanks for the comment, glad to hear this video was helpful.

[@ahmednour2839]

Very informative video, thank you!

[@SoftwareNuggets]

You're very welcome!

[@TheGladiator123]

my man is a absolutely wonderful
appreciate what you do man

[@SoftwareNuggets]

Thanks a lot @TheGladiator123, appreciate you taking the time to write a comment, and hope this video was helpful.

[@TheGladiator123]

@@SoftwareNuggets just if you can
I would like to see more implementation of This function in solving more complex/ real world problem
Also A Question:
Can I use This user defined function to create a new column in the table

[@SoftwareNuggets]

@@TheGladiator123
watch these video's I created using the function. I think you'll be impressed, and you'll become the master!
--deck of cards (great skill to learn)
ruclips.net/video/7U129er3MYQ/видео.html
--table valued function
ruclips.net/video/9aXD69xBvMs/видео.html
--insert stored procedure
ruclips.net/video/jOyYhwxMGpg/видео.html
--cursor in a function
ruclips.net/video/o4blx6Ij5S8/видео.html
--how to write a scalar function
ruclips.net/video/2DtPEXjnxs4/видео.html
--return a rowtype
ruclips.net/video/hOe3bi_nUYA/видео.html
--convert ip address to big int
ruclips.net/video/SVlWmjM4sWk/видео.html

[@SoftwareNuggets]

@@TheGladiator123 here is a function to allow you to add a column to a table.
CREATE OR REPLACE FUNCTION fn_add_column_to_table(table_name VARCHAR, column_name VARCHAR, the_data_type VARCHAR, size INT)
RETURNS VARCHAR
AS
$$
BEGIN
IF size IS NOT NULL THEN
EXECUTE format('ALTER TABLE %I ADD COLUMN %I %s(%s)', table_name, column_name, the_data_type, size);
ELSE
EXECUTE format('ALTER TABLE %I ADD COLUMN %I %s', table_name, column_name, the_data_type);
END IF;
RETURN 'Column added successfully';
END;
$$
LANGUAGE plpgsql;

[@TheGladiator123]

​@@SoftwareNuggets
hey king Just Your Opinion
I wa trying to implement what I learned From You But got Stucked At This edge Case Where I let The User pass an arraay OF words To a Colum with The Type VARCHAR(225)
I still Got errors
and I can't change The Column Defination

[@hamzasl4117]

This was very helpful Sir.

[@SoftwareNuggets]

Hey @hamzas4117, glad to hear this video was very helpful. Thanks for letting me be part of your learning journey.

[@ChrisHanks_ColonelOfTruth]

most excellent stuff. thank you for doing these.

[@SoftwareNuggets]

Glad you enjoy it! Appreciate you leaving a message as well as subscribing to my channel.

[@MikeItsm]

Thank you! Very useful vidio!

[@SoftwareNuggets]

Glad it was helpful!

[@abdulrehmanjaved-rt8jq]

Thanks

[@SoftwareNuggets]

Your welcome

[@santchev1326]

Great work, help me a lot. Thank you!

[@SoftwareNuggets]

Glad to hear that! Thanks for leaving a comment.

[@santchev1326]

@@SoftwareNuggets could you please make a video on postgres daterange type. i have some undrstanding issues

[@SoftwareNuggets]

I’ll work on it tonight, publish as soon as I finish. Thanks for a great suggestion video.

[@santchev1326]

@@SoftwareNuggets that sounds great, I am hurry to see that. I wonder how it could be tricky to use daterange instead of 2 date columns. Thanks🙏

[@SoftwareNuggets]

at 12:15, my intro to the Range Type will be available. I hope to have Part 2, done tomorrow to discuss Overlapping. Please share you comments after you view this video if you have time.

[@ISHANCHANDRAKAR-iz2nh]

thanks it worked

[@SoftwareNuggets]

Which solution helped you?

[@ISHANCHANDRAKAR-iz2nh]

@@SoftwareNuggets including function procedure under $$ for creating function

[@hasanmougharbel8030]

Hey there, god bless your efforts.
I am still learning about sql by my own and having today a simple question.
I learned that Processes (also known as “procedures”) may take values and give
out parameters.
Are processes opposite of a function?
Is there any related functions used to execute a process? Thanks..

[@hasanmougharbel8030]

@@SoftwareNuggets Hello man, very clear and to the point, but still left with a major enquiry. You said that " functions are read-only, they can only get data" and then you said that "Functions can select data, update data, delete data". Are these two statements conflicting because I learned recently that we can not use Insert/Update/Delete statements with SQL Server functions.

[@SoftwareNuggets]

Hey Hasan, i deleted all my messages, just so that I can give you ONE clear message about the difference between a Function and Stored Procedure.
The main functional difference between a function and a stored procedure is: a function returns a result, whereas a stored procedure does not.
Functions normally use the RETURNS keyword. you can return a single data type: varchar, integer, numeric, etc.
Function can also return a TABLE(see video at 16:29), we can use tables for result queries.
However, at (11:51), I write a function that DOES NOT, have a return value, but used the INOUT parameter type.
In stored procedure, they can also have return values, but limited. [If you want a QUERY RESULT] use a function.
procedure are NOT intended to return result sets.
-- other databases behave differently - when you are searching for a feature, make sure the content is about "PostgreSQL"
I hope this clears up your question.

[@SoftwareNuggets]

please watch my CURSOR video, i show how a function can be used to UPDATE/DELETE
Here is a link: ruclips.net/video/o4blx6Ij5S8/видео.html

[@LekkyDev]

Can you use postgresql functions with NodeJs?
Why are there no tutorials about how to do that?
Could you pls make a tutorial to demonstrate how to do that?

[@SoftwareNuggets]

Hey @LekkyDev, I will have a video out tonight, however, I could just upload the source to this problem to my github account earlier if you'd like. Please let me know. If no response, I'll just publish video ASAP.

[@LekkyDev]

@@SoftwareNuggets That would be great if you could upload the source
but videos are always better because you get to see the build up as well.
I really struggle to find any tutorial about plpgsql with nodejs.
Thanks for reaching back and looking forward to the source and the video.

[@SoftwareNuggets]

@LekkyDev, I will upload the video at 7pm tonight. Look forward to hearing comments from you.

[@LekkyDev]

@@SoftwareNuggets Wow thanks in advance. Its past 9pm already central euro time. Still waiting.

[@SoftwareNuggets]

I reschedule for 6pm.

[@abdallhosama9992]

Do you have more explanations about the functions in postgre

[@SoftwareNuggets]

Here is a few video that will teach you about postgresql functions: Please check out my postgresql Playlist and Postgresql Shorts:
Table-Valued Function in PostgreSQL: How to write and consume.
ruclips.net/video/9aXD69xBvMs/видео.html
PostgreSQL Function to Convert an IP Address into a Big Int, and from a BIG Int to an IP Address.
ruclips.net/video/SVlWmjM4sWk/видео.html
PostgreSQL - Use Function to Generate XML from table data using XMLForest, XMLElement and XLMAgg.
ruclips.net/video/nCC1OyZs-PE/видео.html
PostgreSQL - Write a Function that uses RETURNS SETOF json.
ruclips.net/video/NAUZW-_hf7I/видео.html
PostgreSql Enum: A Beginner's guide to Implement and Use ENUMS in a function.
ruclips.net/video/_B-ctj7zPno/видео.html
PostgreSQL - Create Function, use rowtype and return json object
ruclips.net/video/hOe3bi_nUYA/видео.html
PostgreSQL: A table-valued function to Split Full Names into individual fields.
ruclips.net/video/G77v_FFo99A/видео.html
PostgreSQL: Scalar Function. How to write and use a SQL Scalar function.
ruclips.net/video/2DtPEXjnxs4/видео.html

[@humayunhimu6045]

wow! very useful!

[@SoftwareNuggets]

Glad to hear you found this video useful. Thanks for the comment.

[@adnanberki3869]

very helpful and to the point, when dealing with tables, What if I need to save the result of the query in a variable, say the query returns only one value
I need to convert this from msAccess VBA
Holiday_start = DLookup("[Start_date]", "Public_att_holiday", "[Start_date] = #" & Format(DateCnt, "yyyy/mm/dd") & "#")
where Start_date is a field in the Public_att_holiday table which I need to compare with DateCnt and return the start_date Value from the table if exists and save to Holiday_start Variable

[@SoftwareNuggets]

Hey @adnanberki3869 , please let me know if this is what you are looking for.
create or replace function DLookup(DateCnt date)
returns date
as
$$
declare
lookupValue date;
begin
select show.date_premiered into lookupValue
from tv_show_v2 as show
where show.date_premiered = DateCnt
limit 1;
return lookupValue;
end;
$$
language plpgsql;
select DLookup('1969-10-26')

[@SoftwareNuggets]

i created a new video for scalar functions, here is a link if you'd like to check it out:
ruclips.net/video/2DtPEXjnxs4/видео.html

[@adnanberki3869]

@@SoftwareNuggets Thank you for care, yes it gave me good idea of how to fix some issues, I still have some problems since I'm new to coding in postgresql, Thank you again for your time.

[@manojkumargobidesi1575]

Thank you

[@SoftwareNuggets]

I hope this video was useful. Thanks for visiting software nuggets, watching this video and leaving a comment.

[@abdulrehmanjaved-rt8jq]

why we use inout btw, you haven't explained.

[@SoftwareNuggets]

Hey @abdulrehmanjaved-rt8jq,
Let me start of by showing you a very simple function that uses the INOUT clause:
CREATE OR REPLACE FUNCTION update_value(INOUT num_param INT)
AS
$$
BEGIN
num_param := num_param + 10; -- Increase the value by 10
END;
$$
LANGUAGE plpgsql;
-- NOTICE there is NOT a return clause on that function.
--INOUT means, you send a value into that function (the IN) part, and (the out) part is available to the caller
Here is how you call:
SELECT * FROM update_value(5) AS result;
notice the output of the call: num_param equals 15
Your question was, why do we use this.
If you want to call a function, and want to set the behavior of a column to be both input and output, then use INOUT.
CREATE TABLE IF NOT EXISTS orders (
id SERIAL PRIMARY KEY,
total DECIMAL(10, 2)
);
CREATE TABLE IF NOT EXISTS order_items (
id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(id),
price DECIMAL(10, 2),
quantity INT
);
-- Insert sample data
INSERT INTO orders (id) VALUES (1), (2);
INSERT INTO order_items (order_id, price, quantity) VALUES
(1, 10.00, 2),
(1, 15.50, 1),
(2, 5.75, 3),
(2, 20.00, 1);
DROP FUNCTION calculate_total_cost(integer)
CREATE OR REPLACE FUNCTION calculate_total_cost(inout order_id INT, OUT total_cost DECIMAL)
AS $$
BEGIN
SELECT SUM(price * quantity)
INTO total_cost
FROM order_items
WHERE order_items.order_id = calculate_total_cost.order_id;
-- Update the order with the calculated total
UPDATE orders SET total = total_cost WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM calculate_total_cost(1);
in the function calculate_total_cost, change order_id from INOUT to IN; run this sql statement: SELECT * FROM calculate_total_cost(1); compare the return columns from the first function code, to the "change INOUT to IN" code results.
Hope this helps.
Scott

[@abdulrehmanjaved-rt8jq]

@@SoftwareNuggets Thanks for great explaination, appreciated.

[@MC-Minority]

Hey so this is how the book "SQL for Data Analytics : Perform Fast and Efficient Data Analysis with the Power of SQL" teaches how to code the function equivalent.
Bro this is soooo weird.
CREATE OR REPLACE FUNCTION fn_mid(VARCHAR, INTEGER, INTEGER)
RETURNS VARCHAR AS $new_substring$
DECLARE new_substring VARCHAR;
BEGIN
RETURN SUBSTRING($1, $2, $3);
END; $new_substring$
LANGUAGE PLPGSQL;

[@SoftwareNuggets]

hey @MC-Minority,
in this statement, if you comment out --DECLARE new_substring VARCHAR. that variable is un-used.
Scott

[@SoftwareNuggets]

i just watched that video, at position 02:54 has an example close to yours. Might be helpful.

[@ArogunmatiMulikat]

I don't get it well my question did not answer,my question is out line six language in which function can be written in post Gre SQL? am a a student

[@SoftwareNuggets]

Are you asking, other than LANGUAGE plpgsql, what other languages can you use?

[@ledinhanhtan]

🙏🙏🙏

[@SoftwareNuggets]

Thanks