At Zalando, we store most of our valuable data in PostgreSQL. When we want to access it, we typically use a layer of PostgreSQL functions. With every release, we roll out a new set of functions, neatly organized into versioned API schemas. The application then reads and changes the data by calling the functions of the current API schema. This means we have many functions in our databases — and a relatively large team of developers who write them.

If you have been developing PostgreSQL functions for a long time, you know that, until relatively recently, there used to be a pain point when writing query language functions (better known as SQL functions). On the one hand were the PL/pgSQL functions, where parameters names could have been used in the function body; on the other hand were the poor SQL functions, where this was impossible. We had no option but to use the positional parameter notation: $1[obje[object Object]

But now the misery is over! From PostgreSQL 9.2 onwards, even SQL language functions can make use of parameter names. To see the difference, let's pick a function from Zalando’s codebase (with a minor tweak from me):

CREATE OR REPLACE FUNCTION get_something_to_process(
    p_offer         TEXT,
    p_template_id   INTEGER,
    p_valid_to      TIMESTAMP,
    p_valid_from    TIMESTAMP,
    p_name          TEXT,
    p_code          TEXT,
    p_limit         INTEGER DEFAULT 1,
    p_offset        INTEGER DEFAULT 0
) RETURNS SETOF request AS
$BODY$
    ...
     WHERE r_offer = $1
       AND r_template_id = $2
       AND r_valid_from = $3
       AND r_valid_to = $4
       AND r_name = $5
       AND r_code IS NOT DISTINCT FROM $6
     LIMIT $7 OFFSET $8;
$BODY$
LANGUAGE 'sql' STABLE SECURITY DEFINER;

Can you tell what's wrong? Compare it to the following, dollar-free version:

...
     WHERE r_offer = p_offer
       AND r_template_id = p_template_id
       AND r_valid_from = p_valid_to
       AND r_valid_to = p_valid_from
       AND r_name = p_name
       AND r_code IS NOT DISTINCT FROM p_code
     LIMIT p_limit OFFSET p_offset;

Now it's probably obvious why you didn't get the expected result. I swapped two parameters: p_valid_from[object Object][object Object]

And that's all. Use this 'new' feature to make your life better!

We're hiring! Do you like working in an ever evolving organization such as Zalando? Consider joining our teams as a Software Engineer!