Thread: how to concat/concat_ws all fields without braces
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. Jean
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
returns text as $$
begin
return string_agg(value, '|') from json_each_text(row_to_json($1));
end
$$ language plpgsql;
$$ language plpgsql;
postgres=# select rec_concat_fields(foo.*, '*') from foo;
┌───────────────────┐
│ rec_concat_fields │
╞═══════════════════╡
│ ahoj|svete │
└───────────────────┘
(1 row)
┌───────────────────┐
│ rec_concat_fields │
╞═══════════════════╡
│ ahoj|svete │
└───────────────────┘
(1 row)
Regards
Pavel
Jean
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
Jean