[Bug]: Postgres SQL function cannot be used from T-SQL with CTE

[staticlibs]

What happened?

If I create a PG function helper like this (a wrapper to xpath function that returns only the first element from the resulting array, and returns NULL for no results):

create function master_dbo.my_xpath_first(expr sys.nvarchar, doc xml, ns sys.nvarchar)
returns xml as $$
	with cte(arr) as (select xpath(expr::text, doc, ns::text[][]))
	select
		case when array_length(arr, 1) > 0
		then arr[1]
		else null
		end
	from cte
$$ language sql immutable 

It works from T-SQL in simple examples:

select my_xpath_first('//a:item/@id', cast('
<root xmlns="ns1">
    <item id="41"/>
    <item/>
    <item id="43"/>
</root>
' as xml), '{{a, ns1}}')

my_xpath_first
----------------------
41

But it breaks when CTE is used in T-SQL like this:

with items_cte (item) as (
    select unnest(xpath('//a:item', cast('
<root xmlns="ns1">
    <item id="41"/>
    <item/>
    <item id="43"/>
</root>   
    ' as xml), '{{a, ns1}}'))
    as item
)
select 
    my_xpath_first('/a:item/@id', item, '{{a, ns1}}') as id
from items_cte

Msg 33557097, Level 16, State 1, Line 10
zero-length delimited identifier at or near "[]"

There is a workaround to use language plpgsql instead of language sql, but I assume that SQL function is supposed to work too.

Version

BABEL_4_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Fedora

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[tanscorpio7]

I think the root cause is function gets inline to the query and we enter fmgr_security_definer for it & therefore never change the sql_dialect to PG_DIALECT during this function execution. Implementing needs_fmgr_hook should resolve it.