08 Stored PL/pgSQL

Stored PL/pgSQL

Outline

Stored PL/pgSQL ? What is it ?
Applications
CREATE OR REPLACE PROCEDURE statement
CREATE OR REPLACE FUNCTION statement
GRANT statement revisited

it is possible to pre-compile PL/pgSQL procedures and functions and store the results of pre-compilation into a data dictionary. It is similar to storing binary code obtained from compilation of modules written in procedural programming language into libraries or packages.

PL/pgSQL procedures and functions stored in data dictionary can be called from within SQL statements and from within database applications. It possible to pass the values of actual parameters to stored PL/pgSQL functions and procedures.

A syntactical structure of stored PL/pgSQL procedure and function is identical to a syntactical structure of ordinary not-stored PL/pgSQL procedure or function.

Stored procedures can be called and processed with CALL statement of SQL. A call of a stored procedure is exactly the same as a call of not-stored procedure. CALL keyword is followed by a name of procedure followed by a list of actual parameters.

Stored functions can be called and processed in SQL statements wherever a function can be used, for example as row a function in SELECT statement.

The first reason is "extensibility". Stored procedures functions can be used to extend the functionality of data retrieval and data manipulation statements of SQL.

The second reason is "reusability". Stored procedures and functions can be used to eliminate duplication of code in the database applications.

Stored PL/pgSQL ? What is it

Stored PL/pgSQL means PL/pgSQL procedures and PL/pgSQL functions pre-compiled and stored in a data dictionary ready to be processed
Stored procedures and functions can be referenced or called any number of times by multiple applications processing the relational tables
Stored procedures and functions can accept parameters when processed (called)
Stored procedures can be called and processed with CALL statement
Stored functions can be called and processed within SQL statement wherever a function can be used, for example as a row function in SELECT statement
Stored procedures and stored functions can be used to extend the functionality of data retrieval and data manipulation statements of SQL (extensibility) and to eliminate duplication of code in the database applications (reuseability)

Stored procedures and functions can be created with SQL statements: CREATE OR REPLACE PROCEDURE and CREATE OR REPLACE FUNCTION.

A code of stored procedure/function is the only argument of both statements. Of course, it is not recommended to process both statement in online mode in front of SQL prompt. The best idea is to create SQL script with one or more statements that store PL/pgSQL procedures in. a data dictionary,

If a procedure/function does not exist then processing of CREATE OR REPLACE ... statement pre-compiles procedure/functions and if no syntax errors are detected a pre-compiled code is stored in data dictionary.

If a procedure/function is already recorded in a data dictionary then processing of CREATE OR REPLACE ... statement pre-compiles procedure/functions and if no syntax errors are detected a anew pre-compiled code replaces the old one.

Stored PL/pgSQL ? What is it

Stored procedures and functions can be created with SQL statements:
- CREATE OR REPLACE PROCEDURE
- CREATE OR REPLACE FUNCTION

Stored PL/pgSQL

Outline

Stored PL/pgSQL ? What is it ?
Applications
CREATE OR REPLACE PROCEDURE statement
CREATE OR REPLACE FUNCTION statement
GRANT statement revisited

Stored PL/pgSQL procedures and functions allow for reusability of software that implements a functionality common to several database applications.

Assume that we would like to verify a logical consistency constraint that applies to a relational table COURSE. The constraint must be verified in a number of database applications: Application 1, Application 2, ... Application x. Assume that verification of the constraint is separately implemented in each application. Then, we get x possibly different implementations in x different pieces of code. Now, imagine that the constraint must be slightly modified, for example, an additional condition must be added to the constraint. It means that we have to modify implementation of the constraint in x different places. Such solution is possible, however, it is quite time consuming. It is also possible that since its original implementation in x applications the constraint was implemented in one more application we do not know about. Then, it is impossible to modify the constraint in all places it is implemented.

A better solution is to implement verification of the constraint as a stored procedure or function and provide application programmers implementing Application 1, Application 2, ... Application x with a name for stored procedure or function and with a list of formal parameters. Then we can grant the right to invoke a procedure or function to the application programmers. In such a case, any modification of the consistency constraint is limited to an implementation of a single stored procedure or function.

Applications - reusability

Stored PL/pgSQL procedures and functions allows fro extensibility of the existing implementation of SQL.

Assume that we would like to implement the following query:

"Find the names of all departments together with a list of courses offered by each department"

and we would like to display the results in a form where a name of a department is associated with a list of titles of offered courses.

DEPARTMENT NAME | LIST OF COURSES OFFERED

--------------------------------------------------------

Math Calculus Topology Logic Algebra

Comp Sci Python Java Databases

Biol

Phys Relativity Mechanics

Astro Astrology

Implementation of a special non-tabular formatting with one dynamically extensible column is not a simple task in SQL. SELECT statement of SQL transforms data in tabular 1NF format into data in tabular 1NF format.

To create a list of courses we implement a function LCOURSES( dept_name ) that returns a list of courses offered by a department whose name is a value of a parameter dept_name. A sample implementation of such function is presented in one of the coming slides.

A function LCOURSES can be applied as an ordinary row function. It returns a string with the titles of courses offered by a department passed through a parameters. If it is a row function then it can be used like any other row function included in a library of standard functions available for a given implementation of SQL. A row function is called for each row processed by SELECT statement.

We can use a function LCOURSES as a row function in SELECT statement in the following way.

SELECT dname, LCOURSES( dname )

FROM DEPARTMENT;

A function LCOURSES is called for every row retrieved from a relational table DEPARTMENT like any standard row function, for example as UPPER function.

Applications - extensibility

Find the names of all departments together with a list of courses offered by each department, display the results in the following form:

DEPARTMENT NAME | LIST OF COURSES OFFERED
--------------------------------------------------------
Math            | Calculus Topology Logic Algebra
Comp Sci        | Python Java Databases
Biol            |
Phys            | Relativity Mechanics
Astro           | Astrology

Implement a function LCOURSES( dept_name ) that returns a list of courses offered by a department whose name is a value of a parameter dept_name
Use a function LCOURSES as a row function in SELECT statement
A function LCOURSES is called for every row retrieved from a relational table DEPARTMENT like any standard row function, e.g. UPPER function

Stored PL/pgSQL

Outline

Stored PL/pgSQL ? What is it ?
Applications
CREATE OR REPLACE PROCEDURE statement
CREATE OR REPLACE FUNCTION statement
GRANT statement revisited

A statement CREATE OR REPLACE PROCEDURE compiles and stores pre-compiled code of PL/pgSQL procedure in a data dictionary.

Assume that we would like to pre-process data before insertion into a database. For example, we would like to convert a title of a course and a name of department that offers a course into an uppercase format. A conversion can be perform through a simple application of a row function UPPER. To solve the problem we implement a stored procedure INSERT_COURSE that converts the strings into uppercase format and "wraps " INSERT statement within the procedure. The values of course number, title of a subject, total number of credits and department that offers a course are passed to the procedure as actual parameters.

A stored procedure INSERT_COURSE converts the values of parameters ctitle and coffer to upper case and inserts a row into a relational table COURSE.

CREATE OR REPLACE PROCEDURE INSERT_COURSE(

cnumber IN CHAR,

ctitle IN VARCHAR,

ccredits IN NUMERIC,

coffer IN VARCHAR ) IS

BEGIN

INSERT INTO COURSE VALUES( cnumber, UPPER(ctitle), ccredits, UPPER(coffer) );

COMMIT;

END;

$$ LANGUAGE plpgsql;

Then to enforce application of INSERT_COURSE procedure by an application programmer, we remove with REVOKE statement the rights of application programmer to process INSERT statement on a relational table COURSE. Instead, we grant to an application programmer the rights to execute a procedure INSERT_COURSE. The rights to process INSERT statement on a relational table COURSE is dynamically granted to an application programmer fro a period of time when a stored procedure INSERT_COURSE is processed.

CALLstatement can be used to process a procedure INSERT_COURSE. For example, a statement

BEGIN; -- Start a transaction

CALL insert_course('CSIT115','Data Management and Security',6,'Computer Science');

COMMIT; -- Commit insertion and end a transaction

converts the strings 'Data Management and Security' and 'Computer Science' to uppercase and inserts a new row into a relational table COURSE.

CREATE OR REPLACE PROCEDURE statement

CREATE OR REPLACE PROCEDURE statement compiles and stores PL/pgSQL procedure in a data dictionary

The following stored procedure INSERT_COURSE converts the values of string parameters to upper case and inserts a row into a relational table COURSE

  
CREATE OR REPLACE PROCEDURE INSERT_COURSE( cnumber  IN CHAR,
                                           ctitle   IN VARCHAR,
                                           ccredits IN NUMERIC,
                                           coffer   IN VARCHAR) AS $$
BEGIN
  INSERT INTO COURSE VALUES( cnumber, UPPER(ctitle), ccredits, coffer);
END;
$$ LANGUAGE plpgsql;

CALL statement is used to process a procedure INSERT_COURSE

 
BEGIN;    -- Start a transaction
CALL insert_course('CSIT115','Data Management and Security',6,'Computer Science');
COMMIT;   -- Commit insertion and end a transaction

Stored PL/pgSQL

Outline

Stored PL/pgSQL ? What is it ?
Applications
CREATE OR REPLACE PROCEDURE statement
CREATE OR REPLACE FUNCTION statement
GRANT statement revisited

A statement CREATE OR REPLACE FUNCTION compiles and stores pre-compiled code of PL/pgSQL function in a data dictionary.

Assume that we would like to implement a function that returns a string of characters with the titles of courses taught by a given department (see slide 7).

The stored function has one formal parameter "dept_name" of type VARCHAR, The parameters is used to pass a name of a department. The functions returns a string of characters with the titles of courses taught by a given department.

CREATE OR REPLACE FUNCTION LCOURSES( dept_name VARCHAR )

RETURNS VARCHAR AS $$

The function declares a local variable "course_list" used to concatenate the titles of courses.

course_list VARCHAR(300);

The function declares a cursor to iterate over the titles of course offered by a given department

TITLES CURSOR FOR SELECT title FROM COURSE WHERE offered_by = dept_name;

Processing starts from assigning to "course_list" an empty string to correctly handle a case when a department offers no courses.

BEGIN

course_list := '';

a department determined through a value of actual parameter of the function. A cursor variable "course_cur_rec" is used to store a value at the current position of a cursor.

FOR course_cur_rec IN TITLES;

Next, the stored function iterates over a cursor area, one title of course per iteration and concatenates the titles separating titles with a blank. In each iteration, the titles of courses are concatenated with the present value of a variable "course_list".

LOOP

course_list := course_list || course_cur_rec.title || ' ';

END LOOP;

The function returns a value of a variable "course_list" with the titles of courses taught by a given department.

RETURN course_list;

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION statement

CREATE OR REPLACE FUNCTION statement compiles and stores PL/pgSQL function in a data dictionary

A stored function LCOURSES returns a string of characters with the titles of courses offered by a given department

 
CREATE OR REPLACE FUNCTION LCOURSES( dept_name VARCHAR )
RETURNS VARCHAR AS $$
DECLARE
  course_list VARCHAR(300);
  TITLES CURSOR FOR SELECT title FROM COURSE WHERE offered_by = dept_name;
BEGIN             
  course_list := '';
  FOR course_rec IN TITLES
  LOOP
    course_list := course_list || course_rec.title || '|';
  END LOOP;
  RETURN course_list;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION statement

A stored function LCOURSES is called as a row function in SELECT statement

 
SELECT name, LCOURSES(name)
FROM DEPARTMENT;

	    
       name       |                                            lcourses                                            
------------------+---------------------------------------------------------------
 Computer Science | Databases|Advanced Programming|Algorithms and Data Structures|
 Physics          | Mechanics|Relativity|
 Engineering      | Materials in Design|Engineering Computing and Analysis|C++|
 Mathematics      | Calculus|
 Business         | Accounting Fundamentals|Principles of Responsible Business|
 Education        | Introductory Principles of Education|
 Arts             |
 Science          | World Archaeology|

Stored PL/pgSQL

Outline

Stored PL/pgSQL ? What is it ?
Applications
CREATE OR REPLACE PROCEDURE statement
CREATE OR REPLACE FUNCTION statement
GRANT statement revisited

To process a stored procedure or function a user must have EXECUTE rights on a procedure or function. When a procedure or function is created by a given user then such user owns a procedure or or function and it automatically obtains EXECUTE rights on a procedure or function. In reality we frequently would like to share stored procedures or functions among many user. All users who do own a procedure or function and who would like to use it must be granted EXECUte right on a procedure or function.

In addition to read and write access rights it is possible to grant EXECUTE rights on stored procedures and functions

For example, a user "scott" grants execution rights on INSERT_COURSE to a user "janusz" i the following way.

GRANT EXECUTE ON INSERT_COURSE(CHAR,VARCHAR,NUMERIC,VARCHAR) TO janusz;

Now, a user "janusz" can execute a stored procedure INSERT_COURSE in the following way.

CALL scott.INSERT_COURSE(958, 'Multimedia Databases', 6, 'Comp Sci');

Please note "scott.INSERT_COURSE" in grant statement that indicates an owner of a stored procedure INSERT_COURSE.

GRANT statement revisited

In addition to read and write access rights it is possible to grant EXECUTE rights on stored procedures and functions
For example, a user scott grants execution rights on a stored procedure INSERT_COURSE to a user janusz

Now, a user janusz executes a stored procedure INSERT_COURSE

 
CALL scott.INSERT_COURSE('CSIT958', 'Multimedia Databases', 6, 'Computer Science');

GRANT statement revisited

For example, a user scott grants execution rights on a stored function LCOURSES to a user janusz

Now, a user janusz executes a stored function LCOURSES

 
SELECT name, scott.LCOURSES(name)
FROM DEPARTMENT;

References

PostgreSQL 16.1 Documentation, Chapter 43. PL/pgSQL — SQL Procedural Language
PostgreSQL Tutorial, PostgreSQL PL/pgSQL
PostgreSQL 16.1 Documentation, CREATE PROCEDURE
PostgreSQL 16.1 Documentation, CREATE FUNCTION
PostgreSQL 16.1 Documentation, GRANT
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 8 Advanced SQL, Pearson Education Ltd, 2015