Return multiple values and populate column in array like manner

Question

I am using Postgres 9.3 on MacOSX.

I am wondering how I can return multiple values (depending on certain criterion) and use them to populate a column in a list/array like manner?

--DUMMY DATA

CREATE TABLE tbl (
   id VARCHAR(2) PRIMARY KEY
  ,name TEXT
  ,year_born NUMERIC
  ,nationality TEXT
);
INSERT INTO tbl(id, name, year_born, nationality)
VALUES ('A1','Bill',2001,'American')
      ,('B1','Anna',1997,'Swedish')
      ,('A2','Bill',1991,'American')
      ,('B2','Anna',2004,'Swedish')
      ,('B3','Anna',1989,'Swedish')
      ,('A3','Bill',1995,'American');
SELECT * FROM tbl;

id | name | year_born | nationality
---+------+-----------+------------
A1 | Bill |   2001    |  American
B1 | Anna |   1997    |  Swedish
A2 | Bill |   1991    |  American
B2 | Anna |   2004    |  Swedish
B3 | Anna |   1989    |  Swedish
A3 | Bill |   1995    |  American

I pool over column name, nationality by using SELECT DISTINCT ON clause as in the below code

CREATE TABLE another_tbl ( name TEXT, nationality TEXT, ids VARCHAR ); 

CREATE FUNCTION f1() RETURNS SETOF another_tbl AS
$$ SELECT DISTINCT ON (name, nationality) name, nationality, id
   FROM tbl
   GROUP BY name, nationality, ID;
$$ LANGUAGE sql

SELECT * FROM f1();

 name | nationality | ids 
------+-------------+-----
 Anna |  Swedish    | B1
 Bill |  American   | A1

So, here is the thing which I do not know how to achieve, but which I reckon is fairly easy. I want column ids to be populated by all the id's corresponding to the names in the name column as seen below.

Desired output:

 SELECT * FROM f1();

 name | nationality | ids 
------+-------------+-----
 Anna |  Swedish    | B1, B2, B3
 Bill |  American   | A1, A2, A3

Update

Found out about ARRAY which I use together with class VARCHAR for column ids in another_tbl. However, I get a mismatch call saying Final statement returns character varying instead ofcharacter varying[]at column 3.

Erwin Brandstetter · Accepted Answer · 2013-11-20 04:22:43Z

9

Use GROUP BY and the aggregate function string_agg() if you want a text column as result.
Or array_agg() to construct an array.
But drop the now redundant DISTINCT ON.

SELECT name, nationality, string_agg(id, ',') AS ids
FROM   tbl
GROUP  BY 1, 2
ORDER  BY 1, 2;

The RETURNS clause of your function definition has to match, like @ozczecho suggested:

CREATE FUNCTION f1()
  RETURNS TABLE(name text, nationality text, ids text) AS
                                              -- varchar[] for array_agg()
$func$
SELECT t.name, t.nationality, string_agg(t.id, ',') AS ids
FROM   tbl t
GROUP  BY 1, 2
ORDER  BY 1, 2;
$func$ LANGUAGE sql;

edited Nov 20, 2013 at 4:22

answered Nov 20, 2013 at 4:15

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

3 Comments

jO. Over a year ago

Cool, beats my answer..Thanks for clarifying!

Hamman Samuel Over a year ago

I'm attempting to do the same, but using MS SQL. Is there any equivalent for string_agg() in MS SQL? Or another approach for MS SQL that would work to give similar results as this question asks?

Erwin Brandstetter Over a year ago

@HammanSamuel: I asked this similar question quite some time ago and got an excellent answer.

ozczecho · Accepted Answer · 2013-11-20 02:23:30Z

2

I believe you should change:

RETURNS SETOF another_tbl

to:

RETURNS TABLE(name TEXT, nationality TEXT, ids VARCHAR[])

answered Nov 20, 2013 at 2:23

ozczecho

8,9098 gold badges39 silver badges42 bronze badges

2 Comments

jO. Over a year ago

Thanks for commenting. Your suggestion gives the same mismatch error message however, since RETURNS TABLE(name...) in this case should be equivalent to another_tbl. And by default VARCHAR[] will set the array to one-dimension. In my case I do not know in advance how many dimensions the array needs to have. I think you can use ARRAY for that.

Erwin Brandstetter Over a year ago

@jO.: Currently, there is only one array type for any given base type in Postgres, covering all dimensions. Details in this related answer.

jO. · Accepted Answer · 2013-11-20 03:52:53Z

0

Ok, it works using the array_agg function.

CREATE FUNCTION f1() RETURNS SETOF another_tbl AS
$$ SELECT DISTINCT ON (name, nationality) name, nationality, array_agg(id)
   FROM tbl
   GROUP BY name, nationality
$$ LANGUAGE sql

SELECT * FROM f();

name | nationality |    ids     
-----+-------------+-----------
Anna | Swedish     | {B1,B2,B3}
Bill | American    | {A1,A2,A3}

And after adding the function array_to_string to array_agg(id) we get the desired output

name | nationality |   ids    
-----+-------------+---------
Anna | Swedish     | B1,B2,B3
Bill | American    | A1,A2,A3

edited Nov 20, 2013 at 3:52

answered Nov 20, 2013 at 3:02

jO.

3,5327 gold badges32 silver badges38 bronze badges

Collectives™ on Stack Overflow

Return multiple values and populate column in array like manner

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related