Database Reference
In-Depth Information
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$# yield b,c,a,b*c+a
hannu$# yield c,a,b,c*a+b
hannu$# yield c,b,a,c*b+a
hannu$# $$ LANGUAGE plpythonu;
CREATE FUNCTION
hannu=# SELECT * FROM
reverse_permutations(row(2,7,13));
-[ RECORD 1 ]
c | 2
b | 7
a | 13
x | 27
-[ RECORD 2 ]
