💾 Archived View for sdf.org › rsdoiel › blog › 2022 › 08 › 24 › plpgsql-quick-intro.gmi captured on 2023-06-16 at 16:43:18. Gemini links have been rewritten to link to archived content
⬅️ Previous capture (2023-01-29)
-=-=-=-=-=-=-
PL/pgSQL is a procedure language extended from SQL. It adds flow control and local state for procedures, functions and triggers. Procedures, functions and triggers are also the compilation unit. Visually PL/pgSQL looks similar to the MySQL or ORACLE counter parts. It reminds me of a mashup of ALGO and SQL. Like the unit of compilation, the unit of execution is also procedure, function or trigger.
The Postgres documentation defines and explains the PL/pgSQL[1] and how it works. This document is just a quick orientation with specific examples to provide context.
1: https://www.postgresql.org/docs/14/plpgsql.html
Here is a "helloworld" procedure definition.
CREATE PROCEDURE helloworld() AS $
DECLARE
BEGIN
RAISE NOTICE 'Hello WORLD!';
END;
$ LANGUAGE plpgsql;
Let's take a look this line by line.
1. CREATE PROCEDURE defines the procedure and the starting and ending delimiter for the procedure (e.g. AS $ the procedure's text ends when $ is encountered an second time.
2. DECLARE is the block where you would declare the variables used in the procedure, we have none in this example
3. The BEGIN starts the actual procedure instructions
4. The RAISE NOTICE line is how you can display output to the console when the procedure is run
5. The END completes the procedure definition
6. the $ LANGUAGE plpgsql; concludes the text defining the procedure and tells the database engine that procedure is written in PL/pgSQL.
We can run the procedure using the "CALL" query.
CALL helloworld()
NOTE: If you want to change the procedure you can "DROP" it first otherwise you'll get an error that it already exists.
DROP PROCEDURE helloworld;
SQL procedures are generally stored in the RDBMs in database environment. You can think of them as records in the system's database. Procedures and functions are created and can be dropped. While they can be manually typed in the database's shell it is easier to maintain them in plain text files outside the RDBM environment.
1. Write the procedure in a text file.
2. Load the text file (e.g. FILENAME) into Postgres a. outside the Postgres shell use psql -f FILENAME b. inside the Postgres shell used \i FILENAME
3. Call the procedure to test it
To turn these steps into a look I use a "CREATE OR REPLACE" statement and be able to reload the updated procedure easier see 43.12. Tips for Developing in PL/pgSQL[2]. Note in the revised example the "-- " lines are comments.
2: https://www.postgresql.org/docs/14/plpgsql-development-tips.html
Our revised helloworld[3].
--
-- Create (or replace) the new "helloworld" procedure.
-- NOTE: this can be run with "CALL"
--
CREATE OR REPLACE PROCEDURE helloworld() AS $
DECLARE
BEGIN
RAISE NOTICE 'Hello World!';
END;
$ LANGUAGE plpgsql;
hithere[4] is similar to our helloworld example except it is a function that takes a parameter of the person's name. The function returns a "VARCHAR", so this should work as part of a select statement.
--
-- This is a "Hi There" function. The function takes
-- a single parameter and forms a greeting.
--
CREATE OR REPLACE FUNCTION hithere(name varchar) RETURNS varchar AS $
DECLARE
greeting varchar;
BEGIN
IF name = '' THEN
greeting := 'Hi there!';
ELSE
greeting := 'Hello ' || name || '!';
END IF;
RETURN greeting;
END;
$ LANGUAGE plpgsql;
Giving it a try.
SELECT hithere('Mojo Sam');
5: https://www.postgresql.org/docs/14/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS