{"id":9456,"date":"2016-12-03T11:43:42","date_gmt":"2016-12-03T10:43:42","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-6-server-programming\/"},"modified":"2016-12-03T11:43:42","modified_gmt":"2016-12-03T10:43:42","slug":"can-i-do-it-with-postgresql-6-server-programming","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-6-server-programming\/","title":{"rendered":"Can I do it with PostgreSQL? \u2013 6 \u2013 Server programming"},"content":{"rendered":"

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<\/a> or Java<\/a>, in MariaDB you can use stored procedures written in SQL<\/a>, MS SQL Server provides Transact SQL<\/a> and with DB2 you can write stored procedures in a host language or SQL<\/a>. <\/p>\n

<\/p>\n

We’ll use the same sample data as in the last post:<\/p>\n

\nc postgres\ndrop database if exists ddl;\ncreate database ddl;\nc ddl\ncreate table t1 ( a int, b int );\ninsert into t1 (a,b)\n       values (generate_series(1,1000000)\n              ,generate_series(1,1000000));\nselect count(*) from t1;\ncreate index i1 on t1(a);\ncreate unique index i2 on t1(b);\ncreate view v1 as select a from t1;\nalter table t1 add constraint con1 check ( a < 2000000 );\nd t1\n<\/pre>\n
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):<\/p>\n
\nCREATE FUNCTION select_all_from_t1() RETURNS SETOF t1 AS '\n  SELECT * \n    FROM t1;\n' LANGUAGE SQL;\n<\/pre>\n
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:<\/p>\n
\n(postgres@[local]:5439) [ddl] > select select_all_from_t1();\n select_all_from_t1 \n--------------------\n (1,1)\n (2,2)\n (3,3)\n...\n<\/pre>\n
When you want to do something where it does not make sense to return anything you can do it by using the “VOID” keyword:<\/p>\n
\nCREATE FUNCTION update_t1() RETURNS VOID AS '\n  UPDATE t1\n     SET a = 5\n   WHERE a < 10\n' LANGUAGE SQL;\n<\/pre>\n
When you execute this you do not get a result:<\/p>\n
\n(postgres@[local]:5439) [ddl] > select update_t1();\n update_t1 \n-----------\n NULL\n(1 row)\n(postgres@[local]:5439) [ddl] > select count(*) from t1 where a = 5;\n count \n-------\n     9\n(1 row)\n<\/pre>\n
What about parameters? You can do this as well:<\/p>\n
\nCREATE FUNCTION do_the_math(anumber1 numeric, anumber2 numeric ) RETURNS numeric AS '\n  SELECT do_the_math.anumber1 * do_the_math.anumber2;\n' LANGUAGE SQL;\n<\/pre>\n
Execute it:<\/p>\n
\n(postgres@[local]:5439) [ddl] > select do_the_math(1.1,1.2);\n do_the_math \n-------------\n        1.32\n<\/pre>\n
Another great feature is that you can have a variable\/dynamic amount of input parameters when you specify the input parameter as an array:<\/p>\n
\nCREATE FUNCTION dynamic_input(VARIADIC arr numeric[]) RETURNS int AS $$\n    SELECT array_length($1,1);\n$$ LANGUAGE SQL;\n\n(postgres@[local]:5439) [ddl] > select dynamic_input( 1,2,3,4 );\n dynamic_input \n---------------\n             4\n<\/pre>\n
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<\/a>:<\/p>\n
\n(postgres@[local]:5439) [ddl] > dx\n                 List of installed extensions\n  Name   | Version |   Schema   |         Description          \n---------+---------+------------+------------------------------\n plpgsql | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(1 row)\n<\/pre>\n
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).  <\/p>\n
\nCREATE FUNCTION f1(int,int) RETURNS text AS $$\nDECLARE\n    t_row t1%ROWTYPE;\n    result text;\nBEGIN\n    SELECT * \n      INTO t_row\n      FROM t1\n     WHERE a = 99;\n    IF t_row.b > 0\n    THEN\n        result := 'aaaaaa';\n    ELSE\n        result := 'bbbbbb';\n    END IF;\n    RETURN result;\nEND;\n$$ LANGUAGE plpgsql;\n(postgres@[local]:5439) [ddl] > select f1(1,1);\n   f1   \n--------\n aaaaaa\n<\/pre>\n
You can also use anonymous blocks:<\/p>\n
\n(postgres@[local]:5439) [ddl] > DO $$\nBEGIN\n  FOR i IN 1..10\n  LOOP\n    raise notice 'blubb';\n  END LOOP;\nEND$$ LANGUAGE plpgsql;\nNOTICE:  blubb\nNOTICE:  blubb\nNOTICE:  blubb\nNOTICE:  blubb\nNOTICE:  blubb\nNOTICE:  blubb\nNOTICE:  blubb\nNOTICE:  blubb\nNOTICE:  blubb\nNOTICE:  blubb\nDO\n<\/pre>\n
Of course there is more than IF-THEN-ELSE which is documented here<\/a>.<\/p>\n
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<\/a>? <\/p>\n
\n(postgres@[local]:5439) [ddl] > create extension plperl;\nCREATE EXTENSION\n(postgres@[local]:5439) [ddl] > dx\n                 List of installed extensions\n  Name   | Version |   Schema   |         Description          \n---------+---------+------------+------------------------------\n plperl  | 1.0     | pg_catalog | PL\/Perl procedural language\n plpgsql | 1.0     | pg_catalog | PL\/pgSQL procedural language\n\n\nCREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$\n    my ($x, $y) = @_;\n    if (not defined $x) {\n        return undef if not defined $y;\n        return $y;\n    }\n    return $x if not defined $y;\n    return $x if $x > $y;\n    return $y;\n$$ LANGUAGE plperl;\n\n(postgres@[local]:5439) [ddl] > select perl_max(1,2);\n perl_max \n----------\n        2\n<\/pre>\n
You prefer python<\/a>?<\/p>\n
\n(postgres@[local]:5439) [ddl] > create extension plpythonu;\nCREATE EXTENSION\nTime: 327.434 ms\n(postgres@[local]:5439) [ddl] > dx\n                        List of installed extensions\n   Name    | Version |   Schema   |               Description                \n-----------+---------+------------+------------------------------------------\n plperl    | 1.0     | pg_catalog | PL\/Perl procedural language\n plpgsql   | 1.0     | pg_catalog | PL\/pgSQL procedural language\n plpythonu | 1.0     | pg_catalog | PL\/PythonU untrusted procedural language\n\nCREATE FUNCTION pymax (a integer, b integer)\n  RETURNS integer\nAS $$\n  if a > b:\n    return a\n  return b\n$$ LANGUAGE plpythonu;\n\n(postgres@[local]:5439) [ddl] > select pymax(1,1);\n pymax \n-------\n     1\n<\/pre>\n
… or better TcL<\/a>? <\/p>\n
\n(postgres@[local]:5439) [ddl] > create extension pltclu;\nCREATE EXTENSION\nTime: 382.982 ms\n(postgres@[local]:5439) [ddl] > dx\n                        List of installed extensions\n   Name    | Version |   Schema   |               Description                \n-----------+---------+------------+------------------------------------------\n plperl    | 1.0     | pg_catalog | PL\/Perl procedural language\n plpgsql   | 1.0     | pg_catalog | PL\/pgSQL procedural language\n plpythonu | 1.0     | pg_catalog | PL\/PythonU untrusted procedural language\n pltclu    | 1.0     | pg_catalog | PL\/TclU untrusted procedural language\n<\/pre>\n
And these are only the default extensions. There is much more you can do<\/a>:<\/p>\n