[Bug]: Can a T-SQL stored procedure call a PL/pgSQL or native PostgreSQL function in Babelfish?

[chandrapavan507]

What happened?

In Babelfish for PostgreSQL, is it currently possible for a T-SQL stored procedure (created in the tsql dialect) to invoke a function written in PL/pgSQL or any native PostgreSQL function?

For example:

CREATE PROCEDURE tsql_proc
AS
BEGIN
    -- Attempt to call a PG function here
    SELECT native_pg_function();
END;

Is this cross-call supported between the T-SQL layer and PostgreSQL functions?

If not, is there a recommended pattern or workaround to bridge logic between the two execution layers?

Appreciate any guidance or official confirmation on this behavior.

Version

BABEL_4_X_DEV

Extension

None

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

No response

Relevant log output

Code of Conduct

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

[robverschoor]

Technically, this may be possible, but it is not supported from a Babelfish perspective. The reason is that there may be difficult interoperability issues for which it may not even be clear what the "correct" behaviour should be. Please consult https://babelfishpg.org/docs/usage/interoperability/ for more details and some examples.

All that said, it may definitely be possible to get something working across the T-SQL/PG boundary. However, there is no guarantee this will be compatible with future releases of Babelfish, so such an implementation will need to be carefully monitored and supported by the Babelfish/PG user themselves.

[bill-ramos-rmoswi]

Hi @chandrapavan507, In general, you can use PostgreSQL native and extension functions with a few restrictions.

1. Functions like SELECT JUSTIFY_INTERVAL(INTERVAL '31 days 25 hours'); won't work because the T-SQL parser doesn't recognize syntax in a function call. If the function parameters resolve with expressions, literals, columns, or variable references, you are ok. (mostly)
2. Functions that return data types that aren't supported by the TDS protocol, you can use them, but you'll need to cast to a string to use it. For example:

SELECT json_agg(emp_name) AS employee_names
FROM employees;

results in error:

data type json is not supported yet

You can around the issue with CAST

SELECT CAST(json_agg(emp_name AS varchar(max)) AS employee_names
FROM employees;

Produces the desired result

 employee_names
---------------------
 ["Alice", "Bob", "Charlie"]
(1 row)

3. Some functions just don't work right like the PostgreSQL REGEXP_REPLACE() with the global flag. For example:

-- Should replace the the numbers with an empty string
SELECT REGEXP_REPLACE('Order12345', '[0-9]', '', 'g');

Results in error invalid input syntax for type integer: "g"

Fortunately there is a workaround in Babelfish here.

SELECT REGEXP_REPLACE('Order12345', '[0-9]' COLLATE "c", '', CAST('g' AS CHAR(1)));

This works because the cast of the parameter to CHAR(1) avoid an implicit type conversion by Babelfish. You also need to use COLLATE "c" for any function that searches for a pattern.

Go to native PostgreSQL functions for workarounds

Here are my favorite PostgreSQL functions for Babelfish workarounds and faster performance.

• REGEXP_REPLACE() is great for getting rid of all the different ways people have worked around not having the global REPLACE function.
• STRING_TO_TABLE() is faster than the Babelfish supported STRING_AGG() which is currently written in PL/pgSQL because STRING_TO_TABLE() is written in C.
• XPATH() great for XML workarounds.
• UNNEST() great around XPATH() for converting results returned as an ARRAY to rows.
• COALESCE() over ISNULL since COALESCE is written in C and the Babelfish version of ISNULL is a PL/pgSQL function.