O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 record—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 
hannu$# ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required