Postgres aggregate row to string

Question

If I have a following query:

SELECT 'a' AS a, 1 AS b, CURRENT_DATE AS c;

It's possible to aggregate row to string as:

SELECT concat_ws(', ', a, b, c) FROM (
    SELECT 'a' AS a, 1 AS b, CURRENT_DATE AS c
) AS T;

-- Result: "a, 1, <CURRENT DATE>"

But what I can't seem to find - if there is already existing function in postgres (version 9.3 or 9.4) that will produce the same result as above, but operating with a "*"

--
-- Something like:
--
-- some_function(<separator>,  *) - ???
--
SELECT some_function(', ', *) FROM (
    SELECT 'a' AS a, 1 AS b, CURRENT_DATE AS c
) AS T;

-- so the result would be - "a, 1, <CURRENT DATE>"

Thanks for any help.

Hmm. Don't understand the question... "something like t" threw me off — FirebladeDan
– FirebladeDan, Commented Jul 31, 2015 at 20:33
No there isn't. Best you can do is a function to read all fields from the table and create the concatenated list of fields then you use this list to create and run your sql statement dinamically — Jorge Campos
– Jorge Campos, Commented Jul 31, 2015 at 20:34

score 4 · Accepted Answer · 2015-07-31 21:07:28Z

4

If you convert the row to a hstore, you can do that:

select array_to_string(avals(hstore(x)), ', ')
from (
  SELECT 'a' AS a, 1 AS b, CURRENT_DATE AS c
) x

hstore(x) will convert all column values to strings and then put them into the hstore. avals then extracts the values as a string array, and array_to_string converts it into a comma separated list.

To do that for a regular table, you need to pass the table name, not *

select array_to_string(avals(hstore(foobar)), ', ')
from foobar;

If you don't want to (or can't) install the hstore extension, you can do this with the JSON function although it requires one more level of nesting:

select string_agg(value, ', ') 
from (
  select (json_each_text(row_to_json(x1))).value
  from (
    SELECT 'a' AS a, 1 AS b, CURRENT_DATE AS c
  ) x1
) x2

Or to get the data from a table:

select string_agg(value, ', ') 
from (
  select (json_each_text(row_to_json(foobar))).value
  from foobar
) x

If you want you can "hide" this in a function:

create or replace function concat_record(p_data anyelement, p_delim text)
  returns text
as $$
   select array_to_string(avals(hstore(p_data)), p_delim);
$$
language sql;

And then you can do:

select concat_record(foobar, ', ')
from foobar;

Unrelated but: you don't need the select for the dummy data:

select *
from (
  values ('a', 1, CURRENT_DATE)
) as x (a,b,c);

Is a bit shorter to write - especially if you have more than one row.

select *
from (
  values 
     ('a', 1, CURRENT_DATE), 
     ('x', 2, date  '2015-07-01')
) as x (a,b,c);

edited Jul 31, 2015 at 21:07

answered Jul 31, 2015 at 20:41

user330315

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

3 Comments

Dmitry F Over a year ago

Thanks, it kind of works and it doesn't. It will produce the desired result, but there is a slightly different meaning for parameters (I think I should update a question with better example). "*" would mean all columns (maybe from multiple table). and I don't think it's possible to do hstore(*), or row_to_json(*).

user330315 Over a year ago

@DmitryF No, you need to pass the identifier for the row, not *. The x has to be the name of the table (or the alias) you are selecting from. It represents all columns for a single row.

Dmitry F Over a year ago

yeah, I was thinking that too, though I guess concat_ws(', ', array_to_string(avals(hstore(T1)), ', '), array_to_string(avals(hstore(T2)), ', ')) - might not be that bad ( and probably can't be improve, it's just a rough version )

Collectives™ on Stack Overflow

Postgres aggregate row to string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related