W I S C H N E R

And now for something a bit more complex. Today we're going to discover the art of creating a stored procedure / function layer in Postgres database.

Let us first agree on terminology. In Postgres there is no difference between a stored procedure and a function. A procedure is merely a function returning void. Thus from now on we will only use term function.

Just in case you wonder - as a mature DBMS Postgres prepares (precompiles) all functions for optimal performance, exactly as its commercial competitors.

In contemporary databases functions are commonly used to implement security layer. It is generally easier to give a user permission to execute a function that manipulates many database tables to complete a business operation then it is to assign him or her just the right permissions on all involved database tables for the same result.

Therefore modern designs introduce an additional layer of abstraction to access database tables. This layer is implemented via functions. In such scenario users can access database tables only through functions. They have no direct access to database tables.



To implement this design we need to know more about the Security of definer concept.

Security of definer

In Postgres a function can be defined to have “Security of definer” property set. You can set this propety in pgAdmin (see the screenshot bellow).



To set this property manually add SECURITY DEFINER to the end of function definition like this -

CREATE FUNCTION insert_city(character varying)
  RETURNS void AS
$BODY$
BEGIN
 INSERT INTO city("name") VALUES($1);
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

Adding this to a function will make it run under the context of the owner of the function instead of context of the caller of the function. This allows a user to insert city into a table without having insert permission on it. As long as he has permission to execute this function and the owner of the function has permission to insert into city table. It is a way to implement chained security on Postgres.

Postgres functions that return datasets

Postgres functions that return data sets are poorly documented and their syntax might look a bit awkward to those of you used to SQL Server. When a function in Postgres returns multiple results it must be declared to return SETOF some type. Call syntax for such functions is a bit different then what you are used to. You call it using SELECT * FROM function() instead of usual SELECT function() call.

SELECT function_returning_setof(); -- Wrong!
SELECT * FROM function_returning_setop(); -- OK!
SELECT function_returning_scalar(); -- OK

Let's imagine we have a table of all cities with two fields – city_id of type SERIAL, and name of type VARCHAR(80). Actually we're imagining this througout this article.

Here is a code fragment showing how to write a function returning all cities.

CREATE FUNCTION list_all_cities() 
RETURNS SETOF city AS
$$
DECLARE
 rec record;
BEGIN
 FOR rec IN (SELECT * FROM city) LOOP
  RETURN NEXT rec;
 END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Call the function.
SELECT * FROM list_all_cities();

This code is using generic type called RECORD. Try experimenting with %ROWTYPE to obtain same results. Function above returns type SETOF city - each record in the set has the same structure as a record of city table. But what if you wanted to return another structure? One way is to create a type like this -

CREATE TYPE list_all_city_names_result_type AS (city_name varchar(80));
CREATE FUNCTION list_all_city_names() 
RETURNS SETOF list_all_city_names_result_type AS
$$
DECLARE
 rec record;
BEGIN
 FOR rec IN (SELECT "name" FROM city) LOOP
  RETURN NEXT rec;
 END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM list_all_city_names();

If this imposes too harsh limitations then you can also use the generic RECORD data type as return type and tell the procedure what to expect when calling it.

CREATE FUNCTION list_all_city_names2() 
RETURNS SETOF RECORD AS
$$
DECLARE
 rec record;
BEGIN
 FOR rec IN (SELECT "name" FROM city) LOOP
  RETURN NEXT rec;
 END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM list_all_city_names2() 
AS ("city_name" varchar(80)); -- Expect varchar(80)

There's one more trick. I don't recommend it but I'm publishing it anyways. If you'd like to make things really simple for you then you could use another language instead of plpgsql. For example, using sql code you can write queries directly into your function like this -

CREATE FUNCTION getallzipcodes() 
RETURNS SETOF zip AS
$BODY$
 SELECT * FROM zip;
$BODY$ 
LANGUAGE 'sql';

Stored procedure layer and automatic code generation

When I create stored procedure layer I like to generate code for basic CRUD functions. I only write code for complex business functions by hand. Thus in my next article I am going to write code generator to do just that for Postgres/c++ pair.

But right now let's just explain some concepts. CRUD functions are - Create (new record from data), Read (given ID), Update (existing record given ID and new data) and Delete (given id) for single table. These functions are needed for every table. For example for table city you would have following functions:
- create_city(name),
- read_city(id),
- update_city(id,new_name),
- delete_city(id).

Two more types of functions are often needed. Get functions and list functions (sometimes called find functions). The difference between get and list function is that a get function always return single result. For example get_city_id_by_name(name) or get_user_by_phone(phone). Whereas list function returns all records that match the condition provided as parameters. For example - list_cities_starting_with(word) or list_persons_older_then(age) or list_all_countries().

Some people prefer list_ and other prefer find_ prefix for list functions. It is a matter of taste; as long as your programming stlye is consistent.

Table above shows functions that will be needed for almost any table and are therefore candidates for automatic code generation. We'll deal with code generation in one of our following articles.