User-defined functions
PRVWJrHSQ/ DOORwV XVHrV WR GHfiQH WhHLr RwQ IXQFWLRQV XVLQJ the CREATE FUNCTION command. These functions can EH wrLWWHQ LQ SQ/, Rr LQ EXLOW-LQ Rr XVHr-GHfiQHG SrRFHGXrDO languages. They can accept arguments of built-in data-types Rr XVHr-GHfiQHG GDWD-WySHV DQG rHWXrQ QRWhLQJ, Rr REMHFWV RI EXLOW-LQ GDWD-WySHV DQG XVHr-GHfiQHG GDWD-WySHV. UVHrGHfiQHG IXQFWLRQV PDy rHDG Rr wrLWH GDWD IrRP WhH GDWDEDVH, DQG FDQ EH RvHrORDGHG. UVHr-GHfiQHG IXQFWLRQV IRrP DQ essential ingredient in the rest of the extensibility features. $OWhRXJh XVHr-GHfiQHG IXQFWLRQV DrH FRPPRQ WR PRVW database systems, PostgreSQL goes a step further and allows functions written in C and stored in shared libraries to be called from the SQL interface. WhLOH FrHDWLQJ D EDVH WySH, XVHrV FDQ SrRvLGH VSHFLfiFDWLRQV for storage, statistical analysis, collation, etc, for better performance and usage.
UVHr-GHfiQHG WySHV FDQ EH HxWHQGHG IXrWhHr Ey GHfiQLQJ operators and casts operating on these types, using the CREATE OPERATOR and CREATE CAST FRPPDQGV. UVHrV FDQ DOVR GHfiQH QHw RSHrDWRrV IRr HxLVWLQJ GDWD-WySHV. $Q operator is syntactic sugar for a call to the actual function WhDW SHrIRrPV WhH ‘RSHrDWLRQ’. ThXV, D XVHr QHHGV WR GHfiQH WhH XQGHrOyLQJ IXQFWLRQ EHIRrH WhH RSHrDWRr FDQ EH GHfiQHG. Operators can be overloaded, i.e., the same operator name (a string containing characters like ‘+', ‘-', ‘>', ‘=', etc, following certain rules) can be used for different operations depending upon the types and number of operands. An RSHrDWRr VSHFLfiFDWLRQ JRHV EHyRQG PHrH ‘VyQWDFWLF VXJDr’, giving additional information that helps query planner RSWLPLVH TXHrLHV, LQvROvLQJ WhDW RSHrDWRr. UVHrV FDQ VSHFLIy: D FRPPXWDWLvH RSHrDWRr, DOORwLQJ TXHry RSWLPLVHrV WR ‘flLS around’ sides of the operator; or negator operator, to ‘absorb’ NOT operator; or restriction selectivity estimator, to estimate the number of rows that satisfy conditions involving the operator; or whether join conditions involving the operator can be hash or merge strategies etc.
GRLQJ D VWHS IXrWhHr, LW LV SRVVLEOH WR GHfiQH RSHrDWRr classes to build the B-tree indices on the columns with XVHr-GHfiQHG GDWD-WySHV. ThLV VHW RI IHDWXrHV DOORwV yRX WR VHDPOHVVOy XVH XVHr-GHfiQHG GDWD-WySHV LQ WhH VDPH wDy DV PostgreSQL built-in types.