12 Lesson 12: PL/pgSQL / Query Execution

Page 1: PL/pgSQL / Query Execution

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

• Using SQL Commands in PL/pgSQL Code
• Eliminating Naming Ambiguities
• Checking Command Status
• Set Returning Functions

Page 3: Returning Nothing

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).

https://postgrespro.com/docs/postgresql/16/plpgsql-statements#PLPGSQLS TATEMENTS-GENERAL-SQL

Page 4: Returning Nothing

If the result of the query is not needed, replace SELECT with PERFORM:

=> CREATE FUNCTION do_something() RETURNS void
AS $$
BEGIN
    RAISE NOTICE 'Something has been done.';
END
$$ LANGUAGE plpgsql;

CREATE FUNCTION

=> DO $$
BEGIN
    PERFORM do_something();
END
$$;

NOTICE:  Something has been done.
DO

Almost any SQL command that returns no rows can be used in PL/pgSQL without modification:

=> 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
$$;

DO

Transaction Control in Procedures

Procedures (and anonymous blocks) written in PL/pgSQL support transaction control commands:

=> CREATE TABLE test(n integer);

CREATE TABLE

=> CREATE PROCEDURE foo()
AS $$
BEGIN
    INSERT INTO test VALUES (1);
    COMMIT;
    INSERT INTO test VALUES (2);
    ROLLBACK;
END
$$ LANGUAGE plpgsql;

CREATE PROCEDURE

=> CALL foo();

CALL

=> SELECT * FROM test;

 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.

=> BEGIN;

BEGIN

=> CALL foo(); -- error

Page 5: Page 5

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function foo() line 4 at COMMIT

=> ROLLBACK;

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:

=> CREATE OR REPLACE PROCEDURE foo()
AS $$
BEGIN
    CALL bar();
END
$$ LANGUAGE plpgsql;

CREATE PROCEDURE

=> CREATE PROCEDURE bar()
AS $$
BEGIN
    CALL baz();
END
$$ LANGUAGE plpgsql;

CREATE PROCEDURE

=> CREATE PROCEDURE baz()
AS $$
BEGIN
    COMMIT;
END
$$ LANGUAGE plpgsql;

CREATE PROCEDURE

=> CALL foo(); -- it works

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:

=> CREATE FUNCTION qux() RETURNS void
AS $$
BEGIN
    CALL bar();
END
$$ LANGUAGE plpgsql;

CREATE FUNCTION

=> SELECT qux(); -- error

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

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.

https://postgrespro.com/docs/postgresql/16/plpgsql-statements#PLPGSQL-STATEMENTS-SQL-ONEROW

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).

https://postgrespro.com/docs/postgresql/16/plpgsql-development-tips#PLPG SQL-EXTRA-CHECKS

More powerful debugging capabilities are provided by an external extension plpgsql_check (developed by Pavel Stehule).

https://github.com/okbob/plpgsql\_check

Page 7: Returning a Single Row

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:

=> CREATE TABLE t(id integer, code text);

CREATE TABLE

=> INSERT INTO t VALUES (1, 'One'), (2, 'Two');

INSERT 0 2

=> DO $$
DECLARE
    r record;
BEGIN
    SELECT id, code INTO r FROM t;
    RAISE NOTICE '%', r;
END
$$;

NOTICE:  (1,One)
DO

=> DO $$
DECLARE
    r record;
BEGIN
    UPDATE t SET code = code || '!' WHERE id = 1 RETURNING * INTO r;
    RAISE NOTICE 'Modified: %', r;
END
$$;

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):

=> SET plpgsql.extra_warnings = 'all';

SET

=> CREATE PROCEDURE bugs(INOUT a integer)
AS $$
DECLARE
    a integer;
    b integer;
BEGIN
    SELECT id INTO a, b FROM t;
END
$$ LANGUAGE plpgsql;

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:

=> CALL bugs(42);

Page 8: Page 8

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.

=> RESET plpgsql.extra_warnings;

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.

=> CREATE SCHEMA plpgsql_check;

CREATE SCHEMA

=> CREATE EXTENSION plpgsql_check SCHEMA plpgsql_check;

CREATE EXTENSION

=> SELECT * FROM plpgsql_check.plpgsql_check_function('bugs(integer)');

                        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?

=> DO $$
DECLARE
    id   integer := 1;
    code text;
BEGIN
    SELECT id, code INTO id, code
    FROM t WHERE id = id;
    RAISE NOTICE '%, %', id, code;
END
$$;

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

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:

=> 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
$$;

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:

=> DO $$
<<local>>
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
$$;

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:

=> SET plpgsql.variable_conflict = use_variable;

SET

=> 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

NOTICE:  1, One!
DO

=> RESET plpgsql.variable_conflict;  -- reset the value to default

RESET

=> SHOW plpgsql.variable_conflict;

 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

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.

https://postgrespro.com/docs/postgresql/16/plpgsql-statements#PLPGSQL-STATEMENTS-DIAGNOSTICS

Page 12: Exactly One Row

What will happen if the query returns several rows?

=> DO $$
DECLARE
    r record;
BEGIN
    SELECT id, code INTO r FROM t;
    RAISE NOTICE '%', r;
END
$$;

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:

=> SELECT * FROM t;

 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:

=> DO $$
DECLARE
    r record;
BEGIN
    UPDATE t SET code = code || '!' RETURNING * INTO r;
    RAISE NOTICE 'Modified: %', r;
END
$$;

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?

=> DO $$
DECLARE
    r record;
BEGIN
    r := (-1,'!!!');
    SELECT id, code INTO r FROM t WHERE false;
    RAISE NOTICE '%', r;
END
$$;

NOTICE:  (,)
DO

Variables will contain undefined values. It is also true for INSERT, UPDATE, and DELETE commands. For example:

=> DO $$
DECLARE
    r record;
BEGIN
    UPDATE t SET code = code || '!' WHERE id = -1
        RETURNING * INTO r;
    RAISE NOTICE 'Modified: %', r;
END
$$;

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

=> DO $$
DECLARE
    r record;
BEGIN
    SELECT id, code INTO STRICT r FROM t;
    RAISE NOTICE '%', r;
END
$$;

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

=> DO $$
DECLARE
    r record;
BEGIN
    SELECT id, code INTO STRICT r FROM t WHERE false;
    RAISE NOTICE '%', r;
END
$$;

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):

=> DO $$
DECLARE
    r record;
BEGIN
    UPDATE t SET code = code || '!' WHERE id = -1 RETURNING * INTO STRICT r;
    RAISE NOTICE 'Modified: %', r;
END
$$;

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.

=> DO $$
DECLARE
    r record;
    rowcount integer;
BEGIN
    SELECT id, code INTO r FROM t WHERE false;

    GET DIAGNOSTICS rowcount := ROW_COUNT;
    RAISE NOTICE 'rowcount = %', rowcount;
    RAISE NOTICE 'found = %', FOUND;
END
$$;

NOTICE:  rowcount = 0
NOTICE:  found = f
DO

=> DO $$
DECLARE
    r record;
    rowcount integer;
BEGIN
    SELECT id, code INTO r FROM t;

    GET DIAGNOSTICS rowcount := ROW_COUNT;
    RAISE NOTICE 'rowcount = %', rowcount;
    RAISE NOTICE 'found = %', FOUND;
END
$$;

NOTICE:  rowcount = 1
NOTICE:  found = t
DO

Page 14: Page 14

Page 15: Set Returning Functions

Query rows

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.

https://postgrespro.com/docs/postgresql/16/plpgsql-control-structures#PLP GSQL-STATEMENTS-RETURNING

Page 16: Set Returning Functions

Here is an example of a set-returning function written in PL/pgSQL:

=> CREATE FUNCTION t() RETURNS TABLE(LIKE t)
AS $$
BEGIN
    RETURN QUERY SELECT id, code FROM t ORDER BY id;
END
$$ STABLE LANGUAGE plpgsql;

CREATE FUNCTION

=> SELECT * FROM t();

 id | code
----+------
  1 | One!
  2 | Two
(2 rows)

Another option is to return values row by row.

=> 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;

CREATE FUNCTION

=> SELECT * FROM days_of_week() WITH ORDINALITY;

 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:

=> SET lc_time = 'ru_RU.UTF8';

SET

=> SELECT * FROM days_of_week() WITH ORDINALITY;

 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

=> CREATE FUNCTION where_am_i() RETURNS TABLE(name text, isitme text)
AS $$
DECLARE

    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;

CREATE FUNCTION

Run the function:

=> SELECT * FROM where_am_i();

        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

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

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

FUNCTION add_author(last_name text, first_name text, middle_name
text)
RETURNS integer

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

=> 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;

CREATE FUNCTION

2. The buy\_book Function

=> 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;

CREATE FUNCTION

Page 21: Practice+

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

=> CREATE DATABASE plpgsql_queries;

CREATE DATABASE

=> \c plpgsql_queries

You are now connected to database "plpgsql_queries" as user "student".

=> 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
);

CREATE TABLE

=> INSERT INTO animals(name) VALUES
    ('mammals'), ('elephant'), ('turtle');

INSERT 0 3

=> UPDATE animals SET yes_id = 2, no_id = 3 WHERE id = 1;

UPDATE 1

=> SELECT * FROM animals ORDER BY id;

 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

=> 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;

CREATE FUNCTION

Page 23: Page 23

=> 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;

    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;

CREATE FUNCTION

=> 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;

CREATE FUNCTION

3. Example of a Game Session

Try the word “whale”.

=> SELECT * FROM start_game();

 context | question
---------+----------
       1 | mammals?
(1 row)

=> SELECT * FROM continue_game(1,true);

 context | you_win | question
---------+---------+-----------
       2 |         | elephant?
(1 row)

=> SELECT * FROM continue_game(2,false);

Page 24: Page 24

 context | you_win | question
---------+---------+-----------
       2 | t       | I concede
(1 row)

=> SELECT * FROM end_game(2,'whale','lives in water');

 end_game
----------
(1 row)

This is the table now:

=> SELECT * FROM animals ORDER BY id;

 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.

=> SELECT * FROM start_game();

 context | question
---------+----------
       1 | mammals?
(1 row)

=> SELECT * FROM continue_game(1,true);

 context | you_win |    question
---------+---------+-----------------
       5 |         | lives in water?
(1 row)

=> SELECT * FROM continue_game(5,true);

 context | you_win | question
---------+---------+----------
       4 |         | whale?
(1 row)

=> SELECT * FROM continue_game(4,true);

 context | you_win | question
---------+---------+----------
       4 | f       | You lose
(1 row)

=> \c postgres

You are now connected to database "postgres" as user "student".

=> DROP DATABASE plpgsql_queries;

DROP DATABASE