PL/pgSQL Basics

The Postgres database has a procedural language called PL/PGSQL which can save you db round-trips and do all sorts of optimizations on the db layer. In this post I’m going to be showing 10 sql files as we progress on PL/PGSQL’s basics.

First here’s a basic hello world function:

-- hello-world.sql

CREATE FUNCTION hello_world()
RETURNS VOID AS $$
BEGIN

  RAISE NOTICE 'Hello World';

END; $$ LANGUAGE plpgsql;

Here we have a basic function with name hello_world, it returns nothing (VOID) and the statement body contains a single line which just raises the ‘Hello World’ text, its like the equivalent of echo or print if you will.

-- sum.sql

CREATE FUNCTION sum(a INT, b INT)
RETURNS INT AS $$
BEGIN

   RETURN a + b;

END; $$ LANGUAGE plpgsql;

Here we have a sum function which accepts two integers and also returns an integer. Notice how the parameters are declared, should be name then followed by its type. If your function returns something you also need to specify its type after RETURNS.

-- greet.sql

CREATE OR REPLACE FUNCTION greet(name VARCHAR)
RETURNS VARCHAR AS $$
BEGIN

  RETURN 'Hello ' || name;

END; $$ LANGUAGE plpgsql;

The only thing that’s new here is the body, we concatenate strings using || operator.

-- swap.sql

CREATE FUNCTION swap(a INT, b INT)
RETURNS RECORD AS $$
DECLARE

  tmp INT;

BEGIN

  tmp := a;
  a := b;
  b := tmp;
  RETURN (a, b);

END; $$ LANGUAGE plpgsql;

Here we have the classic swap implementation, we declare variable via DECLARE block. Also we’re returning a set but the return type is RECORD. Its a special generic type which can hold any type, here we’re passing set to it.

-- status.sql

CREATE OR REPLACE FUNCTION status(status INT)
RETURNS VARCHAR AS $$
BEGIN

  IF status = 100 THEN
    RETURN 'DRAFT';
  ELSIF status > 100 AND status < 400 THEN
    RETURN 'PROCESSING';
  ELSIF status = 400 THEN
    RETURN 'RUNNING';
  ELSIF status > 400 THEN
    RETURN 'STOPPED';
  ELSE
    RETURN 'INVALID';
  END IF;

END; $$ LANGUAGE plpgsql;

This is how you do conditions, notice that equal comparison uses a single = (equal) sign.

-- query-single.sql

CREATE OR REPLACE FUNCTION get_contact(contactid BIGINT)
RETURNS contact AS $$
DECLARE

  result contact;

BEGIN

  SELECT * INTO result FROM contact WHERE id = contactid;
  RETURN result;

END; $$ LANGUAGE plpgsql;

This is our first example on how to use queries with it, we’re assigning the result on to result variable. Notice how the return type and result type is contact. This is the name of the table actually and if you use it as a type, it means that variable can hold a row type for that table.

-- result-existence.sql

CREATE OR REPLACE FUNCTION get_contact(contactid BIGINT)
RETURNS contact AS $$
DECLARE

  result contact;
  count INT;

BEGIN

  SELECT * INTO result FROM contact WHERE id = contactid;
  GET DIAGNOSTICS count := ROW_COUNT;
  IF NOT FOUND THEN
    RAISE NOTICE 'not found!';
  ELSE
    RAISE NOTICE 'found %!', count;
  END IF;
  RETURN result;

END; $$ LANGUAGE plpgsql;

PL/PGSQL has a bunch of special variables and FOUND is just one of them. It holds a boolean value of wether or not our query returned anything. We can also retrieve the number of rows affected via ROW_COUNT which is another one of those special variables.

-- return-next.sql

CREATE OR REPLACE FUNCTION get_numbers()
RETURNS setof INT AS $$
DECLARE

  numbers INT[] = ARRAY[1, 2, 3];

BEGIN

  FOR i IN 1 .. array_upper(numbers, 1) LOOP
    RETURN NEXT numbers[i];
  END LOOP;

END; $$ LANGUAGE plpgsql;

Then we introduce arrays, you can do inline initialization by using ARRAY[…] form. One thing to keep in mind is that array’s index starts at 1 instead of 0, and you can get the number of elements on the built-in array function array_upper. We also introduce RETURN NEXT, what it does is it doesn’t actually exit the function but just appends the item to the implicit output handler of our function. Then once the function ends, it returns this altogether.

So what about queryset?

-- return-query.sql

CREATE OR REPLACE FUNCTION get_contacts()
RETURNS SETOF contact AS $$
BEGIN

  RETURN QUERY SELECT * FROM contact;

END; $$ LANGUAGE plpgsql;

Here we introduce RETURN QUERY which lets us return a cursor to the queryset. Notice that the return type is SETOF contact, its required when using RETURN NEXT or RETURN QUERY as the rows returned will contain the set of columns for that table, contacts in this case.

-- cursor.sql

CREATE OR REPLACE FUNCTION get_contacts()
RETURNS SETOF contact AS $$
DECLARE

  pointer CURSOR
    FOR SELECT * FROM contact;

BEGIN

  FOR row IN pointer LOOP
    RETURN NEXT row;
  END LOOP;

END; $$ LANGUAGE plpgsql;

Finally as our last example, here’s an explicit usage of a bound cursor. Here pointer is a cursor pointing to its query’s result which we can iterate on a normal FOR loop. Again we use SETOF contact as return type to conform on the set of columns present on each row.

Note that while all we’ve shown are just stand alone functions, a function can call another one, can be called in a trigger and eveywhere else where a built-in postgres function can be called. Now go forth and try it!

./marconijr

PL/pgSQL Basics