Error message:

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:  PL/pgSQL function actv_comp(date,date) line 4 at RETURN QUERY

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 MPP feature in greenplus. Only part of the data is saved in each node. GP6 starts to support replication tables, so we need to change the table into a replication table to ensure that there is a complete data in each node.

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

– modify the table distribution to replicated type

alter table tt1 set Distributed REPLICATED;

Second, 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