>> 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(objectidtext, 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
allowingDML in the function. I am testing against a very simple function here but we have many other functions with
thesame problem but those also contain DML so even if I got the STABLE to work in this one test case it does not appear
Icould use in all of my functions with this problem. Are there other options here besides the STABLE option that would
workfor functions that also contain DML?
Thanks
Steve
