DDL cannot be used within a function

[markrui3]

What happened?

When I execute this SQL:

create function test()
returns int
as 
begin
create table #t1(id int)
return 1
end
go

I got this error:

DDL cannot be used within a function

How can I avoid this error?

Version

BABEL_4_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

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

No response

Relevant log output

No response

Code of Conduct

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

[robverschoor]

This is correct. SQL Server will also raise an error message for this. It is not allowed to perform actions in a function that have side effects beyond that function call.

[markrui3]

This is correct. SQL Server will also raise an error message for this. It is not allowed to perform actions in a function that have side effects beyond that function call.

All right, do you have any rewrite rule for this function to make it run in Babelfish?

[robverschoor]

There is no plan to provide such functionality in Babelfish since this is beyond the T-SQL capabilities. If you want to create such functionality you'd have to do it on PG level using your favorite PG extension. But please be aware there is no support for any unexpected effects.