greenplum 函数调用对象无法在SQL中使用

lkw15209712520 2015-10-26 05:06:47

CREATE OR REPLACE FUNCTION f_channel_id(p_prod_id numeric) RETURNS varchar AS $$
DECLARE
v_channel_id varchar(100) ;
BEGIN
v_channel_id := array(select distinct channel_id from sales where prod_id=44 order by channel_id) ;
return v_channel_id;
END;
$$ LANGUAGE plpgsql immutable;
单独执行ok
sales_demo=# select f_channel_id(44);
f_channel_id
--------------
{4,2,3}
(1 row)
但是如果这样执行
sales_demo=# select prod_id,f_channel_id(prod_id) from sales;

ERROR: function cannot execute on segment because it accesses relation "public.sales" (functions.c:150) (seg10 slice1 sdw3:50002 pid=24798) (cdbdisp.c:1457)
DETAIL:
SQL statement "SELECT array(select distinct channel_id from sales where prod_id=44 order by channel_id)"
PL/pgSQL function "f_channel_id" line 4 at assignment

因工作内容需要，如果用for循环一个一个传入数据太慢了，求大神知道可有办法解决解决这个问题。

...全文