A stored procedure can compile and still change its meaning
The syntax errors are the visible part of moving a stored procedure between databases. The quieter problem is a query that compiles in both places but answers a different question. Take a procedure that looks up a case and returns a flag. The normal test has one matching row. It passes. That doesn't tell us what happens when the query finds nothing, or when the data contains two matches. In…
A stored procedure can compile successfully in different databases, but the behavior of the query within the procedure may change. This occurs when a query that works in one database returns a different result in another database. For instance, a stored procedure that looks up a case and returns a flag may return different outcomes when the query finds no matching rows or multiple matches.
In PostgreSQL, `SELECT ... INTO` without `STRICT` assigns the first returned row to the target, whereas with `STRICT`, the procedure checks whether a row was assigned or not. If multiple rows match, the remaining rows are discarded, and the behavior is defined by the order in which the rows are returned. On the other hand, `SELECT ... INTO STRICT` expects exactly one row; if none or multiple rows are found, PostgreSQL raises an error.
Adding `LIMIT 1` to a query can help avoid duplicates, but it may also hide the condition that the original procedure was designed to reject. When debugging, the output may show a warning message instead of raising an error, which can lead to confusion. In PostgreSQL, `RAISE NOTICE` displays a message, while `RAISE EXCEPTION` raises an error and aborts the transaction if not caught.
The migration process should preserve the expected outcomes and not alter the procedure's behavior. For example, if a case is meant to have one receiver, adding `LIMIT 1` might stop the query from complaining about duplicate rows, but it may also mask the condition that the original procedure was supposed to detect. Similarly, using `RAISE NOTICE` instead of `RAISE EXCEPTION` changes the procedure's behavior, as the message is not the same as stopping the work.
Additionally, PostgreSQL procedures support `INOUT` parameters, which are not available in Oracle. Treating this feature as Oracle-only during migration could lead the migration in the wrong direction before the logic is even tested. While it's not necessary to use `STRICT` in every procedure, it's essential to make the intention of the query visible instead of relying on the target database to choose the behavior by accident.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
