PostgreSQL Stored Procedures EXPLAINED | Step-by-step Guide In pgAdmin 4 | Intermediate SQL Tutorial
Welcome back to another intermediate-advanced SQL tutorial!
Let us talk about Stored Procedures in SQL, more specifically, in PostgreSQL with the pgAdmind 4 interface.
First, I will explain the analogy with OOP Object-Oriented Programming and SQL Procedures (no worries, I will explain everything in simple terms) and then I will guide you through step-by-step how to create your custom methods, a.k.a. Stored Procedures.
What to watch next:
Automating stored procedures with SQL triggers
• SQL Automation | PostgreSQL Triggers EXPLA...
Machine Learning in SQL
• Machine Learning in SQL? But I Am Python E...
========================================
Timeline:
00:00 Intro
00:23 What are Stored Procedures?
02:52 Create Sample Table
04:48 Populate Table With Data Manually
06:09 Time to Create Procedure (OOP Analogy)
07:47 Procedure Skeleton
09:44 Populate Values With SQL Procedure
10:25 Note On Local Variables
11:15 How to List All Procedures In The Schema
12:12 How to Call Procedure in Postgres
12:38 Check That Everything Works As Intended
12:52 Homework Challenge!
17:40 Afterword
========================================
Queries from the tutorial:
-- Create a simple table:
CREATE TABLE invoices
(
invoice_id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL,
total_amount NUMERIC NOT NULL,
invoice_date DATE NOT NULL,
is_paid BOOLEAN DEFAULT FALSE
);
-- Check that table is created (without data yet)
SELECT * FROM public.invoices
ORDER BY invoice_id ASC;
-- defines the creation of a new stored procedure
CREATE OR REPLACE PROCEDURE create_invoice
(
-- procedure parameters
-- can be IN/OUT/INOUT for input-only, output-only, or both, respectively
IN customer_name TEXT,
IN total_amount NUMERIC,
IN invoice_date DATE
)
-- plpgsql is a procedural language used in PostgreSQL
LANGUAGE plpgsql
-- $$ marks the beginning of the procedure's body
AS $BODY$
BEGIN
-- procedure body
INSERT INTO invoices (customer_name, total_amount, invoice_date)
VALUES (customer_name, total_amount, invoice_date);
END;
$BODY$;
-- Inserting values in the table using a direct method:
INSERT INTO invoices (customer_name, total_amount, invoice_date)
VALUES ('Jane Doe', 234, '2024-04-29');
-- The same, but using our custom procedure
CALL create_invoice('John Q. Public', 102, '2024-04-29');
========================================
Ways to connect:
Subscribe!
https://bit.ly/RBrilenkovYT
LinkedIn
/ ruslan-brilenkov
Medium
/ ruslan-brilenkov
========================================
Disclaimer 1: This royalty-free background music was generated with AI software and post-processed afterward. If you are tired of spending too much time and energy finding background music for your videos, dramatically reduce that time with Mix.audio!
Use the promo link below to get a 30% discount on your first monthly subscription payment, and by doing so, you will also support the channel at no extra cost to you:
https://bit.ly/ruslanpromoAI
========================================
Disclaimer 2: everything presented in this video is my own opinion and is meant to educate and share information, nothing mentioned or described here is legal or financial advice. Ruslan Brilenkov is not responsible for any profits or losses associated with your investment. So, please be responsible for your own actions.