How to write SQL Functions in PostgreSQL

How to write SQL Functions in PostgreSQL.

Learn how to write PostgreSQL SQL functions. Learn how to use parameters in a PostgreSQL function, how to use the return value and datatype.

Table of Contents
00:00 Begin Video
00:11 How to write postgresql string function MID, use $1
02:54 How to write sql function that uses ALIAS keyword
04:51 How to write sql function that uses parameter names and datatypes
05:51 - Use a template for fnMakeFull (firstname + secondName), how to use IF ELSIF ELSE, concat, concat_ws, initcap
11:51 how to use INOUT parameter, write function to swap
13:05 how to write function that uses array as input parameter
how to loop over array to calculate the mean value
16:27 to write sql function that returns data from a table QUERY RESULT, return query
20:42 End message from scott johnson

how to create function in postgresql using pgadmin
create or replace function postgres
postgresql function with parameters
postgresql function return table all columns
postgres return dataset from function
postgresql alias column
postgresql column alias where clause
postgres string concat operator

#softwareNuggets, #postgresql, language plpgsql, @SoftwareNuggets

[SoftwareNuggets]

Template Code for creating SQL Function
create or replace function func_name(fieldName datatype)
returns <return_datatype>
as
$$
begin
<type in function body here>
end;
$$
language plpgsql;


--parameter *default **variable number of arguments
create or replace function func_name({parameter type} fieldName datatype)
as
$$
begin
<type in function body here>
end;
$$
language plpgsql;


if <condition> then
<statements>
elsif <condition> then
<statements>
else
<statements>
end if;


create or replace function fn<table_event>(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;

[kvnagendra]

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

[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!

[dexvt]

Amazing lecture. Thanks Software Nuggets!

[monicabn]

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

[khizerhayyat]

very informative and easy to understand teaching style.

[senhordoobvio]

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

[NirojMeshram]

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

[gn]

Very easy to understand and very helpful. Thank you!

[flyingzeppo]

Very useful and informative. Thank you.

[TheGladiator]

my man is a absolutely wonderful
appreciate what you do man

[jingosf]

Excellent stuff! Very helpful and useful tutorial.

[ChrisHanks_ColonelOfTruth]

most excellent stuff. thank you for doing these.

[hasanmougharbel]

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

[santchev]

Great work, help me a lot. Thank you!

[abdallhosama]

Do you have more explanations about the functions in postgre

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

[adnanberki]

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

[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;

[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