Writing User-Defined Functions in PostgreSQL

Table of contents

1. 1. Introduction to User-Defined Functions

   0/11

   Get familiar with creating functions in PostgreSQL.

   List of exercises Start now
   1. Introduction
      1. Welcome
      2. Example: get_pi() function
      3. Invoke the get_pi() function
      4. Purpose of functions
      5. Function syntax
      6. Invoking the new circumference function
      7. Updating the circle table – circumference
   2. Summary
      1. Summary
      2. Create a new function – area of a circle
      3. Updating the circle table – area of a circle
      4. Congratulations

2. 2. Writing Functions in PostgreSQL – the Basics

   0/29

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

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. The person table
      3. The salary table
      4. Returning values from a function
   2. Syntax of PL/pgSQL
      1. RAISE NOTICE
      2. RAISE NOTICE with arguments
      3. Declaring variables and value assignment
      4. Assign the result of a query to a variable #1
      5. Assign the result of a query to a variable #2
      6. Declaring constants
      7. Conditional statements
      8. Boolean operators
      9. WHILE loop
      10. FOR loop
   3. Function parameters
      1. Input parameters
      2. DEFAULT value for parameters
      3. OUT parameters
      4. OUT parameters – invoking the function #1
      5. OUT parameters – invoking the function #2
      6. INOUT parameters
      7. VARIADIC parameters
      8. Function overloading
   4. Handling exceptions
      1. Raising exceptions – example
      2. Raising exceptions
      3. Handling exceptions
   5. Summary
      1. Summary and Exercise 1
      2. Exercise 2
      3. Exercise 3
      4. Congratulations

3. 3. Writing Complex Functions in PostgreSQL

   0/18

   Learn how to use complex statements in functions.

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. The user_account table
      3. The topic table
      4. The post table
      5. The post table – example
   2. Side-effects in functions
      1. Functions returning void
      2. Multiple operations in one function
      3. Throwing exceptions
   3. Invoking functions
      1. Writing complex functions
      2. Call a function in SQL
      3. Invoke with SELECT does not work in functions
      4. Call a function with SELECT in function
      5. Assign a query result to a variable
      6. Assign a function result to a variable
      7. Use SELECT INTO with multiple values
      8. Returning a result from INSERT, UPDATE, or DELETE
      9. PERFORM
      10. Summary

4. 4. Functions returning tables in PostgreSQL

   0/13

   Discover how you can return tables from functions in PostgreSQL.

   List of exercises Buy course
   1. Returning tables and sets
      1. Functions returning tables – example
      2. RETURNS TABLE with RETURN QUERY
      3. Invoke a function that returns a table #1
      4. Invoke a function that returns a table #2
      5. Function returning a table – SETOF #1
      6. Function returning a table – SETOF #2
      7. RETURNS TABLE with RETURN NEXT
   2. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Congratulations

5. 5. Function management

   0/8

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

   List of exercises Buy course
   1. Function management
      1. Introduction
      2. CREATE OR REPLACE FUNCTION
      3. CREATE OR REPLACE FUNCTION – practice
      4. ALTER FUNCTION and RENAME TO
      5. ALTER FUNCTION and RENAME TO – practice
      6. DROP FUNCTION
      7. DROP FUNCTION IF EXISTS
      8. Summary

6. 6. Quiz

   0/15

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

   List of exercises Buy course
   1. Custom logging
      1. The log table
      2. Create a logging function
      3. Test the logging function
   2. Loading Customers
      1. The dim_customer table
      2. The stg_customer table
      3. Implement the ETL process
      4. Test the implementation
   3. Subscriptions
      1. The newsletter table
      2. The subscription table
      3. Create a function
      4. Function overloading
      5. Function testing
      6. Mailing list function
      7. Executing mailing list function
   4. Congratulations
      1. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more