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 PL/pgSQL, SELECT ... INTO without STRICT assigns the first returned row to the target. With no rows, the target is set to nulls. FOUND lets the procedure check whether a row was assigned. If several rows match, the remaining rows are discarded; without an ORDER BY, which row comes first is not defined.
SELECT ... INTO STRICT asks for exactly one row instead. PostgreSQL raises NO_DATA_FOUND if there are none, and TOO_MANY_ROWS if there is more than one. Its documentation describes this as matching Oracle PL/SQL's SELECT INTO behavior.
That is a behavioral choice, not a punctuation fix. Suppose a case is meant to have one receiver. Adding LIMIT 1 might make a migrated query stop complaining about duplicates. It might also hide the very condition the original procedure was expected to reject.
The difference is easier to see if the tests name the expected outcome. Zero rows: return a negative flag, or raise an error? One row: return the matching value. Two rows: accept one, combine them, or report invalid data? A migration that preserves the happy path but changes those answers has changed the procedure.
The debugging output has a similar trap. In PostgreSQL, RAISE NOTICE reports a message; RAISE EXCEPTION normally raises an error that aborts the current transaction unless it is caught. Choosing one instead of the other changes what happens next. A message saying something went wrong is not the same as stopping the work.
Another correction to my earlier comparison: PostgreSQL procedures support INOUT parameters. Treating that mode as Oracle-only would send the migration in the wrong direction before the logic was even tested.
None of this means every procedure needs STRICT. Some queries intentionally accept any matching row, and some missing records are ordinary outcomes. The important part is making that intention visible rather than letting the target database choose it by accident.
The procedure compiling is useful evidence. The procedure surviving the same awkward inputs is better evidence. Zero rows and two rows often tell us more than the perfectly formed test case.
Adapted and corrected from my Oracle/PostgreSQL comparison on the Nife blog. The PostgreSQL documentation linked below is the reference for the behaviors discussed here.
Top comments (0)