How to Use Parameter Names in SQL Functions

by Andras Vaczi - 15 Oct 2015

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, $2 and so on.

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 with p_valid_to.

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

Similar blog posts