Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
	id int generated by default as identity primary key,
	category varchar(12),
	data varchar(12)
)

CREATE TABLE


INSERT INTO test (category, data)
VALUES
	('fruit','apple'),
	('fruit','banana'),
	('fruit','cherry'),
	('animals','aardvark'),
	('animals','bilby'),
	('animals','cassowary'),
	('instruments','accordion'),
	('instruments','banjo'),
	('instruments','cor anglais');

INSERT 0 9


  	WITH cte AS (
		SELECT category, string_agg(data,'|') AS items
		FROM test
  		WHERE category='instruments'
		GROUP BY category
	)
	SELECT category, items FROM cte;


category
items


instruments
accordion|banjo|cor anglais



SELECT 1


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;
END $$;

CREATE FUNCTION


SELECT * FROM doit('instruments');

ERROR:  structure of query does not match function result type
DETAIL:  Returned type text does not match expected type character varying in column 2.
CONTEXT:  SQL statement "WITH cte AS (
		SELECT category, string_agg(data,'|') AS alldata
		FROM test
  		WHERE category=selectedcategory
		GROUP BY category
	)
	SELECT category, alldata FROM cte"
PL/pgSQL function doit(character varying) line 2 at RETURN QUERY