0

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?

Manngo
  • 14,066
  • 10
  • 88
  • 110
  • Char is not equal to varchar, the documentation is clear on that points – nbk Apr 22 '23 at 03:48
  • @nbk Can you point me to that part? I was unaware that `string_agg` returns a `char`. – Manngo Apr 22 '23 at 03:51
  • @nbk According to https://www.postgresql.org/docs/9.6/functions-aggregate.html the return type is the same as the argument types – Manngo Apr 22 '23 at 03:53
  • You see in the link that input is text or byzea and that is also the return type – nbk Apr 22 '23 at 07:24
  • 1
    @nbk OK, I see it now. I used `varchar` but the type should have been `text`, and PostgreSQL does not regard them as the same thing. I regard that as counter-intuitive. Thanks – Manngo Apr 22 '23 at 07:43
  • @Manngo Is there a specific reason why you picked `varchar` out of the [three string types](https://www.postgresql.org/docs/current/datatype-character.html)? Most PostgreSQL [functions](https://www.postgresql.org/docs/current/functions.html) use `text` type on input and output, so making `varchar` a first choice when constructing routines and structures sounds inconvenient. The db will guess and auto-cast most of the time, but not in cases like this one. – Zegarek Apr 22 '23 at 09:37
  • @Zegarek Laziness. My original code used limited length strings such as `varchar(24)`, but I simplified it while testing. It never occurred to me that `string_agg()` doesn’t return `varchar` as it obviously accepts `varchar` parameters, apparently auto-casted. I know that PostgreSQL actually prefers `text` to `varchar`, but I never thought it would be so fussy. – Manngo Apr 23 '23 at 00:28

0 Answers0