Yes, hacking into the catalog is bad. Reason #1 is that if you upgrade to new version and forget to move the hack, things start breaking. Just running pg_dump and loading to the same version on another instance will also lose the hack. There's also always the chance that a new version of Postgres will change so much that your hack is now not possible and force you to go back and re-engineer.
Overriding with your own function is the correct way to go.
PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context. I tend to think of it in terms of an ascending scale of power matched by ascending complexity and cost, where you should use the simplest tool that'll do the job well:
- Use views where possible
- Where a view is not suitable, use an SQL function
- Where an SQL function isn't suitable, use PL/PgSQL.
- Where PL/PgSQL is too limited or not expressive enough, use PL/Perl, PL/Python, PL/V8, PL/Java, or whatever your preference is
- ... and where no PL will do the job, use an external program and possibly
LISTEN
and NOTIFY
to talk to it.
Very frequently a view is sufficient when you think a function is needed. Even if it's extremely expensive to SELECT
the whole view, WHERE
clauses in the query referencing the view are usually pushed down into the view and may result in very different query plans. I've often had big performance improvements from converting SQL functions into views.
The main time you find you can't use a view and should consider an SQL function is when:
- Parameters that can't be expressed as simple
WHERE
clauses are needed, like a parameter within a WITH
expression
- You want a security barrier via a
SECURITY DEFINER
function, and the security_barrier
views in PostgreSQL 9.2 and above aren't sufficient for your needs;
- You need parameters that aren't pushed down into sub-clauses of a view by the optimizer and want to control it more directly; or
- There are lots of params or there's lots of repetition of the params, so it's impractical to write the query as a view.
For most of those tasks a plain SQL function works fine, and is often easier to read than PL/PgSQL. SQL functions declared STABLE
or IMMUTABLE
(and not also declared STRICT
or SECURITY DEFINER
) can also be inlined into the calling statement. That gets rid of the function call overhead and can also sometimes result in huge performance benefits when a WHERE condition in the calling function gets pushed down into the SQL function by the optimizer. Use SQL functions whenever they're sufficient for the task.
The main time SQL functions won't do the job is when you need lots of logic. If/then/else operations that you can't express as CASE
statements, lots of re-use of calculated results, building values up from chunks, error handling, etc. PL/PgSQL comes in handy then. Choose PL/PgSQL when you can't use SQL functions or they're a poor fit, like for:
- Dynamic SQL and dynamic DDL via the
EXECUTE
statement
- When you want to
RAISE
errors/warnings for the logs or client
- When you need exception handling - you can trap and handle errors with
EXCEPTION
blocks instead of having the whole transaction terminate on error
- Complex conditional logic that doesn't fit
CASE ... WHEN
very well
- Lots of re-use of calculated values that you can't do fit into
WITH
and CTEs
- Building dynamic records
- You need to perform an action after producing the result set
With common table expressions (CTEs), especially writable CTEs and WITH RECURSIVE
I find I use PL/PgSQL a lot less than I used to because SQL is so much more expressive and powerful. I use views and plain SQL functions a lot more now. It's worth remembering that plain SQL functions can contain more than one statement; the last statement is the function's result.
Best Answer
In Postgres, the cast from
money
tonumeric
is exact, immutable and does not round.In SQL Server the data type
MONEY
uses 8 bytes to store a values in any currency in the numeric range from -922337203685477.5808 to 922337203685477.5807.Matches
numeric(19,4)
.In PostgreSQL the data type
money
also occupies 8 bytes but the allowed range is -92233720368547758.08 to +92233720368547758.07. Same precision, but different scale by default (withoutLC_MONETARY
setting other than 'C') - only two fractional digits.Matches
numeric(19,2)
.But, quoting the manual:
I updated the tag info for money accordingly.
As @a_horse commented (repeatedly),
money
is not defined by the SQL standard.And it's pretty much consensus around here to stay away from the
money
data type if you can.