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; 

Output:

 column (egg,4) 

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

 column, column2 egg, 4 

How can I do it? Googled for 1 hour did not give me anywhere. Therefore, I hope to add search keywords at the end: multiple return values ​​multiple results multiple columns unsest list unsest set

Answer 1

Yes, the syntax for this is a bit confusing, requiring additional parentheses:

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

To get multiple components from the output while calling the function at once, you can use a subselect:

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

Answer 2

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

and some options:

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

Answer 3

The 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) 

Answer 4

The following is the working code to avoid having to run this function twice and at the same time to avoid the 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