Introduction to PL_pgSQL

在针对PostgreSQL数据库进行开发时,您将多次使用应用程序中的SQL,但有时需要更多。为了提高性能,您可能需要在数据库服务器上运行一些逻辑,或者您可能只需要创建一些服务器端函数来简化应用程序开发。有无数的原因可以解释为什么您要编写一些运行在PostgreSQL服务器上的代码,而PostgreSQL在您想这样做时有一组丰富的选项,但大多数情况下,您将使用pl/pgsql。本教程的主要目标是向您介绍PostgreSQL PL/PGSQL编程语言,并为您提供实用的技能,使您能够成为使用PostgreSQL数据库的高效开发人员。
在本教程的过程中,将介绍各种基本的pl/pgsql函数,如:
如何声明和使用pl/sql变量 如何创建函数和过程
如何编写触发器 处理运行时错误

展开查看详情

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;