0

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:

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).

questionto42
  • 7,175
  • 4
  • 57
  • 90
  • *I need this list of quoted strings as an input for the parameter of a function (stored procedure).* : that would be suprising. A function typically takes a list of values as input, not a string containing the aggregated values of the arguments. – GMB Dec 07 '22 at 23:36
  • @GMB I was aware of this even without your comment, wanted to fix it, now you saw it before I could get to it. I changed `parameter` to `argument`, I think that is the right wording here. (hopefully) – questionto42 Dec 08 '22 at 12:06

1 Answers1

2

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Alternatively: `string_agg(format('%L', my_val), ',')` or `string_agg(quote_literal(my_val), ',')` –  Dec 08 '22 at 06:44
  • @a_horse_with_no_name Both work, the latter seems easiest to remember and to read, I took it. – questionto42 Dec 08 '22 at 16:12
  • This is not the core of this Q/A anymore: when I now take this list of quoted strings and copy its values by hand into the argument field, it works. But if I use the `(select string_agg...)` instead, it does not work. I guess the input cannot deal with the output, `(select...)` as a field. Could be a new question, but perhaps you know about how to change from a one-field output to a text[] output? – questionto42 Dec 08 '22 at 16:27
  • New question at [How to get a list of quoted strings from a string as the output of a SELECT query that has that list of quoted strings in it, but is of type string?](https://stackoverflow.com/questions/74734762/how-to-get-a-list-of-quoted-strings-from-a-string-as-the-output-of-a-select-quer). – questionto42 Dec 08 '22 at 18:24