How to split a type into multiple columns in Postgres?

Question

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

araqnid · Accepted Answer · 2011-02-01T18:30:48.707

8

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;

edited Feb 01 '11 at 18:30

answered Feb 01 '11 at 18:25

araqnid

127,052
24
157
134

1

Your code works, but do I really need a sub-select for doing this? I was so close with make_pair('egg', 4).name, but that did not work. – David Feb 01 '11 at 18:28
1

You can say `select (make_pair('egg', 4)).name`, for example. If you want both components but only to execute the function once, I think you need the sub-select. I'll update the answer. – araqnid Feb 01 '11 at 18:30
Not having to execute the function more than once is definitely an objective. – David Feb 01 '11 at 19:23
I have added my own answer with working code to avoid having to run the function twice and at the same time avoid a subquery. I will still accept your answer as you lead me to it. – David Feb 01 '11 at 19:37

Milen A. Radev · Answer 2 · 2011-02-02T10:04:26.790

3

SELECT * FROM make_pair('egg', 4);

and some variants:

 SELECT name, value FROM make_pair('egg', 4) AS x;


 SELECT a, b FROM make_pair('egg', 4) AS x(a,b);

edited Feb 02 '11 at 10:04

answered Feb 02 '11 at 07:35

Milen A. Radev

60,241
22
105
110

This is a very elegant solution, but I do not know how to use it in my case where I need to run the function on each row in a table. Performance is also very important for me. – David Feb 02 '11 at 10:14

score 2 · Answer 3 · answered Feb 03 '14 at 06:52

A solution I found was to use join:

create table tmp (a int, b int, c int);
insert into tmp (a,b,c) values (1,2,3), (3,4,5), (5,12,13);
create type ispyth3 as (is_it boolean, perimeter int);
create function check_it(int, int, int) returns ispyth3 as $$
    begin
        return ($1*$1 + $2*$2 = $3*$3, $1+$2+$3);
    end
$$ language plpgsql;
select * from tmp join check_it(a,b,c) on 1=1;

This returns:

 a | b  | c  | is_it | perimeter 
---+----+----+-------+-----------
 1 |  2 |  3 | f     |         6
 3 |  4 |  5 | t     |        12
 5 | 12 | 13 | t     |        30
(3 rows)

Another way to express the cross join is to use `JOIN CHECK_it(a,b,c) ON TRUE` or simply use `CROSS JOIN`. — NoelProf, Jan 23 '18 at 16:41

score 1 · Answer 4 · answered Feb 01 '11 at 19:36

The following is working code to avoid having to run the function twice and at the same time avoid a subquery.

CREATE TYPE named_value AS (
  name   text,
  value  integer
);

CREATE or replace FUNCTION setcustomvariable(variablename text, variablevalue named_value)
  RETURNS named_value
AS $$
  GD[variablename] = variablevalue
  return variablevalue
$$ LANGUAGE plpythonu;

CREATE or replace FUNCTION getcustomvariable(variablename text)
  RETURNS named_value
AS $$
  return GD[variablename]
$$ LANGUAGE plpythonu;

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

select setcustomvariable('result', make_pair('egg', 4)), (getcustomvariable('result')).name, (getcustomvariable('result')).value

How to split a type into multiple columns in Postgres?

4 Answers4