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;
@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.
@khizerhayyat549
3 ай бұрын
very informative and easy to understand teaching style.
@SoftwareNuggets
3 ай бұрын
Hey @khizerhayyat549, thanks for the comment, much appreciated.
@senhordoobvio178
2 ай бұрын
The best tutorial about this I have seen, thanks for that
@SoftwareNuggets
2 ай бұрын
Much appreciated
@monicabn3067
11 ай бұрын
One of the best tutorial for function which can be practiced via open source application ❤❤
@SoftwareNuggets
11 ай бұрын
Thank you
@g13n4
3 ай бұрын
Very easy to understand and very helpful. Thank you!
@SoftwareNuggets
3 ай бұрын
Glad to hear this video was helpful. Appreciate you leaving a comment.
@NirojMeshram
2 ай бұрын
Very well explained. covered so many thing in such a easy way.
@SoftwareNuggets
2 ай бұрын
Hey @NirojMeshram, much appreciated.
@hamzasl4117
3 ай бұрын
This was very helpful Sir.
@SoftwareNuggets
3 ай бұрын
Hey @hamzas4117, glad to hear this video was very helpful. Thanks for letting me be part of your learning journey.
@abdulrehmanjaved-rt8jq
2 ай бұрын
Thanks
@SoftwareNuggets
2 ай бұрын
Your welcome
@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.
@TheGladiator123
4 ай бұрын
my man is a absolutely wonderful appreciate what you do man
@SoftwareNuggets
4 ай бұрын
Thanks a lot @TheGladiator123, appreciate you taking the time to write a comment, and hope this video was helpful.
@TheGladiator123
4 ай бұрын
@@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
4 ай бұрын
@@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) kzitem.info/news/bejne/mItnmG2bq2V-jIY --table valued function kzitem.info/news/bejne/mpeOqmpvsXSngKg --insert stored procedure kzitem.info/news/bejne/y4Wvv5ytsX94o5w --cursor in a function kzitem.info/news/bejne/0GqY0qxsgpxmhm0 --how to write a scalar function kzitem.info/news/bejne/k3qqtnmOo6Cppmk --return a rowtype kzitem.info/news/bejne/yYWbmZafmKCGjHY --convert ip address to big int kzitem.info/news/bejne/tIyivaGghmakiqA
@SoftwareNuggets
4 ай бұрын
@@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
4 ай бұрын
@@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
@flyingzeppo
2 жыл бұрын
Very useful and informative. Thank you.
@SoftwareNuggets
2 жыл бұрын
Glad to hear this video was useful. I appreciate you leaving a comment.
@jingosf
2 жыл бұрын
Excellent stuff! Very helpful and useful tutorial.
@SoftwareNuggets
2 жыл бұрын
Thanks for the comment, glad to hear this video was helpful.
@ISHANCHANDRAKAR-iz2nh
2 ай бұрын
thanks it worked
@SoftwareNuggets
2 ай бұрын
Which solution helped you?
@ISHANCHANDRAKAR-iz2nh
2 ай бұрын
@@SoftwareNuggets including function procedure under $$ for creating function
@ahmednour2839
8 ай бұрын
Very informative video, thank you!
@SoftwareNuggets
8 ай бұрын
You're very welcome!
@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.
@user-cs8kr4go8t
Жыл бұрын
Thank you! Very useful vidio!
@SoftwareNuggets
Жыл бұрын
Glad it was helpful!
@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.
@ArogunmatiMulikat
4 ай бұрын
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
4 ай бұрын
Are you asking, other than LANGUAGE plpgsql, what other languages can you use?
@abdulrehmanjaved-rt8jq
2 ай бұрын
why we use inout btw, you haven't explained.
@SoftwareNuggets
2 ай бұрын
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
2 ай бұрын
@@SoftwareNuggets Thanks for great explaination, appreciated.
@humayunhimu6045
Жыл бұрын
wow! very useful!
@SoftwareNuggets
Жыл бұрын
Glad to hear you found this video useful. Thanks for the comment.
@hasanmougharbel8030
2 жыл бұрын
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
2 жыл бұрын
@@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
2 жыл бұрын
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
2 жыл бұрын
please watch my CURSOR video, i show how a function can be used to UPDATE/DELETE Here is a link: kzitem.info/news/bejne/0GqY0qxsgpxmhm0
@MC-Minority
7 ай бұрын
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
7 ай бұрын
hey @MC-Minority, in this statement, if you comment out --DECLARE new_substring VARCHAR. that variable is un-used. Scott
@SoftwareNuggets
7 ай бұрын
i just watched that video, at position 02:54 has an example close to yours. Might be helpful.
@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: kzitem.info/news/bejne/k3qqtnmOo6Cppmk
@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.
@abdallhosama9992
10 ай бұрын
Do you have more explanations about the functions in postgre
@SoftwareNuggets
10 ай бұрын
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. kzitem.info/news/bejne/mpeOqmpvsXSngKg PostgreSQL Function to Convert an IP Address into a Big Int, and from a BIG Int to an IP Address. kzitem.info/news/bejne/tIyivaGghmakiqA PostgreSQL - Use Function to Generate XML from table data using XMLForest, XMLElement and XLMAgg. kzitem.info/news/bejne/z3l5l4Ovk6Veg3o PostgreSQL - Write a Function that uses RETURNS SETOF json. kzitem.info/news/bejne/r3eLwItjmJqXan4 PostgreSql Enum: A Beginner's guide to Implement and Use ENUMS in a function. kzitem.info/news/bejne/wHhjyaigcKyBoaQ PostgreSQL - Create Function, use rowtype and return json object kzitem.info/news/bejne/yYWbmZafmKCGjHY PostgreSQL: A table-valued function to Split Full Names into individual fields. kzitem.info/news/bejne/qG1t3JN8f6FqbHY PostgreSQL: Scalar Function. How to write and use a SQL Scalar function. kzitem.info/news/bejne/k3qqtnmOo6Cppmk
@ledinhanhtan
Ай бұрын
🙏🙏🙏
@SoftwareNuggets
Ай бұрын
Thanks
@manojkumargobidesi1575
Жыл бұрын
Thank you
@SoftwareNuggets
Жыл бұрын
I hope this video was useful. Thanks for visiting software nuggets, watching this video and leaving a comment.
Пікірлер: 79