PostgreSQL数据库中Concat函数创建

创建数组类型的concat函数

CREATE OR REPLACE FUNCTION concat(VARIADIC str text[])
  RETURNS text AS
$BODY$
DECLARE
  result text := '';
BEGIN
  FOR i IN 1..array_length(str, 1) LOOP
    result := result::text || str[i]::text;
  END LOOP;
  RETURN result;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(VARIADIC str bigint[])
  RETURNS text AS
$BODY$
DECLARE
  result text := '';
BEGIN
  FOR i IN 1..array_length(str, 1) LOOP
    result := result::text || str[i]::text;
  END LOOP;
  RETURN result;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(VARIADIC str integer[])
  RETURNS text AS
$BODY$
DECLARE
  result text := '';
BEGIN
  FOR i IN 1..array_length(str, 1) LOOP
    result := result::text || str[i]::text;
  END LOOP;
  RETURN result;
END;
$BODY$
LANGUAGE plpgsql;

其他类型（根据需要选择）

CREATE OR REPLACE FUNCTION concat(str1 text,num1 numeric,str2 text)
  RETURNS text AS
$BODY$
BEGIN
  return concat(str1,num1::text,str2);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(str1 text,num1 numeric)
  RETURNS text AS
$BODY$
BEGIN
  return concat(str1,num1::text);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(str1 text,num1 numeric,num2 numeric)
  RETURNS text AS
$BODY$
BEGIN
  return concat(str1,num1::text,num2::text);
END;
$BODY$
LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION ourea_yc.mysql_concat(num1 numeric,num2 numeric,num3 numeric)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text,num3::text);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(num1 numeric,str1 text)
  RETURNS text AS
$BODY$
BEGIN
  return concat(num1::text,str1);
END;
$BODY$
LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION ourea_yc.mysql_concat(num1 numeric,num2 numeric)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(num1 numeric,str1 text,str2 text)
  RETURNS text AS
$BODY$
BEGIN
  return concat(num1::text,str1,str2);
END;
$BODY$
LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION ourea_yc.mysql_concat(num1 integer,num2 integer)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION ourea_yc.mysql_concat(num1 bigint,num2 bigint)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION ourea_yc.mysql_concat(num1 bigint,num2 bigint,num3 bigint)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text,num3::text);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION ourea_yc.mysql_concat(num1 integer,num2 integer,num3 integer)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text,num3::text);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(num1 integer,num2 integer,str1 text)
  RETURNS text AS
$BODY$
BEGIN
  return concat(num1::text,num2::text,str1);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(num1 integer,num2 text,str1 integer)
  RETURNS text AS
$BODY$
BEGIN
  return concat(num1::text,num2::text,str1);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(num1 text,num2 integer,str1 integer)
  RETURNS text AS
$BODY$
BEGIN
  return concat(num1::text,num2::text,str1);
END;
$BODY$
LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION concat(num1 text,num2 text,str1 integer)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text,str1);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(num1 bigint,num2 bigint,str1 text)
  RETURNS text AS
$BODY$
BEGIN
  return concat(num1::text,num2::text,str1);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(num1 bigint,num2 text,str1 bigint)
  RETURNS text AS
$BODY$
BEGIN
  return concat(num1::text,num2::text,str1);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION concat(num1 text,num2 bigint,str1 bigint)
  RETURNS text AS
$BODY$
BEGIN
  return concat(num1::text,num2::text,str1);
END;
$BODY$
LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION concat(num1 text,num2 text,str1 bigint)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text,str1);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION concat(num1 text,num2 text,str1 bigint)
--   RETURNS text AS
-- $BODY$
-- BEGIN
--   return concat(num1::text,num2::text,str1);
-- END;
-- $BODY$
-- LANGUAGE plpgsql;
-- CREATE OR REPLACE FUNCTION ourea_yc.mysql_concat(VARIADIC args text[])
--   RETURNS text AS
-- $$
-- BEGIN
--   RETURN array_to_string(args, '');
-- END;
-- $$
-- LANGUAGE plpgsql;

-- CREATE OR REPLACE FUNCTION ourea_yc.mysql_concat(VARIADIC args numeric[])
--   RETURNS text AS
-- $$
-- BEGIN
--   RETURN array_to_string(args::text, '');
-- END;
-- $$
-- LANGUAGE plpgsql;

---

测试

CONCAT('%',1,'%')

CONCAT('%','1','%')


-- mysql_concat(t,t)
-- mysql_concat(t,n)
-- mysql_concat(n,t)
-- mysql_concat(t,t,t)
-- mysql_concat(t,n,t)

SELECT concat('Hello', 123, true);

select concat('abc','wewewf');
select concat('%','1','%');
select concat('1','2','%','^','w')
select concat('1',2,3)
select concat('1',2)
select concat('1',2,'3')
select concat('1','2',3)
select concat(1,'2','3')
select concat(1,'2')
select concat(1,2,3)
select concat(1,2)
select concat(1,2,'3')
select concat(1,'2',3)
select concat(1,2,3,4,5,6)
select concat(1,'2',3,'4',5,'6')
select concat('1','2',3,'4',5,6)
select concat('1','2',null,'4',5,6)

select concat('1',2,'/')
select concat('1','/',3)
select concat('/',2,3)
select concat('/',2::integer,999999999::bigint)


select concat('/','2','3','4','5','6')

select concat((select id from t_resource limit 1),(select id from t_resource limit 1))




select concat_ws(',',1,2,'3','4','5')
select concat_ws(',','1',2,3,'4','5')