CODEWITHSUNDEEP
postgresqluser-defined-functionsstring-agg
Manngo
Manngo

Reputation: 16409

Matching data type for string_agg in PostgreSQL

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?

Upvotes: 0

Views: 74

Answers (0)

Related Questions