PostgreSQL PL pgSQL

Table of Contents Why ? Why not ? General Samples Anonymous block Hello, world Variable Procedure Hello, world Variable Function Test PgTAP install Build Share code Install in DB test structure test data test script test execution test data production code test code pg_prove transaction

Why ?

Official docs The best using of PL/pgSQL is like glue for SQL statements.

SQL is the language PostgreSQL and most other relational databases use as query language. It's portable and easy to learn. But every SQL statement must be executed individually by the database server. That means that your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. All this incurs interprocess communication and will also incur network overhead if your client is on a different machine than the database server.

Think about C..

Why not ?

Rationale here

Do not use for:

• interactive application
• massive data manipulation in memory (eg. array) instead of DB tables
• I/O operations
• recursive calls

General

List:

• call a function: SELECT FUNCTION_NAME ( parameter_name : paramater_value);
• call a procedure: CALL ( parameter_name : paramater_value);

Samples

Anonymous block

Hello, world

DO $$ 
BEGIN    
   RAISE NOTICE 'Hello, world'
END $$;

Variable

DO $$ 
<<first_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;
END first_block $$;

Procedure

Hello, world

CREATE PROCEDURE hello(name VARCHAR)
    LANGUAGE PLPGSQL
AS
$$
DECLARE
BEGIN 
   RAISE NOTICE 'Hello %', name;
END;
$$;
-- Execution: CALL hello('world');

Variable

CREATE PROCEDURE counter()
    LANGUAGE PLPGSQL
AS
$$
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;   
END;
$$;
-- Execution: CALL counter();

Function

DROP FUNCTION IF EXISTS hello;
CREATE FUNCTION hello( p_name TEXT) RETURNS TEXT
    LANGUAGE PLPGSQL
AS
$$
DECLARE
    message TEXT;
BEGIN
    message := 'Hello, ' || p_name;
	RETURN message;
END;
$$;

-- Execution: SELECT hello(p_name := 'world');

Test

Look here first

PgTAP

PgTAP is an implementation of Test Anything Protocol (TAP) in PG/PL SQL

For best practises, look here

install

From here

Build

Create a database named sandbox

sudo mkdir /usr/pgsql_src
wget http://api.pgxn.org/dist/pgtap/1.1.0/pgtap-1.1.0.zip
unzip pgtap-1.1.0.zip && cd pgtap*
make
make installcheck PGUSER=postgres
sudo make install

Share code

sudo cp sql/pgtap.sql `pg_config --sharedir`/contrib

Install in DB

Here, install in sandbox

psql -d sandbox -f `pg_config --sharedir`/contrib/pgtap.sql 

test structure

From here

Below you'll find a successful test execution. If you want to do TDD style, omit the test data, create test script, and start executing test.

test data

Create test data: execute DDL on sandbox database

CREATE TABLE public.agents
(
  name text,
  id integer NOT NULL,
  CONSTRAINT agents_pkey PRIMARY KEY (id),
  CONSTRAINT unique_name UNIQUE (name)
);
CREATE INDEX idx_agents_name
  ON public.agents

test script

Create test script in test.md

BEGIN; -- Start the transaction

-- Plan count should match the number of tests. If it does
-- not then pg_prove will fail the test
SELECT plan(6);

-- Run the tests.

-- Columns
SELECT columns_are('agents', ARRAY[ 'id', 'name' ]);

SELECT col_type_is('agents', 'name', 'text', 'name column type is -- text' );

-- Keys
SELECT has_pk('agents', 'Has a Primary Key' );
SELECT col_is_pk( 'agents', 'id', 'Column is Primary Key -- id' );

-- Indexes
SELECT has_index( 'agents', 'idx_agents_name', 'name', 'Column has index -- name' );

-- Constraints
SELECT col_is_unique( 'agents', 'name', 'Name columns has unique constraint' );

-- Finish the tests and clean up.
SELECT * FROM finish();
ROLLBACK; -- We don’t commit the transaction, this means tests don’t change the database in anyway

test execution

Execute pg_prove --dbname sandbox test.md You should get

test.md .. ok
All tests successful.
Files=1, Tests=6,  0 wallclock secs ( 0.01 usr  0.01 sys +  0.04 cusr  0.00 csys =  0.06 CPU)
Result: PASS

test data

From here

production code

CREATE sequence people_id_seq;
CREATE TABLE people (id INTEGER DEFAULT NEXTVAL('people_id_seq'), full_name TEXT);

CREATE OR REPLACE FUNCTION capitalize_fullname() RETURNS TRIGGER AS $BODY$
BEGIN
NEW.full_name := INITCAP( NEW.full_name );
RETURN NEW;
END;
$BODY$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER capitalize_fullname
BEFORE INSERT OR UPDATE ON people
FOR EACH ROW EXECUTE PROCEDURE capitalize_fullname();

test code

Test insert does not fail

SELECT lives_ok(
'INSERT INTO people (full_name) VALUES ($$abc$$)',
'Inserting to people should work, and not raise exception.'
);
 
SELECT finish();
ROLLBACK;

Test data are correctly updated

BEGIN;
SELECT plan(3);
 
INSERT INTO people (full_name) VALUES ( 'abc' );
SELECT IS( full_name, 'Abc', 'Capitalization of >Abc<' ) FROM people WHERE id = currval( 'people_id_seq' );
 
INSERT INTO people (full_name) VALUES ( 'DEPESZ' );
SELECT IS( full_name, 'Depesz', 'Capitalization of >Depesz<' ) FROM people WHERE id = currval( 'people_id_seq' );
 
INSERT INTO people (full_name) VALUES ( 'HuBeRt dEPesZ LubaCZEWski' );
SELECT IS( full_name, 'Hubert Depesz Lubaczewski', 'Capitalization of >Hubert Depesz Lubaczewski<' ) FROM people WHERE id = currval( 'people_id_seq' );
 
SELECT finish();
ROLLBACK;

Failing test

BEGIN;
 SELECT plan(1);
 INSERT INTO people (full_name) VALUES ( 'Louis d''Orléans' );
 
     -- IS (actual,     expected,          description)
 SELECT IS( full_name, 'Louis d''Orléans', 'Capitalization of >Louis d''Orléans<' ) FROM people WHERE id = urrval( 'people_id_seq' );
 SELECT finish();
ROLLBACK;

pg_prove

man page

transaction

More here