postgresqlplpgsqlplperl

How to split a type into multiple columns in Postgres?

I have the following code to return multiple values from pl/python:

CREATE TYPE named_value AS (
  name   text,
  value  integer
);
CREATE or replace FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
  return [ name, value ]
$$ LANGUAGE plpythonu;

select make_pair('egg', 4) as column;

The output is:

column
(egg,4)

What I want to do is to split the output into two separate columns. Like this:

column, column2
egg, 4

How do I do this? Googled for 1 hour got me nowhere. So I hope I will add some search keywords in the end: multiple return values multiple results multiple columns unnest list unnest set

Solution

  • Yeah, the syntax for this is a bit wacky, requiring extra parentheses:

    select (make_pair('egg', 4)).name

    To get multiple components from the output while only invoking the function once, you can use a sub-select:

    select (x.column).name, (x.column).value from (select make_pair('egg', 4) as column) x;