Introduction to PL_pgSQL

1 .Introduction to PL/pgSQL 

2 .Procedural Language Overview • PostgreSQL allows user-defined functions to be written in a variety of procedural languages. The database server has no built-in knowledge about how to interpret the function’s source text. Instead, the task is passed to a handler that knows the details of that particular language. • PostgreSQL currently supports several standard procedural languages • PL/pgSQL • PL/Tcl • PL/Perl • PL/Python • PL/Java • And many more 

3 .What is PL/pgSQL • PL/pgSQL is the procedural extension to SQL with features of programming languages • Data Manipulation and Query statements of SQL are included within procedural units of code • Allows using general programming tools with SQL, for example: loops, conditions, functions, etc. • This allows a lot more freedom than general SQL, and is lighter-weight than calling from a client program 

4 .How PL/pgSQL works • PL/pgSQL is like every other “loadable, procedural language.” • When a PL function is executed, the fmgr loads the language handler and calls it. • The language handler then interprets the contents of the pg_proc entry for the function (proargtypes, prorettype, prosrc). 

5 .How PL/pgSQL works • On the first call of a function in a session, the call handler will “compile” a function statement tree. • SQL queries in the function are just kept as a string at this point. • What might look to you like an expression is actually a SELECT query: my_variable := some_parameter * 100; 

6 .How PL/pgSQL works • The PL/pgSQL statement tree is very similar to a PostgreSQL execution tree. • The call handler then executes that statement tree. • On the first execution of a statement node, that has an SQL query in it, that query is prepared via SPI. • The prepared plan is then executed for every invocation of that statement in the current session. 

7 .PL/pgSQL Environment 

8 .Kinds of PL/pgSQL Blocks The basic unit in any PL/pgSQL code is a BLOCK. All PL/pgSQL code is composed of a single block or blocks that occur either sequentially or nested within another block. There are two kinds of blocks: • Anonymous blocks (DO) • Generally constructed dynamically and executed only once by the user. It is sort of a complex SQL statement • Named blocks (Functions and Stored Procedures) • Have a name associated with them, are stored in the database, and can be executed repeatably, and can take in parameters 

9 .Structure of Anonymous Block DO $$ [ <<label>> ] DECLARE /* Declare section (optional). */ BEGIN /* Executable section (required). */ EXCEPTION /* Exception handling section (optional). */ END [ label ] $$; 

10 .Comments • There are two types of comments in PL/pgSQL • -- starts a comment that extends to the end of the line • /* multi-line comments */ • Commenting is necessary to tell people what is intended and why it was done a specific way • Err on the side of too much commenting 

11 .Variables • Use variables for • Temporary storage of data • Manipulation of stored values • Re-usability • Ease of maintenance • Declared in the declarative section within a block v_last_name VARCHAR(15); 

12 .Handling Variables • Variables declared in the declarations section preceding a block are initialized to their default values every time the block is entered, not only once per function call • Variables in a declaration section can shadow variables of the same name in an outer block. If the outer block is named with a label, its variables are still available by specifying them as <label>.<varname> 

13 .Declarations Syntax identifier [CONSTANT] datatype [NOT NULL] [:= | = | DEFAULT expr]; Examples DECLARE v_birthday DATE; v_age INT NOT NULL = 21; v_name VARCHAR(15) := 'Homer'; v_magic CONSTANT NUMERIC := 42; v_valid BOOLEAN DEFAULT TRUE; 

14 .%TYPE • Declare variable according to : • A database column definition • Another previously declared variable identifier table.column_name%TYPE; Example DECLARE v_email users.email%TYPE; v_my_email v_email%TYPE := 'rds-postgres-extensions-request@amazon.com'; 

15 .%ROWTYPE • Declare a variable with the type of a ROW of a table identifier table%ROWTYPE; Example DECLARE v_user users%ROWTYPE; 

16 .Records • A record is a type of variable similar to ROWTYPE, but with no predefined structure • The actual structure of the record is created when the variable is first assigned • A record is not a true data type, only a place holder DECLARE r record; 

17 .Variable Scope DO $$ DECLARE quantity integer := 30; BEGIN RAISE NOTICE 'Quantity here is %', quantity; -- 30 quantity := 50; -- Create a subblock DECLARE quantity integer := 80; BEGIN RAISE NOTICE 'Quantity here is %', quantity; -- 80 END; RAISE NOTICE 'Quantity here is %', quantity; -- 50 END $$; 

18 .Qualify an Identifier DO $$ << mainblock >> DECLARE quantity integer := 30; BEGIN RAISE NOTICE 'Quantity here is %', quantity; --30 quantity := 50; -- Create a subblock DECLARE quantity integer := 80; BEGIN RAISE NOTICE 'Quantity here is %', mainblock.quantity; --50 RAISE NOTICE 'Quantity here is %', quantity; --80 END; RAISE NOTICE 'Quantity here is %', quantity; --50 END $$; 

19 .RAISE • Reports messages • Can be seen by the client if the appropriate level is used RAISE NOTICE 'Calling cs_create_job(%)', v_job_id; 

20 .Assigning Values • Use the assignment operator ( := or = ) DECLARE v_last_name VARCHAR := 'Smith'; v_date DATE; BEGIN v_last_name := lower(v_last_name); v_date := to_date('2000-01-01', 'YYYY-MM-DD'); 

21 .SELECT in PL/pgSQL • Retrieve data from the database with a SELECT statement • Queries must return only one row • INTO clause is required DECLARE v_first_name users.first_name%TYPE; v_last_name users.last_name%TYPE; BEGIN SELECT first_name, last_name INTO v_first_name, v_last_name FROM users WHERE user_id = 1; END 

22 .INSERT / UPDATE / DELETE DECLARE v_forum_name forums.name%TYPE := 'Hackers'; BEGIN INSERT INTO forums (name) VALUES (v_forum_name); UPDATE forums SET moderated = true WHERE name = v_forum_name; END 

23 .PERFORM • Evaluate an expression or query but discard the result • Frequently used when executing maintenance commands BEGIN PERFORM create_partition('moderation_log', '2016-06'); END 

24 .Structure of Named Blocks CREATE FUNCTION [ function_name ] () RETURNS [return_type] $$ [ <<label>> ] DECLARE /* Declare section (optional). */ BEGIN /* Executable section (required). */ EXCEPTION /* Exception handling section (optional). */ END [ label ] $$ LANGUAGE plpgsql; 

25 .Function Example CREATE FUNCTION get_user_count() RETURNS integer AS $$ DECLARE v_count integer; BEGIN SELECT count(*) INTO v_count FROM users; RETURN v_count; END $$ LANGUAGE plpgsql; 

26 .Dollar Quoting • The tag $$ denotes the start and end of a string • Optionally can have a non-empty tag as part of the quote • $_$ • $abc$ • Can be used to prevent unnecessary escape characters throughout the string $function$ BEGIN RETURN ($1 ~ $q$[\t\r\n\v\\]$q$); END; $function$ 

27 .Function Parameters • One or more parameters can be used • Parameter names are optional, but highly recommended CREATE FUNCTION get_user_name(varchar, p_last_name varchar) RETURNS varchar AS $$ DECLARE v_first_name varchar; v_name varchar; BEGIN v_first_name := $1; SELECT name INTO v_name FROM users WHERE first_name = v_first_name AND last_name = p_last_name LIMIT 1; RETURN v_name; END $$ LANGUAGE plpgsql; 

28 .Default Parameters • Paramters can have a default value • This essentially makes them optional parameters CREATE FUNCTION get_user_count(p_active boolean DEFAULT true) RETURNS integer AS $$ DECLARE v_count integer; BEGIN SELECT count(*) INTO v_count FROM users WHERE active = p_active; RETURN v_count; END $$ LANGUAGE plpgsql; 

29 .Assertions • A convenient shorthand for inserting debugging checks • Can be controlled by plpgsql.check_asserts variable CREATE FUNCTION get_user_count(p_active boolean DEFAULT true) RETURNS integer AS $$ DECLARE v_count integer; BEGIN ASSERT p_active IS NOT NULL; SELECT count(*) INTO v_count FROM users WHERE active = p_active; RETURN v_count; END $$ LANGUAGE plpgsql;