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;
}
Erwin Brandstetter :
Use an SQL CASE statement:\n\nCASE WHEN x < y THEN\n x * (y - 1) + ((y - x - 2)^2)::int / 4\nELSE\n (x - 1) * y + ((x - y - 2)^2)::int / 4\nEND\n\n\nThe operator ^ as well as the function power() return double precision. So I cast to int to match your question.\n\nWrapped into a plain SQL function (with operator ^):\n\nCREATE OR REPLACE FUNCTION pair1(x int, y int)\n RETURNS int AS\n$func$\nSELECT CASE WHEN x < y THEN\n x * (y - 1) + ((y - x - 2)^2)::int / 4\n ELSE\n (x - 1) * y + ((x - y - 2)^2)::int / 4\n END\n$func$ LANGUAGE sql IMMUTABLE;\n\n\nIn Postgres 9.1 or older you have tro reference input columns with positional parameters $1, $2 instead.\n\nThe same as PL/pgSQL function (with function power()):\n\nCREATE OR REPLACE FUNCTION pair2(x int, y int)\n RETURNS int AS\n$func$\nBEGIN\nRETURN CASE WHEN x < y THEN\n x * (y - 1) + power(y - x - 2, 2)::int / 4\n ELSE\n (x - 1) * y + power(x - y - 2, 2)::int / 4\n END;\nEND\n$func$ LANGUAGE plpgsql IMMUTABLE;\n\n\nSQL Fiddle demo.\n\nI 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:\nDoes PostgreSQL support "accent insensitive" collations?",
2014-03-14T01:52:02