Summary: in this tutorial, you’ll learn about PL/pgSQL blocks, how to create and execute some blocks.Overview of a PL/pgSQL BlockPL/pgSQL is a blocked structure programming language. PL/pgSQL organizes code into blocks.Here’s the syntax of a block:[ label ] [ DECLARE declarations ] BEGIN statements END [ label ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)A block has two main sections:Declaration:The declaration section is optional. It is where you declare variables, constants, and cursors.Body:The body section is required. It is where you put the logic of the block, such as SQL statements.A block may include an optional label appearing at the beginning and end. The label is necessary only when you use statements like theEXITstatement or to qualify the variable names declared in the block.PL/pgSQL Block ExampleThe following example shows how to create a block that displays a messageHello, World:DO $$ BEGIN RAISE NOTICE %, Hello, World; END; $$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)OutputNOTICE: Hello, WorldCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)How it works:TheDOstatement executes the PL/pgSQL block.We use the PL/pgSQL block as a dollar-quoted string constant, which starts with$$and ends with$$. This helps avoid escaping quotations and special characters in the PL/pgSQL code.A block starts with theBEGINkeyword and ends with the keywordEND. Note thatBEGINdoesn’t mean start a transaction.TheRAISE NOTICEstatement displays a notice when you execute the block:RAISE NOTICE %, Hello, World;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)TheRAISE NOTICEstatement starts with a placeholder%, followed by a comma, and the value for that placeholder. The block does not include a declaration section in this example.Executing the Block in psqlFirst, connect to PostgreSQL using the psql tool:psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)The psql tool will prompt you to enter the password for the postgres user:Password for user postgres:Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)You must enter a valid password and press the Enter (or Return) key.Second, copy the PL/pgSQL code above and paste it into the psql tool:postgres# DO postgres-# $$ postgres$# BEGIN postgres$# RAISE NOTICE %, Hello, World; postgres$# END; postgres$# $$; NOTICE: Hello, World DOCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)OutputNOTICE: Hello, World DOCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Executing the PL/pgSQL Block Using pgAdmin 4First, launch the pgAdmin4 tool and open the inventory database.Second, open the query tool by following the menu pathTools Query tool:Third, copy and paste the PL/pgSQL block into the query tool:Finally, click theExecute Scriptbutton (or press theF5keyboard shortcut).PL/pgSQL Block with Declaration ExampleThe following example shows how to define a block with a declaration section.DO $$ DECLARE name VARCHAR Joe; BEGIN RAISE NOTICE Hello %, name; END; $$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)OutputNOTICE: Hello JoeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In this example, we add the declaration section and declare a variablenamewithVARCHARtype and valueJoe:DECLARE name VARCHAR Joe;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)To declare a variable, you provide the name, data type, and initial value:name VARCHAR Joe;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)We substitute thenamevariable with the placeholder%in theHello %string in theRAISE NOTICEstatement:RAISE NOTICE Hello %, name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)The output will be like this:NOTICE: Hello JoeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)PL/pgSQL SubblocksIn PL/pgSQL, blocks can be nested within the body of other blocks. Blocks nested inside other blocks are called subblocks. Blocks that contain nested blocks are called outer blocks:Block nesting allows you to organize the blocks into smaller, more manageable trunks. For example:DO $$ DECLARE total_quantity INT 0; BEGIN DECLARE safety_stock INT 10; on_hand_qty INT 100; BEGIN total_quantity safety_stock on_hand_qty; END; RAISE NOTICE The total quantity is %, total_quantity; END; $$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)OutputNOTICE: The total quantity is 110Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)How it works:In the main block, we declare and initializetotal_quantityto 0:DECLARE total_quantity INT 0;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In the subblock, we declare and initializesafety_stockto 10 andon_hand_qtyto 100:DECLARE safety_stock INT 10; on_hand_qty INT 100;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Additionally, we calculate the total quantity by addingsafety_stockandon_hand_qtyand assigning the result tototal_quantity:total_quantity safety_stock on_hand_qty;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)After the subblock, we display the total quantity in the main block:RAISE NOTICE The total quantity is %, total_quantity;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)SummaryPL/pgSQL is a blocked structure programming language.A block includes two main sections: declaration and body.The declaration section is optional, while the body section is required.Blocks can be nested within the body section of other blocks.
