Convert record to string with custom delimiter

Question

I can convert record to string by just doing myrecord::text. My problem is that my record has text fields which contain commas, and the resulting type cast text is comma separated. Thus, I cannot separate the resulting text in a good way.

Solution would be to define a custom delimiter when casting record to text (for example tab or semicolon). How can I achieve this?

Googling gives me answers like use functions string_agg, unnest, array_to_string, etc. None of those functions exist in my installation (I am using PostgreSQL version 12.4 on a Windows machine).

replace(myrecord::text, ',', '\t') puts tab inside of my text fields that should have commas.

Example code:

DECLARE
myrecord RECORD;
BEGIN
FOR myrecord IN
SELECT * FROM mytable
LOOP
RAISE INFO '%',replace(myrecord::text, ',', '\t');
END LOOP;
END;

Answer 1

If you need a tab separated output of the contents of a table, you could use COPY:

COPY mytable TO '/dir/outfile' (FORMAT 'csv', DELIMITER E'\t');

Answer 2

I think, the answer given by @LaurenzAlbe, fully meets your task.

For interest, I will offer another example for copying with direct conversion without an intermediate table.
See this also.

Example for concat all columns with a designated separator.

COPY (select string_agg(value,';' order by ordinality)
      from
        (select test_table.id,t.*
         from test, lateral jsonb_each_text(to_jsonb(test_table)) with ordinality as t
         where ....
        )x
      group by id
      order by id)
TO '/dir/outfile' (FORMAT 'csv', DELIMITER E'\t');;

However, the answer is more about concat(....) than about copy ...

To represent whole table row as a string, where the columns are separated by a comma, you can use the operator concat(t.*).
If there are comma-containing values in the columns of the row, we mast use another delimiter (separator).