GreenPlum -- 'concat ' function in greenplum

Question

Is there 'concat' function in GreenPlum? I can use concat function in postgresql and it works well, but when i use it in Greenplum, I got an error.

select concat('a', 'b');
ERROR:  function concat(unknown, unknown) does not exist at character 8
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.
LINE 1: select concat('a', 'b');
               ^

Is there some other functions can instead of 'concat' function in GreenPlum? And I have tried to create a function to instead of it, but got some syntax errors also.

CREATE OR REPLACE FUNCTION my_concat(VARIADIC arr VARCHAR[] ) RETURNS VARCHAR AS  $$  SELECT array_to_string(arr, '');  $$  LANGUAGE SQL;
ERROR:  syntax error at or near "VARCHAR" at character 51
LINE 1: CREATE OR REPLACE FUNCTION my_concat(VARIADIC arr VARCHAR[] ...
                                                          ^

Anyone can help? Thanks very much!

Answer 1

Like most databases, Greenplum uses "||" to concatenate two strings together.

SELECT 'Green' || 'plum';

Result:

Greenplum

Answer 2

its a versional issue , you have use || in place where ever u using contact function.

Answer 3

Greenplum doesn't have the concat function yet. May be you can modify your code to use "||" instead of concat.

Answer 4

Well,

First I agree that you should replace your code to use the correct SQL syntax '||' for concatenation.

If you really want to create a function to emulate the concat, you could do something like:

create or replace function myschema.concat(arg1 text, arg2 text)
returns text as
$body$
declare
    v_arg1  text;
    v_arg2  text;
begin
    v_arg1  := arg1;
    v_arg2  := arg2;

    return v_arg1 || v_arg2;
end
$body$
language plpgsql volatile;

Then, the query will work:

select myschema.concat('test1', 'test2');
>>test1test2