How to make a list of quoted strings from the string values of a column in PostgreSQL

Question

Begin with:

select my_col from test;

Out:

my_col
x
y
z

How can I change the output of the three rows into an output of a list of three quoted strings in PostgreSQL, so that it looks like the following?

Out:

'x','y','z'

If I run string_agg(my_val, ''','''), I get

Out:

x','y','z

If I run quote_literal() on top of this output, I get:

Out:

'x'',''y'',''z'

I need this list of quoted strings as an input for the argument of a function (stored procedure). The function works by passing the 'x','y','z' as the argument by hand. Therefore, it is all just about the missing leading and trailing quote.

Side remark, not for the question: it would then get read into the function as variadic _v text[] so that I can check for its values in the function with where t.v = any(_v).

Answer 1

You seem to want:

select string_agg('''' || my_val || '''', ',') my_val_agg
from test

That is: concatenate the quotes around the values before aggregating them - then all that is left is to add the , separator in between.

'''' is there to produce a single quote. We can also use the POSIX syntax in Postgres:

select string_agg(E'\'' || my_val || E'\'', ',') my_val_agg
from test