Can I do it with PostgreSQL? – 6 – Server programming

Today we’ll continue this series with another topic: What does PostgreSQL provide when it comes to server programming, that is: Writing functions and triggers to support your application? In Oracle you can either use PL/SQL or Java, in MariaDB you can use stored procedures written in SQL, MS SQL Server provides Transact SQL and with DB2 you can write stored procedures in a host language or SQL.

We’ll use the same sample data as in the last post:

c postgres
drop database if exists ddl;
create database ddl;
c ddl
create table t1 ( a int, b int );
insert into t1 (a,b)
       values (generate_series(1,1000000)
              ,generate_series(1,1000000));
select count(*) from t1;
create index i1 on t1(a);
create unique index i2 on t1(b);
create view v1 as select a from t1;
alter table t1 add constraint con1 check ( a < 2000000 );
d t1

So, what can you do? To begin with you can create functions containing pure SQL commands. These are called “query language functions”. You can for example do things like this (although this function is not very useful as can you do the same by just selecting the whole table):

CREATE FUNCTION select_all_from_t1() RETURNS SETOF t1 AS '
  SELECT * 
    FROM t1;
' LANGUAGE SQL;

There are two important points here: The “LANGUAGE” part which means that the function is written in pure SQL. The keyword “SETOF” which means that we want to return a whole set of the rows of t1. Once the function is created you can use it in SQL:

(postgres@[local]:5439) [ddl] > select select_all_from_t1();
 select_all_from_t1 
--------------------
 (1,1)
 (2,2)
 (3,3)
...

When you want to do something where it does not make sense to return anything you can do it by using the “VOID” keyword:

CREATE FUNCTION update_t1() RETURNS VOID AS '
  UPDATE t1
     SET a = 5
   WHERE a < 10
' LANGUAGE SQL;

When you execute this you do not get a result:

(postgres@[local]:5439) [ddl] > select update_t1();
 update_t1 
-----------
 NULL
(1 row)
(postgres@[local]:5439) [ddl] > select count(*) from t1 where a = 5;
 count 
-------
     9
(1 row)

What about parameters? You can do this as well:

CREATE FUNCTION do_the_math(anumber1 numeric, anumber2 numeric ) RETURNS numeric AS '
  SELECT do_the_math.anumber1 * do_the_math.anumber2;
' LANGUAGE SQL;

Execute it:

(postgres@[local]:5439) [ddl] > select do_the_math(1.1,1.2);
 do_the_math 
-------------
        1.32

Another great feature is that you can have a variable/dynamic amount of input parameters when you specify the input parameter as an array:

CREATE FUNCTION dynamic_input(VARIADIC arr numeric[]) RETURNS int AS $$
    SELECT array_length($1,1);
$$ LANGUAGE SQL;

(postgres@[local]:5439) [ddl] > select dynamic_input( 1,2,3,4 );
 dynamic_input 
---------------
             4

So far for the SQL functions. What can you do when you need more than SQL? Then you can use the so called “procedural language functions”. One of these which is available by default is PL/pgSQL:

(postgres@[local]:5439) [ddl] > dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

By using PL/pgSQL you can add control structures around your SQL very much as you can do it in PL/SQL (except that you cannot create packages).

CREATE FUNCTION f1(int,int) RETURNS text AS $$
DECLARE
    t_row t1%ROWTYPE;
    result text;
BEGIN
    SELECT * 
      INTO t_row
      FROM t1
     WHERE a = 99;
    IF t_row.b > 0
    THEN
        result := 'aaaaaa';
    ELSE
        result := 'bbbbbb';
    END IF;
    RETURN result;
END;
$$ LANGUAGE plpgsql;
(postgres@[local]:5439) [ddl] > select f1(1,1);
   f1   
--------
 aaaaaa

You can also use anonymous blocks:

(postgres@[local]:5439) [ddl] > DO $$
BEGIN
  FOR i IN 1..10
  LOOP
    raise notice 'blubb';
  END LOOP;
END$$ LANGUAGE plpgsql;
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
NOTICE:  blubb
DO

Of course there is more than IF-THEN-ELSE which is documented here.

So by now we know two options to write functions in PostgreSQL. Is there more we can do? Of course: You prefer to write your functions in Perl?

(postgres@[local]:5439) [ddl] > create extension plperl;
CREATE EXTENSION
(postgres@[local]:5439) [ddl] > dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plperl  | 1.0     | pg_catalog | PL/Perl procedural language
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language


CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
    my ($x, $y) = @_;
    if (not defined $x) {
        return undef if not defined $y;
        return $y;
    }
    return $x if not defined $y;
    return $x if $x > $y;
    return $y;
$$ LANGUAGE plperl;

(postgres@[local]:5439) [ddl] > select perl_max(1,2);
 perl_max 
----------
        2

You prefer python?

(postgres@[local]:5439) [ddl] > create extension plpythonu;
CREATE EXTENSION
Time: 327.434 ms
(postgres@[local]:5439) [ddl] > dx
                        List of installed extensions
   Name    | Version |   Schema   |               Description                
-----------+---------+------------+------------------------------------------
 plperl    | 1.0     | pg_catalog | PL/Perl procedural language
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 plpythonu | 1.0     | pg_catalog | PL/PythonU untrusted procedural language

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;

(postgres@[local]:5439) [ddl] > select pymax(1,1);
 pymax 
-------
     1

… or better TcL?

(postgres@[local]:5439) [ddl] > create extension pltclu;
CREATE EXTENSION
Time: 382.982 ms
(postgres@[local]:5439) [ddl] > dx
                        List of installed extensions
   Name    | Version |   Schema   |               Description                
-----------+---------+------------+------------------------------------------
 plperl    | 1.0     | pg_catalog | PL/Perl procedural language
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 plpythonu | 1.0     | pg_catalog | PL/PythonU untrusted procedural language
 pltclu    | 1.0     | pg_catalog | PL/TclU untrusted procedural language

And these are only the default extensions. There is much more you can do:

• Java
• PHP
• R
• Ruby
• Scheme
• Unix shell

You see: PostgreSQL gives you the maximum flexibility 🙂