CREATE TABLE em_tag
(
favorite_id character varying(40),
tag_name character varying(100),
)
数据如下:
favorite_id tag_name
1 测试1
1 测试2
要求结果:
1 测试1,测试2创建函数如下:
CREATE OR REPLACE FUNCTION getname(favorite_id character varying)
RETURNS character varying AS
$$
DECLARE
str character varying;
BEGIN
select str=str||','||tag_name from em_tag where favorite_id=$1;
RETURN str;
END;
$$ LANGUAGE plpgsql;调用语句:
select distinct favorite_id,getname(favorite_id) from em_tag group by favorite_id
报错如下:
ERROR: query has no destination for result data
SQL 状态: 42601
指导建议:If you want to discard the results of a SELECT, use PERFORM instead.
上下文:PL/pgSQL function "getname" line 4 at SQL statement
(
favorite_id character varying(40),
tag_name character varying(100),
)
数据如下:
favorite_id tag_name
1 测试1
1 测试2
要求结果:
1 测试1,测试2创建函数如下:
CREATE OR REPLACE FUNCTION getname(favorite_id character varying)
RETURNS character varying AS
$$
DECLARE
str character varying;
BEGIN
select str=str||','||tag_name from em_tag where favorite_id=$1;
RETURN str;
END;
$$ LANGUAGE plpgsql;调用语句:
select distinct favorite_id,getname(favorite_id) from em_tag group by favorite_id
报错如下:
ERROR: query has no destination for result data
SQL 状态: 42601
指导建议:If you want to discard the results of a SELECT, use PERFORM instead.
上下文:PL/pgSQL function "getname" line 4 at SQL statement
1. 如果用function的话,
应该用循环
str:='';
for tmprec in select tag_name from em_tag where favorite_id=$1;
str:=str||','||tmprec.tag_name;
end loop;2. 你的目的其实是要有一个聚集函数,如MYSQL中的group_concat, 应该自定义一个聚集函数,而不是以上的普通函数,这样效率会好些CREATE AGGREGATE group_concat ( BASETYPE = "text",
SFUNC = "array_append", STYPE = "_text",
INITCOND = "{}");
先将文本聚集成文本数组
再利用array_to_string进行concatselect favorite_id,array_to_string(group_concat(distinct tag_name)) from em_tag group by favorite_id
RETURNS character varying AS
$$
DECLARE
str character varying := '';
i integer := 0;
BEGIN
while i < total loop
str := str || character varying ',' || tag_name from em_tag where favorite_id = $1 limit 1 offset i;
str := left(str,len(str)-1);
i := i + 1;
end loop;
RETURN str;
END;
$$ LANGUAGE plpgsql;