ERROR: function cannot execute on segment because it accesses relation "public.top_bash_trees" (functions.c:151) (entry db sdapmdw:5432 pid=15071) (cdbdisp.c:1477)
DETAIL:
SQL statement "select distinct tree_version from top_bash_trees order by tree_version DESC"
PL/pgSQL function "mk_getlatesttreetprate" line 6 at for over execute statement
Function :
CREATE or REPLACE FUNCTION mk_getLatestTreeTPRate() RETURNS SETOF record
LANGUAGE plpgsql
immutable
AS $$
declare
r record;
ff varchar:='SELECT server_date,';
treeVersionList varchar:='select distinct tree_version from top_bash_trees order by tree_version DESC';
begin
for r in execute treeVersionList
loop
ff:=ff || 'max(case when tree_version='''||r.tree_version || '''' || ' then percent_tree_tp_rate end ) as "'||r.tree_version||'",';
end loop;
ff:=substr(ff,1,length(ff)-1) || ' from tree_tp_rate group by server_date order by server_date DESC';
RAISE NOTICE '%', ff;
FOR r IN EXECUTE ff LOOP
--RAISE NOTICE '%', r;
RETURN NEXT r;
END LOOP;
end;
$$
;
query that throws error :
INSERT into <table>
select a.server_date, a.tree_1, a.tree_2, a.tree_3, a.tree_4, a.tree_5, a.tree_6 from mk_getLatestTreeTPRate() as a(server_date date,tree_1 numeric,tree_2 numeric,tree_3 numeric,tree_4 numeric,tree_5 numeric,tree_6 numeric)
No Idea what I can do to resolve this.
How ever simple select runs absolutely fine , Insert into/ create table as statements both throw same error
Message was edited by: bitsanudeep
