How to get a list of quoted strings from the output of a SELECT query that has that list of quoted strings in it, but is of type string?

Question

The following code is not a full setup that you can run to check. It shall just make it a bit clearer what the question is about.

With an example function like this (the variadic example is taken from PostgreSQL inserting list of objects into a stored procedure or PostgreSQL - Passing Array to Stored Function):

CREATE OR REPLACE function get_v(variadic _v text[]) returns table (v varchar(50)) as
$F$
declare
begin
return query 
    select t.v
    from test t
    where t.v = any(_v)
end;
$F$
language plpgsql
;

If you copy the one-value output of a select string_agg... query, 'x','y','z', by hand and put it as the argument of the function, the function works:

SELECT v FROM get_v_from_v(
'x','y','z'
);

The 'x','y','z' gets read into the function as variadic _v text[] so that the function can check its values with where t.v = any(_v).

If you instead put the (select string_agg...) query that is behind that 'x','y','z' output in the same place, the function does not work:

select v from get_v_from_v(
(select string_agg(quote_literal(x.v), ',') from (select v from get_v_from_name('something')) as x)
);

That means: the "one-value output field" 'x','y','z' that comes from the (select string_agg...) query is not the same as the text[] list type: 'x','y','z'.

With get_v_from_name('something') as another function that returns a table of one column and the "v" values in the rows, and after running the string_agg() on its output, you get the 'x','y','z' output. I learnt this working function string_agg() at How to make a list of quoted strings from the string values of a column in postgresql?. The full list of such string functions is in the postgreSQL guide at 9.4. String Functions and Operators.

I guess that the format of the select query output is just a string, not a list, so that the input is not seen as a list of quoted strings, but rather like a string as a whole: ''x','y','z''. The get_v_from_v argument does not need just one string of all values, but a list of quoted strings, since the argument for the function is of type text[] - which is a list.

It seems as if this question does not depend on the query that is behind the output. It seems rather just a general thing that the output in a tuple of a table and taken as the argument of a function is not the same as the same output hand-copied as the same argument.

Therefore, the question. What needs to be done to make the output of a select query the same as the hand-copy of its output, so that the output is just the list 'x','y','z', as if it was just copied and pasted?

PS: I guess that this way of making lists of quoted strings from the one-column table output only to pass it to the function is not best practice. For example, in TSQL/SQL Server, you should pass "table valued parameters", so that you pass values as a table that you select from within the function to get the values, see How do I pass a list as a parameter in a stored procedure?. Not sure how this is done in postgreSQL, but it might be what is needed here.

Answer 1

CREATE OR REPLACE function get_v(_v text[]) returns table (v varchar(50)) as
$F$
declare
begin
return query 
    select t.v
    from test t
    where t.v = any((select * from unnest(_v)))
end;
$F$
language plpgsql
;

With get_v_from_name('something') as another function that returns a table of one column and the "v" values in the rows (this was said in the question), the following works:

select v from get_v_from_v(
(select array_agg(x.v) from (select v from get_v_from_name('something')) as x)
);

Side remark:
array_agg(quote_literal(x.v), ',') instead of array_agg(x.v) fails, the function does not allow a second argument.