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.

[@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.

[@monicabn3067]

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

[@SoftwareNuggets]

Thank you

[@flyingzeppo]

Very useful and informative. Thank you.

[@SoftwareNuggets]

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

[@ahmednour2839]

Very informative video, thank you!

[@SoftwareNuggets]

You're very welcome!

[@jingosf]

Excellent stuff! Very helpful and useful tutorial.

[@SoftwareNuggets]

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

[@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) kzbin.info/www/bejne/bYaUY2ybp5iAj7M --table valued function kzbin.info/www/bejne/b5K7dWlvraepg9U --insert stored procedure kzbin.info/www/bejne/oIDciputrbJ6psk --cursor in a function kzbin.info/www/bejne/pWXFnatsfs9oiZo --how to write a scalar function kzbin.info/www/bejne/aHXXgXiOn9OrqZY --return a rowtype kzbin.info/www/bejne/noDIZJWflNOIj6M --convert ip address to big int kzbin.info/www/bejne/iYfPiKCggpmmjc0

[@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.

[@MikeItsm]

Thank you! Very useful vidio!

[@SoftwareNuggets]

Glad it was helpful!

[@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.

[@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.

[@abdulrehmanjaved-rt8jq]

Thanks

[@SoftwareNuggets]

Your welcome

[@ISHANCHANDRAKAR-iz2nh]

thanks it worked

[@SoftwareNuggets]

Which solution helped you?

[@ISHANCHANDRAKAR-iz2nh]

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

[@humayunhimu6045]

wow! very useful!

[@SoftwareNuggets]

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

[@manojkumargobidesi1575]

Thank you

[@SoftwareNuggets]

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

[@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. kzbin.info/www/bejne/b5K7dWlvraepg9U PostgreSQL Function to Convert an IP Address into a Big Int, and from a BIG Int to an IP Address. kzbin.info/www/bejne/iYfPiKCggpmmjc0 PostgreSQL - Use Function to Generate XML from table data using XMLForest, XMLElement and XLMAgg. kzbin.info/www/bejne/pHSmYoKvj9hghqc PostgreSQL - Write a Function that uses RETURNS SETOF json. kzbin.info/www/bejne/hHK4i4pjlM2Zbas PostgreSql Enum: A Beginner's guide to Implement and Use ENUMS in a function. kzbin.info/www/bejne/lXOQlKegbN-DpNE PostgreSQL - Create Function, use rowtype and return json object kzbin.info/www/bejne/noDIZJWflNOIj6M PostgreSQL: A table-valued function to Split Full Names into individual fields. kzbin.info/www/bejne/fWiap5J8e9Rsb6M PostgreSQL: Scalar Function. How to write and use a SQL Scalar function. kzbin.info/www/bejne/aHXXgXiOn9OrqZY

[@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.

[@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: kzbin.info/www/bejne/pWXFnatsfs9oiZo

[@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: kzbin.info/www/bejne/aHXXgXiOn9OrqZY

[@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.

[@ledinhanhtan]

🙏🙏🙏

[@SoftwareNuggets]

Thanks

[@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?

[@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.