I have a simple Table Valued Query, where I include a string_agg() function. There is a full example at https://dbfiddle.uk/4WG7crbI (I know that the CTE is redundant, but it’s a simplification of a more complex function I’m working on).
The tricky part is something like this:
CREATE FUNCTION doit(selectedcategory varchar)
RETURNS TABLE(cat varchar, items varchar)
LANGUAGE PLPGSQL
AS $$ BEGIN
RETURN QUERY
WITH cte AS (
SELECT category, string_agg(data,'|') AS alldata
FROM test
WHERE category=selectedcategory
GROUP BY category
)
SELECT category, alldata FROM cte; -- alldata::varchar works
END $$;
In the RETURNS clause, I include items varchar. The returned value is string_agg(data,'|').
The error I get is:
DETAIL: Returned type text does not match expected type character varying in column 2.
I can fix it up if I use SELECT category, alldata::varchar FROM cte;. The thing is, isn’t the string_agg() function supposed to return a string anyway? Why would it be incompatible with VARCHAR and is there a better way than casting the result?