How to use Concat function in the sfunc parameter while making a aggregate function using PostgreSQL

Question

I am recently inclined in PostgreSQL and trying to make a ListAggregation function as given here the only difference being that I am trying to use CONCAT instead of TextCat . My function is as under

CREATE AGGREGATE ListAggregation(
      basetype    = Text,
      sfunc       = Concat,
      stype       = Text,
      initcond    = ''
  );

It is throwing error

ERROR:  function concat(text, text) does not exist

********** Error **********

ERROR: function concat(text, text) does not exist
SQL state: 42883

what mistake I am making...please help

N.B.~ I have even looked at the example given here

Thanks

Indeed there's no `concat(text, text)` function. What's wrong with `textcat(text, text)`? — Milen A. Radev, Sep 22 '11 at 09:35

score 1 · Answer 1 · answered Sep 22 '11 at 10:01

1

Interesting, what are you palanning to do? There is already a string_agg() aggregate function in PostgreSQL 9.0+ ...

answered Sep 22 '11 at 10:01

valgog

2,738
1
20
16

As I am learning (this is my 2nd day) PostgreSQL, i am trying some experiemnt. Just want to know what mistake i am making and how can i rectifiy that.Also i know that textcat precisely takes two arguments while concat accepts multiple. so how to get it work. – lightswitchlover Sep 22 '11 at 10:47

score 0 · Answer 2 · answered May 01 '13 at 03:34

You should create state change function sfunc to implement an aggregate with the signature: sfunc( state, value ) ---> next-state

-- sfunc:
CREATE OR REPLACE FUNCTION concat(text, text) 
RETURNS text 
LANGUAGE SQL 
AS $$
  SELECT $1||$2;
$$;

-- Aggregate:
CREATE AGGREGATE ListAggregation(
      basetype    = text,
      sfunc       = concat,
      stype       = text,
      initcond    = ''
);

-- Testing:
WITH test(v) AS (VALUES
  ('AAAA'),
  ('BBBB'),
  ('1111'),
  ('2222') )
SELECT ListAggregation(v) FROM test;

How to use Concat function in the sfunc parameter while making a aggregate function using PostgreSQL

2 Answers2