# Lesson 12: PL/pgSQL / Query Execution > Native Markdown source page: PL/pgSQL / Query Execution --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** [dev1_12_plpgsql_queries.html](https://edu.postgrespro.com/16/dev1-16-en/dev1_12_plpgsql_queries.html) > **Archive:** [DEV1-handouts-16-en.zip](https://edu.postgrespro.com/16/DEV1-handouts-16-en.zip) > **PDF pages:** 24 ## Page 1: PL/pgSQL / Query Execution {#source-page-001} ### Copyright © Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo by: Oleg Bartunov (Phu monastery, Bhrikuti summit, Nepal) ### Use of Course Materials Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials. ### Feedback Please send your feedback, comments and suggestions to: edu@postgrespro.ru ### Disclaimer Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications. ## Page 2: Topics {#source-page-002} - Using SQL Commands in PL/pgSQL Code - Eliminating Naming Ambiguities - Checking Command Status - Set Returning Functions ## Page 3: Returning Nothing {#source-page-003} **SQL commands are embedded into PL/pgSQL code** - same as in expressions: - the statement is prepared, PL/pgSQL variables become parameters - SELECT → PERFORM - convenient for calling functions with side effects - WITH queries should be wrapped into a SELECT - INSERT, UPDATE, DELETE and other SQL commands - except for service commands - transaction management: only in procedures and anonymous blocks ### Speaker notes As we have already seen, PL/pgSQL is very closely integrated with SQL. In particular, all expressions are computed using prepared SQL statements. Besides, expressions can use PL/pgSQL variables and routine parameters: they will be implicitly converted to query parameters. SQL statements can also be executed within PL/pgSQL code. To execute a statement that returns no result (INSERT, UPDATE, DELETE, CREATE, DROP, etc.), you just need to write an SQL command within the PL/pgSQL code as a separate statement. Commands are prepared exactly like expressions, with PL/pgSQL variables used as parameters. It allows caching the parsed (or planned) query to avoid repeating this work. In a similar manner, you can also execute a regular SELECT statement if its result is unimportant; simply replace the SELECT keyword with PERFORM. It makes sense for cases like calling functions with side effects. WITH queries have to be wrapped into a SELECT statement (so that it starts with PERFORM when executed). Service commands like VACUUM, REINDEX and others are not allowed in routines. COMMIT and ROLLBACK commands are allowed only in procedures and anonymous blocks (executed by the SQL command DO). TATEMENTS-GENERAL-SQL ## Page 4: Returning Nothing {#source-page-004} If the result of the query is not needed, replace SELECT with PERFORM: ```sql => CREATE FUNCTION do_something() RETURNS void AS $$ BEGIN RAISE NOTICE 'Something has been done.'; END $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ```text => DO $$ BEGIN PERFORM do_something(); END $$; ``` ```text NOTICE: Something has been done. DO ``` Almost any SQL command that returns no rows can be used in PL/pgSQL without modification: ```sql => DO $$ BEGIN CREATE TABLE test(n integer); INSERT INTO test VALUES (1),(2),(3); UPDATE test SET n = n + 1 WHERE n > 1; DELETE FROM test WHERE n = 1; DROP TABLE test; END $$; ``` ```text DO ``` ### Transaction Control in Procedures Procedures (and anonymous blocks) written in PL/pgSQL support transaction control commands: ```sql => CREATE TABLE test(n integer); ``` ```sql CREATE TABLE ``` ```sql => CREATE PROCEDURE foo() AS $$ BEGIN INSERT INTO test VALUES (1); COMMIT; INSERT INTO test VALUES (2); ROLLBACK; END $$ LANGUAGE plpgsql; ``` ```sql CREATE PROCEDURE ``` ```text => CALL foo(); ``` ```text CALL ``` ```sql => SELECT * FROM test; ``` ```text n --- 1 (1 row) ``` There are certain limitations. First of all, in such cases, a procedure itself must start a new transaction, i.e., it must not be executed in the context of an already started transaction. ```text => BEGIN; ``` ```text BEGIN ``` ```text => CALL foo(); -- error ``` ## Page 5: Page 5 {#source-page-005} ```text ERROR: invalid transaction termination CONTEXT: PL/pgSQL function foo() line 4 at COMMIT ``` ```text => ROLLBACK; ``` ```text ROLLBACK ``` Second, the call stack of this procedure must contain only CALL statements. In other words, if a procedure calls a procedure ... that calls a procedure that performs transaction control, everything works fine: ```sql => CREATE OR REPLACE PROCEDURE foo() AS $$ BEGIN CALL bar(); END $$ LANGUAGE plpgsql; ``` ```sql CREATE PROCEDURE ``` ```sql => CREATE PROCEDURE bar() AS $$ BEGIN CALL baz(); END $$ LANGUAGE plpgsql; ``` ```sql CREATE PROCEDURE ``` ```sql => CREATE PROCEDURE baz() AS $$ BEGIN COMMIT; END $$ LANGUAGE plpgsql; ``` ```sql CREATE PROCEDURE ``` ```text => CALL foo(); -- it works ``` ```text CALL ``` But should this stack include, say, a function call, the transaction would have to be completed somewhere in the middle of the statement which the context belongs to, i.e., SELECT. This is prohibited, so we get the error: ```sql => CREATE FUNCTION qux() RETURNS void AS $$ BEGIN CALL bar(); END $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT qux(); -- error ``` ```text ERROR: invalid transaction termination CONTEXT: PL/pgSQL function baz() line 3 at COMMIT SQL statement "CALL baz()" PL/pgSQL function bar() line 3 at CALL SQL statement "CALL bar()" PL/pgSQL function qux() line 3 at CALL ``` ## Page 6: Returning a Single Row {#source-page-006} SELECT ... INTO - get the first returned row - one variable of a composite type - or an appropriate number of scalar variables - INSERT, UPDATE, DELETE RETURNING ... INTO - get the inserted (updated, deleted) row - one variable of a composite type - or an appropriate number of scalar variables ### Speaker notes If the query result is important, you can use the INTO clause to save it into a single variable of a composite type or into several scalar variables. If the query returns several rows, only the first one makes it into the variable (you can set the output order with the ORDER BY clause). If the query returns no rows, the variables (or the attributes of the composite type variable) will be set to NULL. In a similar manner, you can also use INSERT, UPDATE, and DELETE commands with the RETURNING clause. The difference is that these commands must not return more than one row: it will lead to an error as there is no way to specify which row is the first one. If a query returns several rows, but only one of them is used, it’s highly likely that this query is incorrect. PL/pgSQL can report such suspicious situations (and several others). SQL-EXTRA-CHECKS More powerful debugging capabilities are provided by an external extension plpgsql\_check (developed by Pavel Stehule). ## Page 7: Returning a Single Row {#source-page-007} A SELECT command returning a single row is probably the most frequently used one in PL/pgSQL. Here is an example that displays one row, despite the query within the anonymous block returning two: ```sql => CREATE TABLE t(id integer, code text); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t VALUES (1, 'One'), (2, 'Two'); ``` ```sql INSERT 0 2 ``` ```sql => DO $$ DECLARE r record; BEGIN SELECT id, code INTO r FROM t; RAISE NOTICE '%', r; END $$; ``` ```text NOTICE: (1,One) DO ``` > [!NOTE] > **Note** > Note the construct in the PostgreSQL code that is very similar to SELECT INTO command from SQL. In the command, after INTO, there is the name of the new table, created and filled with the query output. In PL/pgSQL, the equivalent syntax would be CREATE TABLE ... AS SELECT. INSERT, UPDATE, and DELETE commands can also return the result using the RETURNING clause. They can be used in PL/pgSQL with the INTO clause, just like SELECT: ```sql => DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' WHERE id = 1 RETURNING * INTO r; RAISE NOTICE 'Modified: %', r; END $$; ``` ```text NOTICE: Modified: (1,One!) DO ``` ### Compile-Time and Run-Time Checks for Routines In some suspicious cases, PL/pgSQL can issue warnings. Warnings can be enabled with the following parameter (set to none by default): ```text => SET plpgsql.extra_warnings = 'all'; ``` ```text SET ``` ```sql => CREATE PROCEDURE bugs(INOUT a integer) AS $$ DECLARE a integer; b integer; BEGIN SELECT id INTO a, b FROM t; END $$ LANGUAGE plpgsql; ``` ```sql WARNING: variable "a" shadows a previously defined variable LINE 4: a integer; ^ CREATE PROCEDURE ``` This is a warning about two variable declarations that override each other: ```text => CALL bugs(42); ``` ## Page 8: Page 8 {#source-page-008} ```text WARNING: query returned more than one row HINT: Make sure the query returns a single row, or use LIMIT 1. WARNING: number of source and target fields in assignment does not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. a ---- 42 (1 row) ``` And here we can see two run-time warnings: the query has returned more than one row, and the INTO clause provides the wrong number of parameters (PL/pgSQL will assign NULL to the second one). There are no other checks available at the moment, but they can appear in the next PostgreSQL versions. The plpgsql.extra\_warnings value can specify some particular checks. A similar parameter plpgsql.extra\_errors enables throwing errors instead of warnings. ```text => RESET plpgsql.extra_warnings; ``` ```text RESET ``` You can perform more extensive code checks using plpgsql\_check, an external extension developed and supported by Pavel Stehule. This extension is already installed in the VM provided for this course. ```sql => CREATE SCHEMA plpgsql_check; ``` ```sql CREATE SCHEMA ``` ```sql => CREATE EXTENSION plpgsql_check SCHEMA plpgsql_check; ``` ```sql CREATE EXTENSION ``` ```sql => SELECT * FROM plpgsql_check.plpgsql_check_function('bugs(integer)'); ``` ```sql plpgsql_check_function ----------------------------------------------------------------------- warning:00000:5:statement block:parameter "a" is shadowed Detail: Local variable shadows function parameter. warning:00000:6:SQL statement:too few attributes for target variables Query: SELECT id FROM t -- ^ Detail: There are more target variables than output columns in query. Hint: Check target variables in SELECT INTO statement. Context: at SQL statement to a, b variables warning extra:00000:3:DECLARE:never read variable "a" warning extra:00000:4:DECLARE:never read variable "b" warning extra:00000:unused parameter "a" warning extra:00000:unmodified OUT variable "a" (12 rows) ``` Unused variables and an output parameter without an assigned value have been detected. The plpgsql\_checks extension provides many debugging features, including run-time error detection. It also includes a profiler for tuning PL/pgSQL code. ### Eliminating Naming Ambiguities Will the following code be executed successfully? ```sql => DO $$ DECLARE id integer := 1; code text; BEGIN SELECT id, code INTO id, code FROM t WHERE id = id; RAISE NOTICE '%, %', id, code; END $$; ``` ```sql ERROR: column reference "id" is ambiguous LINE 1: SELECT id, code FROM t WHERE id = id ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: SELECT id, code FROM t WHERE id = id CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement ``` No, because of the ambiguity in SELECT: id can mean both the name of the column and the name of the variable. Note that there is ## Page 9: Page 9 {#source-page-009} no ambiguity in the INTO clause: it relates to PL/pgSQL only. By the way, the message shows that PL/pgSQL cuts off the INTO clause before passing the query to SQL. There are several approaches to eliminating such ambiguities. The first one is to prevent them. It can be achieved by prepending variable names with a prefix, which usually corresponds to the variable type, for example: p\_ for parameters l\_ or v\_ for regular (local) variables c\_ for constants It is a simple and effective method if you use it consistently and never add prefixes to column names. Its disadvantage is that it makes the code look sloppy and somewhat clumsy because of extra underscores. This is how it can look like in our example: ```sql => DO $$ DECLARE l_id integer := 1; l_code text; BEGIN SELECT id, code INTO l_id, l_code FROM t WHERE id = l_id; RAISE NOTICE '%, %', l_id, l_code; END $$; ``` ```text NOTICE: 1, One! DO ``` Another approach is to use qualified names, i.e., prepend the object name with a qualifier followed by a dot: A name or an alias of a table for columns A block label for variables A function name for parameters This approach is more straightforward than adding prefixes because it works for any column names. This is how our example will look like if we use qualifiers: ```sql => DO $$ <> DECLARE id integer := 1; code text; BEGIN SELECT t.id, t.code INTO local.id, local.code FROM t WHERE t.id = local.id; RAISE NOTICE '%, %', id, code; END $$; ``` ```text NOTICE: 1, One! DO ``` The third approach is to prioritize variables over columns, or vice versa. Such prioritization is managed by the plpgsql.variable\_conflict configuration parameter. Its possible values are use\_column, use\_variable, error. In some cases, it facilitates conflict resolution, but it does not eliminate conflicts altogether. Besides, such an implicit rule (which can suddenly change, to make things worse) is sure to cause some code to be executed in a way unforeseen by the developer. Nevertheless, let’s see an example. Here variables have priority, so it is enough to add qualifiers to table columns only: ```text => SET plpgsql.variable_conflict = use_variable; ``` ```text SET ``` ```sql => DO $$ DECLARE id integer := 1; code text; BEGIN SELECT t.id, t.code INTO id, code FROM t WHERE t.id = id; RAISE NOTICE '%, %', id, code; END $$; ``` ## Page 10: Page 10 {#source-page-010} ```text NOTICE: 1, One! DO ``` ```text => RESET plpgsql.variable_conflict; -- reset the value to default ``` ```text RESET ``` ```text => SHOW plpgsql.variable_conflict; ``` ```text plpgsql.variable_conflict --------------------------- error (1 row) ``` Which approach to follow is a matter of taste and experience. We recommend choosing either the first or the second one (prefixes or qualifiers); make sure not to use both in one project because consistency can greatly assist code comprehension. In this course, we are going to use qualifiers, but only where it’s absolutely necessary, so that examples don’t get too cluttered. However, in production code you should always take care of all possible ambiguities: there is no guarantee that a new column won’t have the same name as your variable. ## Page 11: Checking Results {#source-page-011} INTO STRICT - guarantees that exactly one row is returned, no more and no less - ROW\_COUNT diagnostics - the number of rows returned (processed) by the last SQL command - FOUND variable - after an SQL command: true if the command has returned (processed) a row - after a loop: indicates that at least one iteration has been completed ### Speaker notes By adding the STRICT keyword to the INTO clause, we can guarantee that the command returns or processes exactly one row; otherwise, an error occurs. Besides, we can check the status of the SQL command that has just been executed (unless it has completed with an error). There are two ways to go about it. The first option is to use the GET DIAGNOSTICS clause to get the number of rows processed by the command. The second option is to use the boolean variable FOUND, which shows whether the command has processed any data at all. You can also use FOUND as an indicator that the loop body has been executed at least once. ## Page 12: Exactly One Row {#source-page-012} What will happen if the query returns several rows? ```sql => DO $$ DECLARE r record; BEGIN SELECT id, code INTO r FROM t; RAISE NOTICE '%', r; END $$; ``` ```text NOTICE: (2,Two) DO ``` Only the first row will be written into the variable. Since we have not specified the ORDER BY clause, the order is virtually unpredictable: ```sql => SELECT * FROM t; ``` ```text id | code ----+------ 2 | Two 1 | One! (2 rows) ``` INSERT, UPDATE, and DELETE commands do not allow specifying the order of the rows, so a command that affects several rows results in an error: ```sql => DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' RETURNING * INTO r; RAISE NOTICE 'Modified: %', r; END $$; ``` ```text ERROR: query returned more than one row HINT: Make sure the query returns a single row, or use LIMIT 1. CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement ``` And what if the query returns no rows at all? ```sql => DO $$ DECLARE r record; BEGIN r := (-1,'!!!'); SELECT id, code INTO r FROM t WHERE false; RAISE NOTICE '%', r; END $$; ``` ```text NOTICE: (,) DO ``` Variables will contain undefined values. It is also true for INSERT, UPDATE, and DELETE commands. For example: ```sql => DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' WHERE id = -1 RETURNING * INTO r; RAISE NOTICE 'Modified: %', r; END $$; ``` ```text NOTICE: Modified: (,) DO ``` Sometimes you may want to be sure that the query retrieves exactly one row, no more and no less. In this case, it is convenient to use the INTO STRICT clause: ## Page 13: Page 13 {#source-page-013} ```sql => DO $$ DECLARE r record; BEGIN SELECT id, code INTO STRICT r FROM t; RAISE NOTICE '%', r; END $$; ``` ```text ERROR: query returned more than one row HINT: Make sure the query returns a single row, or use LIMIT 1. CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement ``` ```sql => DO $$ DECLARE r record; BEGIN SELECT id, code INTO STRICT r FROM t WHERE false; RAISE NOTICE '%', r; END $$; ``` ```text ERROR: query returned no rows CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement ``` As we have seen, INSERT, UPDATE, and DELETE commands that affect several rows result in an error. The STRICT keyword guarantees that there will be exactly one row (and not zero): ```sql => DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' WHERE id = -1 RETURNING * INTO STRICT r; RAISE NOTICE 'Modified: %', r; END $$; ``` ```text ERROR: query returned no rows CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement ``` ### Explicit Checks of the Execution State You can also check the state of the last SQL command executed: The GET DIAGNOSTICS command retrieves the number of processed rows (ROW\_COUNT). A predefined boolean variable FOUND shows whether any row has been processed. ```sql => DO $$ DECLARE r record; rowcount integer; BEGIN SELECT id, code INTO r FROM t WHERE false; ``` ```text GET DIAGNOSTICS rowcount := ROW_COUNT; RAISE NOTICE 'rowcount = %', rowcount; RAISE NOTICE 'found = %', FOUND; END $$; ``` ```text NOTICE: rowcount = 0 NOTICE: found = f DO ``` ```sql => DO $$ DECLARE r record; rowcount integer; BEGIN SELECT id, code INTO r FROM t; ``` ```text GET DIAGNOSTICS rowcount := ROW_COUNT; RAISE NOTICE 'rowcount = %', rowcount; RAISE NOTICE 'found = %', FOUND; END $$; ``` ```text NOTICE: rowcount = 1 NOTICE: found = t DO ``` ## Page 14: Page 14 {#source-page-014} > [!NOTE] > **Note:** > such diagnostics does not detect that the query affects several rows. ROW\_COUNT returns 1, since only one row from the output has been saved to the variable r. ## Page 15: Set Returning Functions {#source-page-015} **Query rows** ```text RETURN QUERY query; ``` - One row - RETURN NEXT expression; - if there are no output parameters - RETURN NEXT; - if there are output parameters - Features - rows are added to the result, but function execution is not terminated - commands can be executed several times - the result is not returned until the function completes ### Speaker notes To create a table function in PL/pgSQL, you have to declare it with a clause RETURNS SETOF or RETURNS TABLE (just like in SQL). Commands RETURN QUERY and RETURN NEXT assemble individual elements of the returning value in a special buffer. These commands do not interrupt execution. They can be called multiple times within a function body, each call adding elements to the final output. When a function finishes (by a RETURN call with no parameters or simply by reaching the end of the function body), the output that has been assembled in the buffer is returned. If a function finishes after executing the RETURN QUERY query command once, the output will be identical to that of an SQL function running the query as its last. However, the query from an SQL function could be substituted into the main query, while with PL/pgSQL this is not the case. The result can also be returned row by row using the RETURN NEXT construct. It is similar to an ordinary RETURN, but instead of stopping the function execution, it adds the return value as another row of the future result. Thus, the RETURN NEXT and RETURN QUERY commands do not work as yield in the generator functions of modern languages. GSQL-STATEMENTS-RETURNING ## Page 16: Set Returning Functions {#source-page-016} Here is an example of a set-returning function written in PL/pgSQL: ```sql => CREATE FUNCTION t() RETURNS TABLE(LIKE t) AS $$ BEGIN RETURN QUERY SELECT id, code FROM t ORDER BY id; END $$ STABLE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT * FROM t(); ``` ```text id | code ----+------ 1 | One! 2 | Two (2 rows) ``` Another option is to return values row by row. ```sql => CREATE FUNCTION days_of_week() RETURNS SETOF text AS $$ BEGIN FOR i IN 7 .. 13 LOOP RETURN NEXT to_char(to_date(i::text,'J'),'TMDy'); END LOOP; END; $$ STABLE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ```sql => SELECT * FROM days_of_week() WITH ORDINALITY; ``` ```text days_of_week | ordinality --------------+------------ Mon | 1 Tue | 2 Wed | 3 Thu | 4 Fri | 5 Sat | 6 Sun | 7 (7 rows) ``` Why is this function declared STABLE, not IMMUTABLE? At first sight, it seems that repeat function calls will return the same value, it is still affected by the current locale: ```text => SET lc_time = 'ru_RU.UTF8'; ``` ```text SET ``` ```sql => SELECT * FROM days_of_week() WITH ORDINALITY; ``` ```text days_of_week | ordinality --------------+------------ Пн | 1 Вт | 2 Ср | 3 Чт | 4 Пт | 5 Сб | 6 Вс | 7 (7 rows) ``` The following example shows a mixed approach: create a function that returns a list of routines that belong to its schema. It uses PG\_ROUTINE\_OID diagnostics item to get its oid: ## Page 17: Page 17 {#source-page-017} ```sql => CREATE FUNCTION where_am_i() RETURNS TABLE(name text, isitme text) AS $$ DECLARE ``` ```sql my_oid oid; schema_oid oid; BEGIN GET DIAGNOSTICS my_oid := PG_ROUTINE_OID; -- schema oid schema_oid := pronamespace FROM pg_proc WHERE oid = my_oid; -- Header name := '=== Schema: ' || schema_oid::regnamespace; RETURN NEXT; -- header with schema name -- list of routines RETURN QUERY SELECT proname::text, CASE WHEN oid = my_oid THEN 'It''s me!' END FROM pg_proc WHERE pronamespace = schema_oid ORDER BY 1; END $$ STABLE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` Run the function: ```sql => SELECT * FROM where_am_i(); ``` ```text name | isitme --------------------+---------- === Schema: public | bar | baz | bugs | days_of_week | do_something | foo | qux | t | where_am_i | It's me! (10 rows) ``` ## Page 18: Takeaways {#source-page-018} **PL/pgSQL is closely integrated with SQL** - procedural code can contain queries - (provided as expressions or as separate commands) - queries can include variables - you can get query results and check query status - Naming ambiguities are an issue ## Page 19: Practice {#source-page-019} 1. Create the add\_author function for adding new authors. The function must take three parameters (last name, first name, middle name) and return the ID of the added author. - Check that the application allows adding new authors. 2. Create the buy\_book function for buying books. The function takes the book ID as a parameter and reduces the number of such books by one. There is no return value. - Check that the Store tab now allows buying books. ### Speaker notes 1. ```text FUNCTION add_author(last_name text, first_name text, middle_name text) RETURNS integer ``` 3. ```text FUNCTION buy_book(book_id integer) RETURNS void ``` You can notice that the application allows selling more books than there are in store. If the total number of books were stored in a table column, adding a CHECK constraint would be a good and simple solution. But our implementation calculates the number of books, so we are not going to address this until we get to the Triggers lesson of the course. ## Page 20: 1. The add_author Function {#source-page-020} ```sql => CREATE FUNCTION add_author( last_name text, first_name text, middle_name text ) RETURNS integer AS $$ DECLARE author_id integer; BEGIN INSERT INTO authors(last_name, first_name, middle_name) VALUES (last_name, first_name, middle_name) RETURNING authors.author_id INTO author_id; RETURN author_id; END $$ VOLATILE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ### 2. The buy\\\_book Function ```sql => CREATE FUNCTION buy_book(book_id integer) RETURNS void AS $$ BEGIN INSERT INTO operations(book_id, qty_change) VALUES (book_id, -1); END $$ VOLATILE LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ## Page 21: Practice+ {#source-page-021} Create a game in which the server tries to guess the animal chosen by the user by asking a series of clarifying questions that can be answered with a “yes” or a “no”. If the server suggests a wrong answer, it requests the user to provide the animal name, and then asks how is it different from its suggestion. This new information is registered for use in the next games. 1. Create a table for data representation. 2. Design an interface and implement all the necessary functions. 3. Check the implementation. ### Speaker notes A possible dialog (between humans): - Is it a mammal? - Yes. - Is it an elephant? - No. - I give up. What is it? - It’s a whale. - How is it different from an elephant? - It lives in water. 1. The data falls nicely into a binary tree structure. Inner nodes store questions, while leaf nodes store animal names. One of the child nodes corresponds to the answer “yes”, the other to the answer “no”. 2. Between the function calls, you need to pass the information about the last node of the tree the user gets to (the context of the dialog). For example, you could have the following functions: ● start the game (no input context): FUNCTION start\_game(OUT context integer, OUT question text) ● continue the game (get the answer, ask the next question): FUNCTION continue\_game( INOUT context integer, IN answer boolean, OUT you\_win boolean, OUT question text) ● end the game (add information about another animal): FUNCTION end\_game( IN context integer, IN name text, IN question text) RETURNS void ## Page 22: 1. Table {#source-page-022} ```sql => CREATE DATABASE plpgsql_queries; ``` ```sql CREATE DATABASE ``` ```sql => \c plpgsql_queries ``` ```text You are now connected to database "plpgsql_queries" as user "student". ``` ```sql => CREATE TABLE animals( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, yes_id integer REFERENCES animals(id), no_id integer REFERENCES animals(id), name text ); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO animals(name) VALUES ('mammals'), ('elephant'), ('turtle'); ``` ```sql INSERT 0 3 ``` ```sql => UPDATE animals SET yes_id = 2, no_id = 3 WHERE id = 1; ``` ```sql UPDATE 1 ``` ```sql => SELECT * FROM animals ORDER BY id; ``` ```text id | yes_id | no_id | name ----+--------+-------+---------- 1 | 2 | 3 | mammals 2 | | | elephant 3 | | | turtle (3 rows) ``` The first row represents the root of the tree. ### 2. Functions ```sql => CREATE FUNCTION start_game( OUT context integer, OUT question text ) AS $$ DECLARE root_id CONSTANT integer := 1; BEGIN SELECT id, name||'?' INTO context, question FROM animals WHERE id = root_id; END $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ## Page 23: Page 23 {#source-page-023} ```sql => CREATE FUNCTION continue_game( INOUT context integer, IN answer boolean, OUT you_win boolean, OUT question text ) AS $$ DECLARE new_context integer; BEGIN SELECT CASE WHEN answer THEN yes_id ELSE no_id END INTO new_context FROM animals WHERE id = context; ``` ```sql IF new_context IS NULL THEN you_win := NOT answer; question := CASE WHEN you_win THEN 'I concede' ELSE 'You lose' END; ELSE SELECT id, null, name||'?' INTO context, you_win, question FROM animals WHERE id = new_context; END IF; END $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ```sql => CREATE FUNCTION end_game( IN context integer, IN name text, IN question text ) RETURNS void AS $$ DECLARE new_animal_id integer; new_question_id integer; BEGIN INSERT INTO animals(name) VALUES (name) RETURNING id INTO new_animal_id; INSERT INTO animals(name) VALUES (question) RETURNING id INTO new_question_id; UPDATE animals SET yes_id = new_question_id WHERE yes_id = context; UPDATE animals SET no_id = new_question_id WHERE no_id = context; UPDATE animals SET yes_id = new_animal_id, no_id = context WHERE id = new_question_id; END $$ LANGUAGE plpgsql; ``` ```sql CREATE FUNCTION ``` ### 3. Example of a Game Session Try the word "whale". ```sql => SELECT * FROM start_game(); ``` ```text context | question ---------+---------- 1 | mammals? (1 row) ``` ```sql => SELECT * FROM continue_game(1,true); ``` ```text context | you_win | question ---------+---------+----------- 2 | | elephant? (1 row) ``` ```sql => SELECT * FROM continue_game(2,false); ``` ## Page 24: Page 24 {#source-page-024} ```text context | you_win | question ---------+---------+----------- 2 | t | I concede (1 row) ``` ```sql => SELECT * FROM end_game(2,'whale','lives in water'); ``` ```text end_game ---------- (1 row) ``` This is the table now: ```sql => SELECT * FROM animals ORDER BY id; ``` ```text id | yes_id | no_id | name ----+--------+-------+---------------- 1 | 5 | 3 | mammals 2 | | | elephant 3 | | | turtle 4 | | | whale 5 | 4 | 2 | lives in water (5 rows) ``` Try "whale" again. ```sql => SELECT * FROM start_game(); ``` ```text context | question ---------+---------- 1 | mammals? (1 row) ``` ```sql => SELECT * FROM continue_game(1,true); ``` ```text context | you_win | question ---------+---------+----------------- 5 | | lives in water? (1 row) ``` ```sql => SELECT * FROM continue_game(5,true); ``` ```text context | you_win | question ---------+---------+---------- 4 | | whale? (1 row) ``` ```sql => SELECT * FROM continue_game(4,true); ``` ```text context | you_win | question ---------+---------+---------- 4 | f | You lose (1 row) ``` ```sql => \c postgres ``` ```text You are now connected to database "postgres" as user "student". ``` ```sql => DROP DATABASE plpgsql_queries; ``` ```sql DROP DATABASE ```