PostgreSQL Server Programming - Second Edition

Handling records as arguments or returned values

As our next exercise, let's write a function which takes a record of three integers a, b, and c as an argument and returns a set of different records—all permutations of a, b, and c with an extra field x computed as a * b + c.

First, this function is written in PL/python to make it easier to understand what we are trying to do:

hannu=# CREATE LANGUAGE plpythonu;
CREATE LANGUAGE 
hannu=# CREATE TYPE abc AS (a int, b int, c int); 
CREATE TYPE 
hannu=# CREATE OR REPLACE FUNCTION 
hannu-#     reverse_permutations(r abc) 
hannu-#   RETURNS TABLE(c int, b int, a int, x int) 
hannu-# AS $$ 
hannu$#     a,b,c = r['a'], r['b'], r['c'] 
hannu$#     yield a,b,c,a*b+c 
hannu$#     yield a,c,b,a*c+b 
hannu$# yield b,a,c,b*b+c ...