Just hearing the question makes me think of two aspects:
ASPECT #1 : Functions are supposed to be DETERMINISTIC
If this so, this implies that a function should present the same return data consistently for a given set of parameters, NO MATTER WHEN YOU CALL THE FUNCTION.
Now, imagine a function that produces a different answer because of gathering data at different times of the day based on static SQL in the function. In a sense, that can still be considered DETERMINISTIC if you query the same set of tables and columns every time, given the same set of parameters.
What if you could change the underlying tables of a function via Dynamic SQL? You be violating the definition of a DETERMINISTIC function.
Notice that MySQL added this option in /etc/my.cnf
log-bin-trust-function-creators
Although this may be an oversimplification to say, this allows functions to be allowed to write data into binary logs without strictly enforcing the DETERMINISTIC property.
ASPECT #2 : Triggers should be able to be rolled back
- Could you imagine a trigger with all the same behaviors as a function and then introducing Dynamic SQL into the mix?
- Could you imagine trying to apply MVCC (Multiversion Concurrecy Control) against Dynamic SQL after applying MVCC to the base table the trigger was meant for?
You would essentially have data that grows quadratically (even exponentially) just in MVCC alone. The process of managing the rollback of SQL with triggers that can be non-DETERMINISTIC would be ungodly complex, to say the least.
In light of these two aspects, I'm sure MySQL Developers thought of these things and quickly dismissed them by imposing restrictions.
So, why lift the restriction for Procedures? Simply put, there is no concern over DETERMINISTIC properties or Rollback.
Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc. That's a holdover from PostQUEL. Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.
Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do. The use of the term "stored procedure" in this context appears to be by analogy to other database products. See this mailing list thread for a vague idea.
In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value. (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.) So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.
Best Answer
There is no way to define stored procedures or stored functions (or events) that are global.
One approach is to create a shared common schema and then qualify the calls to the functions and procedures with the name of that schema (
CALL shared.the_procedure();).This is something I do with my collection of custom date/time calculation functions (e.g.,
SELECT date_time.next_quarter_start_after(NOW())), and with the ever-so-handy common_schema framework, which, of course, lives in `common_schema`.If you take that approach, you have to remember then when a routine is running, the "current" database automatically changes, and the return value of the
DATABASE()function returns the name of the schema under which the routine was defined, not your session's current database. It changes back when the routine exits, so if used in a trigger, it doesn't break anything surrounding it but you don't have a way of knowing from inside the routine what the current database was, if you needed to know.