Writing User-Defined Functions in PostgreSQL - SQL online course | LearnSQL.com

Advanced

Interactive Course

Take your PostgreSQL skills to the next level by learning how to write your own functions.

Table of contents

94

Interactive exercises

20 h

Estimated time

95

Users enrolled

☆☆☆☆☆

★★★★★

4 ratings

Description Table of contents Reviews Start learning

Why write your own functions? In PostgreSQL, functions allow you to perform operations that would normally take several queries and round trips. In simple words, this means you'll get your results much faster. User-defined functions extend the functionality of PostgreSQL and add convenience, improve query logic, and allow other applications to reuse your database.

In this course, you'll learn about PL/pgSQL, the most commonly used procedural language in PostgreSQL. You'll get to know the syntax of PL/pgSQL, various types of parameters that functions in PostgreSQL support (IN, OUT, INOUT parameters), and how to use complex statements in a function body. Moreover, you'll learn to write functions with hands-on, practical examples based on real-world use cases. In the final part of the course, you'll write functions implementing a simple ETL (Extract-Transform-Load) process in a data warehouse (DWH).

Writing user-defined functions is something all advanced users of databases should know. This course is ideal for aspiring database programmers and future database administrators (DBAs).

Note: Up until PostgreSQL 11, user-defined functions in PostgreSQL were sometimes also called (and used as) stored procedures. Don't be surprised if you see the term "stored procedure" refer to a user-defined function in Postgres. This course only covers creating user-defined functions in PostgreSQL.

What's in It for Me?

94 interactive exercises. Learn at your own pace, from anywhere and anytime. Interact with hands-on exercises for improved retention.
Lifetime access to the course. When you purchase the course, you'll get instant personal access to all of its content.
Certificate of completion. After you successfully finish all of the exercises, you'll get a downloadable PDF certificate to showcase your accomplishment.
30-day money-back guarantee. If you're not satisfied with the quality of the course, you can get a refund within 30 days of your purchase.
Hints for the exercises. You can ask questions and share insights with other members of our community through the Discuss tab. You can also drop us a line directly at academy@vertabelo.com, and we'll be more than happy to answer! 😉

What Do You Need to Take This Course?

A web browser and an Internet connection.
Basic knowledge of SQL.
Basic knowledge of programming.

This Course Will Teach You:

What a user-defined function is and when you should use it.
How to create a user-defined function in PostgreSQL.
The syntax of PostgreSQL’s most commonly used procedural language, PL/pgSQL.
The different types of parameters in PostgreSQL functions (IN, OUT and INOUT).
How you can use results of a SQL query inside a function.
How you can nest function calls.
How to return a table from a function.

Who Should Take This Course?

Students taking course on relational databases.
PostgreSQL developers.
Beginner Database Administrators (DBAs).
People interested in programming relational databases, particularly PostgreSQL.

Table of contents

Free

Paid content

0%

Course progress

0/94

Exercises completed

Get familiar with creating functions in PostgreSQL.

0/11

Exercises completed

Start now

Introduction

Example: get_pi() function

Invoke the get_pi() function

Purpose of functions

Function syntax

Invoking the new circumference function

Updating the circle table – circumference

Summary

Create a new function – area of a circle

Updating the circle table – area of a circle

Congratulations

Get to know the basic syntax of implementing functions in PostgreSQL. Learn about parameter types supported in PostgreSQL functions.

0/29

Exercises completed

Upgrade

Introduction

The person table

The salary table

Returning values from a function

Syntax of PL/pgSQL

RAISE NOTICE with arguments

Declaring variables and value assignment

Assign the result of a query to a variable #1

Assign the result of a query to a variable #2

Declaring constants

Conditional statements

Boolean operators

Function parameters

Input parameters

DEFAULT value for parameters

OUT parameters – invoking the function #1

OUT parameters – invoking the function #2

INOUT parameters

VARIADIC parameters

Function overloading

Handling exceptions

Raising exceptions – example

Raising exceptions

Handling exceptions

Summary

Summary and Exercise 1

Congratulations

Learn how to use complex statements in functions.

0/18

Exercises completed

Upgrade

Introduction

The user_account table

The topic table

The post table – example

Side-effects in functions

Functions returning void

Multiple operations in one function

Throwing exceptions

Invoking functions

Writing complex functions

Call a function in SQL

Invoke with SELECT does not work in functions

Call a function with SELECT in function

Assign a query result to a variable

Assign a function result to a variable

Use SELECT INTO with multiple values

Returning a result from INSERT, UPDATE, or DELETE

Discover how you can return tables from functions in PostgreSQL.

0/13

Exercises completed

Upgrade

Returning tables and sets

Functions returning tables – example

RETURNS TABLE with RETURN QUERY

Invoke a function that returns a table #1

Invoke a function that returns a table #2

Function returning a table – SETOF #1

Function returning a table – SETOF #2

RETURNS TABLE with RETURN NEXT

Summary

Congratulations

Learn how to create, remove and rename functions in PostgreSQL.

0/8

Exercises completed

Upgrade

Function management

CREATE OR REPLACE FUNCTION

CREATE OR REPLACE FUNCTION – practice

ALTER FUNCTION and RENAME TO

ALTER FUNCTION and RENAME TO – practice

DROP FUNCTION IF EXISTS

Check your knowledge of writng functions in PostgreSQL by implementing a simple Extract-Tranform-Load (ETL) process.

0/15

Exercises completed

Upgrade

Custom logging

Create a logging function

Test the logging function

Loading Customers

The dim_customer table

The stg_customer table

Implement the ETL process

Test the implementation

Subscriptions

The newsletter table

The subscription table

Create a function

Function overloading

Function testing

Mailing list function

Executing mailing list function

Congratulations

Congratulations

Average rating

4.75 / 5

☆☆☆☆☆

★★★★★

4 ratings

Details

5 stars

75%

4 stars

25%

3 stars

0%

2 stars

0%

1 stars

0%