Hello Jeremy,
Yes and no
For Stored Functions, the “sql_body” can either be a block with BEGIN
ATOMIC ... END or a single statement RETURN _expression_;
For a Stored Procedure, the “sql_body” is always the block.
Using “language SQL” and an “sql_body” (as per documentation) is certainly
easier (no string constant) and more compatible with the SQL standard. But if
you do so, you loose the possibility to retrieve your actual code from the
database, which, IMO, is very counter intuitive.
Lots of people use, how shall we call it, ‘interactive development’ in
tools (like the one we create) and instead of running a saved and modified
script, you load the objects from the database, you modify it in a database
development tool, test, modify, test etc.
I understand that dependency tracking is useful, and automatic object
modification when doing object renaming is nice, but personally, that would be a
corner case compared to day-to-day development of stored code.
With
regards,
Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird. From: Jeremy Smith
Sent: Friday, December 2, 2022 3:10 PM
Subject: Re: Stored procedure code no longer stored in v14 and v15,
changed behaviour
Isn't this only true if you use the new sql_body/BEGIN ATOMIC syntax,
though? Can't you keep using the older AS 'definition' syntax and still
get the body of the function, unchanged, in the prosrc column? You would,
of course, lose the benefit of the dependency tracking. As a user, though,
I don't find it surprising that creating a function using new syntax
specifically designed to provide dependency tracking would change the way the
function is stored internally.
To me, the new syntax gives the same benefits and drawbacks to SQL
functions that we already have with views, which are also re-written on
creation.
-Jeremy |