0

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.

questionto42
  • 7,175
  • 4
  • 57
  • 90
  • You need to provide a complete working example as update to your question, in particular the `select` query. – Adrian Klaver Dec 08 '22 at 19:23
  • Though I'm going to guess it will need something like: `select array_agg(t.val) from (values ('x') , ('y'), ('z')) as t(val); {x,y,z}`. – Adrian Klaver Dec 08 '22 at 19:28
  • @AdrianKlaver I changed quite a bit, but for now, I stop putting more time into this. It seems as if this setup is too strange, does not seem to be best practice. My aim over all is: start with `get_v_from_name()` and take its one-column-table output as the argument of `get_v_from_v()` and then call `get_v_from_v()` many times on its own output afterwards until some (hierarchical) mappings are checked deeply enough. Hard to make an easy test of it, and I guess that this is rather a basic problem of how to deal with a query output as an argument of a function. – questionto42 Dec 08 '22 at 20:59
  • Well `string_agg` is doing what it is supposed to aggregating a set of values into a string. If I am following correctly what you want is what I proposed earlier `array_agg`. So: `select array_agg(quote_literal(x.v), ',') ...`. – Adrian Klaver Dec 08 '22 at 22:27
  • @AdrianKlaver Ah good, that could be it. I thought at first I would stick to the "easiest" `variadic` parameter so that I could pass a plain list instead of an array. It just seemed easiest when I tested with the copied an pasted `'x','y','z'` input. But if you now mean that only/at best only `array_agg()` should be taken for this, that may be a game changer. – questionto42 Dec 08 '22 at 22:32
  • @AdrianKlaver yes, worked. I had to change the function. Firstly, drop the `variadic` so that it would start with: `get_v(_v text[])` instead. Secondly, change to: `where t.v = any((select * from unnest(_v)))` since you have to unnest your table even if it is just a one-column table (I guessed this from [PostgreSQL inserting list of objects into a stored procedure](https://stackoverflow.com/a/51641876/11154841) --> "Array"). Thirdly, change to: `select v from get_v_from_v( (select array_agg(x.v) from (select v from get_v_from_name('something')) as x) );`. Works also when nested many times. – questionto42 Dec 08 '22 at 23:15
  • @AdrianKlaver You can answer this shortly, you may take the outcome from my comment or keep it on a higher level only. Helped me to fix this anyway. One small thing: `array_agg(quote_literal(x.v), ',')`, in one of your comments above, is not allowed, it has to be just one argument, thus, `array_agg(x.v)` is all you can have in there, and you do not need to care about the quotes anymore. – questionto42 Dec 08 '22 at 23:37

1 Answers1

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

questionto42
  • 7,175
  • 4
  • 57
  • 90