PostgreSQL: How to create a pairing function?

Question

How do I create a user defined function in PostgreSQL that acts like the C function pair defined below?

Also, which type of function should I use and why?

(A) query language (SQL) function
(B) procedural language function
(C) internal function
(D) C-language function
(E) None of the above

Bonus points for implementations in both SQL & PL/pgSQL languages.

#include <stdio.h>
#include <math.h>

int pair(int x, int y)
{
    int z;
    if (x < y) {
        z = x * (y-1);
        y = y - x - 2;
    } else {
        z = (x-1) * y;
        y = x - y - 2;
    }
    return z + pow(y, 2) / 4;
}

// TESTING

void test(int x, int y, int z)
{
    printf("%s", pair(x, y) == z ? "." : "F");
}

int main(void)
{
    test(1, 2, 1);
    test(1, 3, 2);
    test(1, 4, 3);
    test(1, 5, 5);
    test(1, 6, 7);
    test(1, 7, 10);
    test(1, 8, 13);
    test(1, 9, 17);

    test(2, 3, 4);
    test(2, 4, 6);
    test(2, 5, 8);
    test(2, 6, 11);
    test(2, 7, 14);
    test(2, 8, 18);
    test(2, 9, 22);

    printf("\n");

    return 0;
}

score 2 · Answer 1 · answered Mar 14 '14 at 00:30

SQL is simpler. If x < y it will return the first element of the array else the second

create or replace function pair(x int, y int)
returns double precision as $$
select (array[
    x * (y - 1) + power(y - x - 2, 2) / 4,
    (x - 1) * y + power(x - y - 2, 2) / 4
    ])[(not x < y)::int + 1]
;
$$ language sql;

select
    pair(1, 2) = 1,
    pair(1, 3) = 2,
    pair(1, 4) = 3,
    pair(1, 5) = 5,
    pair(1, 6) = 7,
    pair(1, 7) = 10,
    pair(1, 8) = 13,
    pair(1, 9) = 17,
    pair(2, 3) = 4,
    pair(2, 4) = 6,
    pair(2, 5) = 8,
    pair(2, 6) = 11,
    pair(2, 7) = 14,
    pair(2, 8) = 18,
    pair(2, 9) = 22
;

A boolean casted to integer yields 1 or 0. As Postgresql array index starts at 1 then 1 is added to the result of the casting.

score 2 · Accepted Answer · edited May 23 '17 at 11:57

Use an SQL CASE statement:

CASE WHEN x < y THEN
   x * (y - 1) + ((y - x - 2)^2)::int / 4
ELSE
   (x - 1) * y + ((x - y - 2)^2)::int / 4
END

The operator ^ as well as the function power() return double precision. So I cast to int to match your question.

Wrapped into a plain SQL function (with operator ^):

CREATE OR REPLACE FUNCTION pair1(x int, y int)
  RETURNS int AS
$func$
SELECT CASE WHEN x < y THEN
          x * (y - 1) + ((y - x - 2)^2)::int / 4
       ELSE
          (x - 1) * y + ((x - y - 2)^2)::int / 4
       END
$func$  LANGUAGE sql IMMUTABLE;

In Postgres 9.1 or older you have tro reference input columns with positional parameters $1, $2 instead.

The same as PL/pgSQL function (with function power()):

CREATE OR REPLACE FUNCTION pair2(x int, y int)
  RETURNS int AS
$func$
BEGIN
RETURN CASE WHEN x < y THEN
          x * (y - 1) + power(y - x - 2, 2)::int / 4
       ELSE
          (x - 1) * y + power(x - y - 2, 2)::int / 4
       END;
END
$func$  LANGUAGE plpgsql IMMUTABLE;

SQL Fiddle demo.

I depends, but generally I would use a simple SQL function. And don't forget to declare it IMMUTABLE. This allows various performance optimizations in bigger queries and using it in functional indexes. Example with more details:
Does PostgreSQL support "accent insensitive" collations?

Thank you so much, Erwin. This answer is Amazing! – ma11hew28 Mar 14 '14 at 11:49 — ma11hew28, Mar 14 '14 at 11:49

PostgreSQL: How to create a pairing function?

2 Answers2