how to concat/concat_ws all fields without braces

Hi

so 15. 6. 2019 v 8:20 odesílatel Jean Louis <bugs@gnu.support> napsal:

Hello,

I have tried doing something like:

SELECT concat_ws(' ', table.*) FROM table;

and if I do that way, it is essentially same as

SELECT concat(table.*) FROM table;

and I get the items in braces like (1,something).

Why do I get it in braces?

Is there a way without specifying specific fields
to get all items concatenated without braces?

I would prefer conat_ws option.

It cannot to work. Postgres try to convert composite type based on all
fields to one text value, and this value is passed as one argument. It can
work because concat, concat_ws has 'variadic "any" parameter. But it does
cannot to work like you expect.

you can write own function that will do what you want

create or replace function rec_concat_fields(record, text)
returns text as $$
begin
return string_agg(value, '|') from json_each_text(row_to_json($1));
end
$$ language plpgsql;

postgres=# select rec_concat_fields(foo.*, '*') from foo;
┌───────────────────┐
│ rec_concat_fields │
╞═══════════════════╡
│ ahoj|svete │
└───────────────────┘
(1 row)

Regards

Pavel

Jean Louis schrieb am 15.06.2019 um 13:19:

I have tried doing something like:

SELECT concat_ws(' ', table.*) FROM table;

and if I do that way, it is essentially same as

SELECT concat(table.*) FROM table;

and I get the items in braces like (1,something).

Why do I get it in braces?

Is there a way without specifying specific fields
to get all items concatenated without braces?

I would prefer conat_ws option.

you can use the json functions for that:

select (select string_agg(x.v, ',') from jsonb_each_text(to_jsonb(t)) as x(k,v)) as all_columns
from the_table t;

Dear Pavel,

Ahoj.

* Pavel Stehule <pavel.stehule@gmail.com> [2019-06-15 11:37]:

you can write own function that will do what you want

create or replace function rec_concat_fields(record, text)
returns text as $$
begin
return string_agg(value, '|') from json_each_text(row_to_json($1));
end
$$ language plpgsql;

postgres=# select rec_concat_fields(foo.*, '*') from foo;
┌───────────────────┐
│ rec_concat_fields │
╞═══════════════════╡
│ ahoj|svete │
└───────────────────┘
(1 row)

I have tried that one, not working quite;

create or replace function concat_fields(record, text)
returns text as $$
begin
return string_agg(value, ' ') from json_each_text(row_to_json($1));
end
$$ language plpgsql;
rcdbusiness$# ERROR: PL/pgSQL functions cannot accept type record

Jean

Dear Thomas,

* Thomas Kellerer <spam_eater@gmx.net> [2019-06-15 11:37]:

Jean Louis schrieb am 15.06.2019 um 13:19:

I have tried doing something like:

SELECT concat_ws(' ', table.*) FROM table;

and if I do that way, it is essentially same as

SELECT concat(table.*) FROM table;

and I get the items in braces like (1,something).

Why do I get it in braces?

Is there a way without specifying specific fields
to get all items concatenated without braces?

I would prefer conat_ws option.

you can use the json functions for that:

select (select string_agg(x.v, ',') from jsonb_each_text(to_jsonb(t)) as x(k,v)) as all_columns
from the_table t;

Danke, that works well. I did not know it works
through Jansson exports. I hope it is fast enough.

Jean

so 15. 6. 2019 v 16:20 odesílatel Jean Louis <bugs@gnu.support> napsal:

Dear Pavel,

Ahoj.

* Pavel Stehule <pavel.stehule@gmail.com> [2019-06-15 11:37]:

you can write own function that will do what you want

create or replace function rec_concat_fields(record, text)
returns text as $$
begin
return string_agg(value, '|') from json_each_text(row_to_json($1));
end
$$ language plpgsql;

postgres=# select rec_concat_fields(foo.*, '*') from foo;
┌───────────────────┐
│ rec_concat_fields │
╞═══════════════════╡
│ ahoj|svete │
└───────────────────┘
(1 row)

I have tried that one, not working quite;

create or replace function concat_fields(record, text)
returns text as $$
begin
return string_agg(value, ' ') from json_each_text(row_to_json($1));
end
$$ language plpgsql;
rcdbusiness$# ERROR: PL/pgSQL functions cannot accept type record

depends on Postgres version. I have 12, but probably it should to work with
Postgres 10, 11

Pavel