PostgreSQL Advaced Features

1
PostgreSQL Advaced Features

• Gurunandan R. Bhat

2
PostgreSQL Advaced Features

• Interesting Data Types
• User defined types
• Transactions
• Rules and Views
• Stored Procedures
• Triggers
• SPI

3
Interesting Data Types

• IP and MAC Addresses as Data Types
• Operations broadcast, netmask etc
• Geometric shapes
• Point
• Line
• Box
• Polygon
• Circle
• Operations
• Translation, Scaling
• Intersection, Left of, Right of, Above, Below

4
User Defined Types

• Can create any user defined type
• Define a C-struct
• Define an Input function
• Define an output function
• Define C functions that carry out operations
• Map the operators
• You can even optimise the operators
• Define Commutators, Negators etc

5
Interesting SQL Extensions

• CASE WHEN THEN ELSE END
• Coalesce
• Nullif
• ANY SOME ALL
• Row-wise Comparision

6
Stored Procedures

• Supported Languages
• SQL
• PL/SQL (plpgsql)
• Tcl/Tk
• Perl
• C

7
Examples

• An SQL function returning Composite row
• CREATE FUNCTION new_emp() RETURNS EMP AS '
• SELECT text ''None'' AS name,
• 1000 AS salary,
• 25 AS age,
• point ''(2,2)'' AS cubicle
• ' LANGUAGE SQL

8
Examples

• PL/SQL
• Declare
• Begin
• Statements
• End
• Output Logs via RAISE NOTICE/EXCEPTIONN
• Return value via RETURN Statement
• Create Function ltnamegt returns ltvargt as '
• Function Body
• Language 'PLPGSQL'

9
RULES

• Simpler than Triggers
• Faster than Triggers
• Create Rule ltnamegt as on INSERTUPDATEDELETE to
  lttablegt
• Do INSTEAD
• SQL
• END
• Parsed and Executed at the Parser itself
• Watch out for cycles

10
Triggers

• Can be written in any language
• Recursion and Cycles are supported
• Examples
• To be filled in later