{"id":15596,"date":"2021-02-03T13:02:23","date_gmt":"2021-02-03T12:02:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/an-introduction-to-server-side-programming-in-postgresql-1-sql-functions-basics\/"},"modified":"2021-02-03T13:02:23","modified_gmt":"2021-02-03T12:02:23","slug":"an-introduction-to-server-side-programming-in-postgresql-1-sql-functions-basics","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/an-introduction-to-server-side-programming-in-postgresql-1-sql-functions-basics\/","title":{"rendered":"An introduction into server side programming in PostgreSQL – 1 – SQL functions, basics"},"content":{"rendered":"

Over the last years I’ve seen many different applications running against PostgreSQL. The use cases vary from simple applications, which only use basic data types and a few tables and views, to complex applications with custom types, more specific data types like jsonb<\/a> or range types<\/a>, that use hundreds of schemas\/tables\/views\/materialized views etc. Surprisingly only a few of them make use of functions and procedures in PostgreSQL. I’ve always told people to process the data where it is, and usually the data is in the database, as this is best for performance and enables you to make us of advanced features of PostgreSQL. This does, of course, not only apply to PostgreSQL but to all other databases systems as well. This usually leads to discussion about being independent of the underlying databases and then it gets religious. While I understand the developer\/business owner’s point of view of being as much independent of the database as possible, makes it easier to migrate from one database system to another in the future, this decision closes many doors when it comes to getting the maximum out of the database. <\/p>\n

<\/p>\n

The most simple function you can create in PostgreSQL is a SQL function<\/a>. A SQL functions contain one or more SQL statements and either return nothing, one row or a set of rows. To get started lets create a standard pgbench<\/a> schema with a couple of rows:<\/p>\n

\npostgres=# ! pgbench -i -s 10 postgres\ndropping old tables...\nNOTICE:  table \"pgbench_accounts\" does not exist, skipping\nNOTICE:  table \"pgbench_branches\" does not exist, skipping\nNOTICE:  table \"pgbench_history\" does not exist, skipping\nNOTICE:  table \"pgbench_tellers\" does not exist, skipping\ncreating tables...\ngenerating data (client-side)...\n1000000 of 1000000 tuples (100%) done (elapsed 2.20 s, remaining 0.00 s)\nvacuuming...\ncreating primary keys...\ndone in 3.52 s (drop tables 0.00 s, create tables 0.04 s, client-side generate 2.27 s, vacuum 0.30 s, primary keys 0.90 s).\npostgres=# d\n              List of relations\n Schema |       Name       | Type  |  Owner   \n--------+------------------+-------+----------\n public | pgbench_accounts | table | postgres\n public | pgbench_branches | table | postgres\n public | pgbench_history  | table | postgres\n public | pgbench_tellers  | table | postgres\n(4 rows)\n<\/pre>\n
One of the most simple SQL functions is something like this:<\/p>\n
\npostgres=# create or replace function get_bid_for_aid_1 () returns int\n           as $$\n           select bid from pgbench_accounts where aid = 1;\n           $$ language SQL;\nCREATE FUNCTION\n<\/pre>\n
This really is not rocket science but it is a good starting point to understand the concepts. PostgreSQL uses the so called dollar quoting. That means, everything between the first “$$” and the last “$$” is the body of the function. This makes it easier to write sting literals without escaping like, e.g. single or double quotes (a more detailed description can be found here<\/a>). If you want to use a tag with dollar quoting you can do that as well:<\/p>\n
\npostgres=# create or replace function get_bid_for_aid_1_tmp () returns int\n           as $my_tag$\n           select bid from pgbench_accounts where aid = 1;\n           $my_tag$ language SQL;\nCREATE FUNCTION\n<\/pre>\n
Side note: There is nothing like in “invalid object” as you might know that from Oracle. If your function body contains errors it will not be stored in the database, you’ll have to fix that first:<\/p>\n
\npostgres=# create or replace function get_bid_for_aid_1_tmp_1 () returns int\n           as $my_tag$\n           select bid from pgbench_accountsXX where aid = 1;\n           $my_tag$ language SQL;\nERROR:  relation \"pgbench_accountsxx\" does not exist\nLINE 3: select bid from pgbench_accountsXX where aid = 1;\n<\/pre>\n
More information about this behavior can be found here<\/a>. Coming back to our initial function: As soon as the function is stored in PostgreSQL you can reference it in your SQL queries:<\/p>\n
\npostgres=# select * from get_bid_for_aid_1();\n get_bid_for_aid_1 \n-------------------\n                 1\n(1 row)\n<\/pre>\n
Right now this function is quite limited as it returns the bid only for aid=1. To make that a bit more re-usable can you make use of parameters:<\/p>\n
\npostgres=# drop function get_bid_for_aid_1();\nDROP FUNCTION\npostgres=# create or replace function get_bid_for_aid (int) returns int\n           as $my_tag$\n           select bid from pgbench_accounts where aid = $1;\n           $my_tag$ language SQL;\nCREATE FUNCTION\npostgres=# select * from get_bid_for_aid(1);\n get_bid_for_aid \n-----------------\n               1\n(1 row)\n<\/pre>\n
Now the function is more flexible, as you can ask for any bid for a given aid. But there is even more we can do, to make the function more readable. Instead of specifying just the data type for the input parameter we can give the parameter a name:<\/p>\n
\npostgres=# create or replace function get_bid_for_aid (pn_aid int) returns int\n           as $my_tag$\n           select bid from pgbench_accounts where aid = pn_aid;\n           $my_tag$ language SQL;\nCREATE FUNCTION\npostgres=# select * from get_bid_for_aid(1);\n get_bid_for_aid \n-----------------\n               1\n(1 row)\n<\/pre>\n
Although this does not change the behavior of the function, it makes the function easier to read and we recommend to work with those named parameters instead of only specifying the data types. Especially when the body of a function becomes quite large and it uses many parameters, it saves you quite some scrolling forward and backward because you already know the data type from the parameter name (or at least you should be able to guess it).<\/p>\n
You are not limited to use a function in the from clause of your statement. Actually you can use the function wherever you want, as long as the result is valid SQL, e.g. in the where clause:<\/p>\n
\npostgres=# select count(*) \n             from pgbench_accounts \n            where bid = get_bid_for_aid (1);\n count  \n--------\n 100000\n(1 row)\n<\/pre>\n
By now we used one simple select with our function, but you can also modify your data using SQL functions:<\/p>\n
\npostgres=# create or replace function upd_pgbench_account_for_aid ( pn_aid int, pv_filler character ) returns void\n           as $$\n             update pgbench_accounts\n                set filler = pv_filler\n              where aid = pn_aid;\n           $$ language SQL;\nCREATE FUNCTION\npostgres=# select * from upd_pgbench_account_for_aid (1,'dummy'); \n upd_pgbench_account_for_aid \n-----------------------------\n \n(1 row)\n\npostgres=# select filler from pgbench_accounts where aid = 1;\n                                        filler                                        \n--------------------------------------------------------------------------------------\n dummy                                                                               \n(1 row)\npostgres=# select * from upd_pgbench_account_for_aid (pv_filler=>'dummy2',pn_aid=>1); \n upd_pgbench_account_for_aid \n-----------------------------\n \n(1 row)\n\npostgres=# select filler from pgbench_accounts where aid = 1;\n                                        filler                                        \n--------------------------------------------------------------------------------------\n dummy2                                                                              \n(1 row)\n<\/pre>\n
There are a couple of things to note here:<\/p>\n