What is the correct way to define a Postgres SQL-lang function that returns multiple columns?

Question

I have the following function, based on the SQL Functions Returning Sets section of the PG docs, which accepts two arrays of equal length, and unpacks them into a set of rows with two columns.

CREATE OR REPLACE FUNCTION unpack_test(
    in_int INTEGER[],
    in_double DOUBLE PRECISION[],
    OUT out_int INTEGER,
    OUT out_double DOUBLE PRECISION
) RETURNS SETOF RECORD AS $$
    SELECT $1[rowx] AS out_int, $2[rowx] AS out_double
    FROM generate_series(1, array_upper($1, 1)) AS rowx;
$$ LANGUAGE SQL STABLE;

I execute the function in PGAdmin3, like this:

SELECT unpack_test(int_col, double_col) FROM test_data

It basically works, but the output looks like this:

|unpack_test|
|record     |
|-----------|
|(1, 1)     |
|-----------|
|(2, 2)     |
|-----------|
 ...

In other words, the result is a single record, as opposed to two columns. I found this question that seems to provide an answer, but it deals with a function that selects from a table directly, whereas mine accepts the columns as arguments, since it needs to generate the series used to iterate over them. I therefore can't call it using SELECT * FROM function, as suggested in that answer.

Anomie · Accepted Answer · 2011-08-23 18:59:18Z

3

First, you'll need to create a type for the return value of your function. Something like this could work:

CREATE TYPE unpack_test_type AS (out_int int, out_double double precision);

Then change your function to return this type instead of record.

Then you can use it like this:

SELECT (unpack_test).out_int, (unpack_test).out_double FROM
        (SELECT unpack_test(int_col, double_col) FROM test_data) as test

It doesn't seem possible to take a function returning a generic record type and use it in this manner.

answered Aug 23, 2011 at 18:59

Anomie

95.5k13 gold badges130 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DNS Over a year ago

Your SQL actually works on its own; it isn't even necessary to create a new type. The returned record is already an 'anonymous type'; the trick is in accessing its fields. I had tried a sub-select like yours, but wrote it as unpack_test.out_int or test.out_int. The secret is to wrap the name in parentheses, as you did, since Postgres apparently requires that syntax.

Anomie Over a year ago

@DNS: On 9.0 here, it gives an error "record type has not been registered" when using it without the specific type.

DNS Over a year ago

Interesting; it works on 8.3, so the behavior must have changed.

Anomie Over a year ago

@DNS: They did stricten up a lot of stuff with casting in 9.0, e.g. no more automatic cast from integer to text.

Collectives™ on Stack Overflow

What is the correct way to define a Postgres SQL-lang function that returns multiple columns?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related