>> Thanks for the reply, but that did not seem to help.
> I tried to replicate this as follows:
> --- CUT ---
> create table request(objectid text, productid int, data jsonb); create index
> on request(objectid, productid);
> CREATE OR REPLACE FUNCTION public.steve1(param_requestid text[],
> param_productid integer DEFAULT 1) RETURNS TABLE(objectid text, n text, v
> text, vt integer) LANGUAGE sql AS $function$
> SELECT objectid::text
> , i->>'n'::text
> , i->>'v'::text
> , (i->>'vt') :: INT as vt
> FROM request r
> , jsonb_array_elements(data -> 'i') i WHERE objectid =
> ANY($1)
> AND productid=$2
> $function$
> stable ;
> explain
> SELECT objectid::text, n::text, v::text, vt::int FROM
> steve1(ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251-6723-48a8-8385-55133fab704a'],
> 1);
> --- CUT ---
> and I got:
> QUERY
> PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.15..11.42 rows=100 width=100)
> -> Index Scan using request_objectid_productid_idx on request r
> (cost=0.15..8.17 rows=1 width=64)
> Index Cond: ((objectid = ANY
> ('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}'::text[]))
> AND (productid = 1))
> -> Function Scan on jsonb_array_elements i (cost=0.01..1.00 rows=100
> width=32)
> (4 rows)
> which is what I expected from successful inlining of the function.
> So there are some moving parts in your situation that you've not told us
> about.
> regards, tom lane
Hi Tom,
I ran your code and got similar results so I agree there is more for me to dig
into. I see STABLE has a limit of not allowing DML in the function. I am
testing against a very simple function here but we have many other functions
with the same problem but those also contain DML so even if I got the STABLE to
work in this one test case it does not appear I could use in all of my
functions with this problem. Are there other options here besides the STABLE
option that would work for functions that also contain DML?
Thanks
Steve
