I am trying to create an aggregate function
that concatenates
numbers by grouping
them. How can I go about it? Let's say I have a table like this below.
Table Numbers
123
145
187
105
I want the outcome to look like
105_123_145_187
I know how to use group_concat
separator _
if I am working in MySQL.
How can I do it in PostgreSQL?
There is already such function:
SELECT string_agg(num::text,'_')
FROM Numbers;
Details here: string_agg.
Tell me, if you use postgresql 8.4 or earlier version. I will show you, how to implement this function as custom aggregate.
UPD Custom aggregate:
CREATE OR REPLACE FUNCTION public.concat_delimited (text, text, text)
RETURNS text AS
$body$
SELECT $1 || (CASE WHEN $1 = '' THEN '' ELSE $3 END) || $2;
$body$
LANGUAGE 'sql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;
CREATE AGGREGATE public.text_concat (text, text)
(
SFUNC = public.concat_delimited,
STYPE = text
);