We may often encounter the need to call custom functions in query, but in Greenplum, if you have query in the function and then call the function in query, you will report it wrong.

Example:
create function

iap=# create or replace function f1() returns text as $$
declare
c1 text;
begin
execute 'select info from tt1 limit 1' into c1;
return c1;
end;
$$ language plpgsql;

Invocation in query:

iap=# select f1() from tt1;
ERROR:  query plan with multiple segworker groups is not supported
HINT:  likely caused by a function that reads or modifies data in a distributed table
CONTEXT:  SQL statement "select info from tt1 limit 1"
PL/pgSQL function f1() line 5 at EXECUTE statement

This is due to the feature of MPP in greenplus. Only part of the data is saved in each node. GP6 supports copying tables. Then we need to change the table into a copying table to ensure that each node has a complete data.

In addition, we need to modify the function to immutable.

Change the table distribution to replicated type

iap=# alter table tt1 set Distributed REPLICATED;
ALTER TABLE

– modify the function to immutable type

iap=# create or replace function f1() returns text as $$
declare
c1 text;
begin
execute 'select info from tt1 limit 1' into c1;
return c1;
end;
$$ language plpgsql immutable;
CREATE FUNCTION

Call again:

iap=# select f1() from tt1 limit 1;
                f1
----------------------------------
 d810ed19ec188ddf3af8a14dbd341c3c
(1 row)

Summary:
, if you need to call UDF function in query, if you encounter “ERROR: query plan with multiple segworker groups is is,” then the solution is as follows:

modify table to copy table; Modify the function to immutable type