Index
A
- acquisition
- cost / Cost of acquisition
- add(int, int)
- functionality, adding / Adding functionality to add(int, int)
- NULL arguments, handling / Smart handling of NULL arguments
- any number of arguments, working with / Working with any number of arguments
- add_func.c / add_func.c
- add_func.sql.in / add_func.sql.in
- AFTER trigger / Disallowing DELETE
- ALTER EXTENSION ADD command
- ANY parameter / Other parameters
- application design
- about / Application design
- databases, drawbacks / Databases are considered harmful
- databases / Databases are considered harmful
- encapsulation / Encapsulation
- PostgreSQL / What does PostgreSQL offer?
- data locality / Data locality
- arguments
- about / Working with any number of arguments
- records, handling as / Handling records as arguments or returned values
- argument tuple
- fields, extracting from / Extracting fields from an argument tuple
- arrays
- looping / Looping Through Arrays
- assert, PL/Python
- using / Using assert
- audit trail
- creating / Creating an audit trail
- audit trigger / The audit trigger
B
- backends
- synchronizing between / Synchronizing between backends
- BEFORE trigger / Disallowing DELETE
- BIRT / Third-party tools
C
- .control file, extension
- about / The .control file
- C
- additional resources / Additional resources for C
- C++
- functions, writing in / Writing functions in C++
- caching
- about / Caching
- cancel trigger / Disallowing DELETE
- C code, writing
- about / Basic guidelines for writing C code
- memory, allocating / Memory allocation
- palloc(), using / Use palloc() and pfree()
- pfree(), using / Use palloc() and pfree()
- structures, zero filling / Zero-fill the structures
- files, including / Include files
- symbol names, public / Public symbol names
- C function
- about / The simplest C function – return (a + b)
- return (a + b) / The simplest C function – return (a + b)
- add_func.c / add_func.c
- Makefile function / Makefile
- CREATE FUNCTION add(int, int) / CREATE FUNCTION add(int, int)
- add_func.sql.in function / add_func.sql.in
- writing / Summary for writing a C function
- C functions
- error, reporting / Error reporting from C functions
- error, states / "Error" states that are not errors
- messages, sent to client / When are messages sent to the client?
- changes
- auditing / Auditing changes
- CLUSTER statement / CONNECT, CLUSTER, and RUN ON
- code
- examples / About this book's code examples
- commit / Doing something at commit/rollback
- Common Language Runtime (CLR) / Procedural languages
- community
- about / Community
- COMMUTATOR clause / COMMUTATOR
- composite-type arguments, PL/Tcl
- passing / Passing composite-type arguments
- conditional expressions
- about / Conditional expressions
- URL / Conditional expressions
- loops, with counters / Loops with counters
- query results, looping / Looping through query results
- PERFORM command versus SELECT command / PERFORM versus SELECT
- looping, through arrays / Looping Through Arrays
- conditional triggers / Conditional triggers
- CONNECT statement / CONNECT, CLUSTER, and RUN ON
- context manager
- URL / Handling exceptions
- contrib
- Coordinated Universal Time (UTC) / The audit trigger
- cost
- about / More control
- CREATE FUNCTION add(int, int) / CREATE FUNCTION add(int, int)
- ctags
- cursors
- returning / Returning cursors
- returned from another function, iterating over / Iterating over cursors returned from another function
- pros / Wrapping up of functions returning cursors
- cons / Wrapping up of functions returning cursors
- URL / Wrapping up of functions returning cursors
D
- data
- cleaning / Data cleaning
- partitioning, across multiple servers / Data partitioning across multiple servers
- splitting / Splitting the data
- distributing / The distribution of data
- moving, from single to partitioned database / Moving data from the single to the partitioned database
- database
- changes, fast capturing / Fast capturing of database changes
- database, scaling
- single-server chat, creating / Creating a simple single-server chat
- tables, splitting over multiple databases / Dealing with success – splitting tables over multiple databases
- data, moving from single to partitioned database / Moving data from the single to the partitioned database
- database-backed systems, growing
- ways / What expansion plans work and when?
- bigger server, moving to / Moving to a bigger server
- Master-slave replication / Master-slave replication – moving reads to slave
- Multi-master replication / Multimaster replication
- database abstraction layer / Databases are considered harmful
- databases, PL/Tcl
- accessing / Accessing databases
- data changes
- visibility / Visibility of data changes
- data comparisons
- operators used / Data comparisons using operators
- data definition language (DDL) / Auditing changes
- Data Manipulation Language (DML) operation / Working on a simple "Hey, I'm called" trigger
- data wrappers
- Datum / Interlude – what is Datum?
- DB API 2 / Running queries in the database
- ddl_command_end event / Creating event triggers
- ddl_command_start event / Creating event triggers
- debugging
- manual debugging, with RAISE NOTICE / Manual debugging with RAISE NOTICE
- visual debugging / Visual debugging
- debugging, manual
- exceptions, throwing / Throwing exceptions
- URL / Throwing exceptions
- file, logging to / Logging to a file
- RAISE NOTICE, advantages / The advantages of RAISE NOTICE
- RAISE NOTICE, disadvantages / The disadvantages of RAISE NOTICE
- DELETE trigger
- disallowing / Disallowing DELETE
- developers
- availability / Availability of developers
- don't repeat yourself (DIY) / DRY – don't repeat yourself
- dynamic link library (DLL) / Procedural languages
E
- EnterpriseDB
- error
- reporting, from C functions / Error reporting from C functions
- states / "Error" states that are not errors
- NOTICE / "Error" states that are not errors
- INFO / "Error" states that are not errors
- LOG / "Error" states that are not errors
- reporting, URL / When are messages sent to the client?
- error handling / General error reporting and error handling
- error reporting / General error reporting and error handling
- ERROR trigger / Disallowing DELETE
- event triggers
- use cases / Use cases for creating event triggers
- creating / Creating event triggers
- ddl_command_start event / Creating event triggers
- ddl_command_end event / Creating event triggers
- sql_drop event / Creating event triggers
- URL / Creating event triggers, A roadmap of event triggers
- audit trail, creating / Creating an audit trail
- roadmap / A roadmap of event triggers
- event triggers, PL/pgSQL functions
- TG_TAG / Creating event triggers
- TG_EVENT / Creating event triggers
- exceptions, PL/Python
- handling / Handling exceptions
- exceptions, RAISE NOTICE
- throwing / Throwing exceptions
- expanded display
- switching to / Switching to the expanded display
- extensibility / What can't be extended?
- extension
- creating / When to create an extension
- URL / When to create an extension, The .control file
- unpackaged / Unpackaged extensions
- versions / Extension versions
- .control file / The .control file
- building / Building an extension
- installing / Installing an extension
- viewing / Viewing extensions
- publishing / Publishing your extension
- installing, from PGXN / Installing an extension from PGXN
- extension, publishing
- PostgreSQL Extension Network / Introduction to PostgreSQL Extension Network
- signing up / Signing up to publish your extension
- extension project, creating / Creating an extension project the easy way
- metadata, providing / Providing the metadata about the extension
- extension code, writing / Writing your extension code
- package, creating / Creating the package
- extensions / add_func.sql.in
F
- file_fdw
- fillfactor
- foreign data wrappers (FDW)
- using / Using foreign data wrappers
- function
- used, for configuring PL/Proxy cluster / Configuring the PL/Proxy cluster using functions
- function overloading / User-defined functions
G
- General Inverted Index (GIN) / Type extensibility
- Git repository
I
- immutable fields trigger / The immutable fields trigger
- index access methods
- creating / Creating index access methods
- URL / Creating index access methods
- integer set
- returning / Returning a set of integers
K
- keep it simple stupid (KISS) / KISS – keep it simple stupid
- k nearest neighbor (KNN) / Type extensibility
L
- licensing
- about / Licensing
- light-weight locks (LWLocks) / Synchronizing between backends
- log trigger / A log trigger
- looping syntax
- URL / Loops with counters
- loops
- with counters / Loops with counters
- statement, terminating / Statement termination
M
- Makefile function / Makefile
- Master-slave replication / Master-slave replication – moving reads to slave
- metadata
- providing, for extension / Providing the metadata about the extension
- Multi-master replication
- about / Multimaster replication
- Multiversion Concurrency Control (MVCC) / Visibility
N
- NEGATOR clause / NEGATOR
- NEW record
- modifying / Modifying the NEW record
- NULL arguments
- handling / Smart handling of NULL arguments
O
- operator
- new operator, creating / Creating a new operator
- overloading / Overloading an operator
- optimizing / Optimizing operators
- operator, optimizing
- COMMUTATOR / COMMUTATOR
- NEGATOR / NEGATOR
- operators
- used, for data comparisons / Data comparisons using operators
- PostgreSQL documentation, URL / Overloading an operator
- optional clauses
- URL / Overloading an operator
- os.walk()
- OUT parameters
- and records / OUT parameters and records
- about / OUT parameters
- records, returning / Returning records
- RETURNS TABLE, using / Using RETURNS TABLE
- no predefined structure, returning with / Returning with no predefined structure
- SETOF ANY, returning / Returning SETOF ANY
- variadic argument lists / Variadic argument lists
P
- package
- creating / Creating the package
- submitting, to PGXN / Submitting the package to PGXN
- palloc()
- using / Use palloc() and pfree()
- parameters
- about / Other parameters
- Pentaho data integration (kettle) / Third-party tools
- Pentaho Report Server / Third-party tools
- PERFORM command
- versus SELECT command / PERFORM versus SELECT
- pfree()
- using / Use palloc() and pfree()
- pgAdmin3 / Third-party tools
- installing / Installing pgAdmin3
- pgfoundry
- PGXN
- package, submitting / Submitting the package to PGXN
- URL / Submitting the package to PGXN
- extension, installing from / Installing an extension from PGXN
- FDWs URL / Using foreign data wrappers
- php5-postgresql / Third-party tools
- pl/lolcode
- URL / Summary
- PL/Perl
- using / When to use PL/Perl
- installing / Installing PL/Perl
- function / A simple PL/Perl function
- function, URL / A simple PL/Perl function, Passing and returning non-scalar types, Writing PL/Perl triggers
- non-scalar types, passing / Passing and returning non-scalar types
- non-scalar types, returning / Passing and returning non-scalar types
- triggers, writing / Writing PL/Perl triggers
- untrusted Perl / Untrusted Perl
- PL/pgSQL
- used, for integrity checks / Using PL/pgSQL for integrity checks
- about / Why PL/pgSQL?
- disadvantages / Why PL/pgSQL?
- URL / Why PL/pgSQL?
- advantages / Why PL/pgSQL?
- PL/pgSQL debugger
- about / Visual debugging
- URL / Visual debugging
- installing / Installing the debugger
- PostgreSQL Windows installer, URL / Installing the debugger
- installing, from source / Installing the debugger from the source
- pgAdmin3, installing / Installing pgAdmin3
- using / Using the debugger
- advantages / The advantages of the debugger
- disadvantages / The disadvantages of the debugger
- PL/pgSQL function
- structure / The structure of a PL/pgSQL function
- arguments, accessing / Accessing function arguments
- results / Acting on the function's results
- PL/pgSQL TRIGGER function
- OLD, NEW / Variables passed to the PL/pgSQL TRIGGER function
- TG_NAME / Variables passed to the PL/pgSQL TRIGGER function
- TG_WHEN / Variables passed to the PL/pgSQL TRIGGER function
- TG_LEVEL / Variables passed to the PL/pgSQL TRIGGER function
- TG_OP / Variables passed to the PL/pgSQL TRIGGER function
- TG_RELID / Variables passed to the PL/pgSQL TRIGGER function
- TG_TABLE_NAME / Variables passed to the PL/pgSQL TRIGGER function
- TG_TABLE_SCHEMA / Variables passed to the PL/pgSQL TRIGGER function
- TG_NARGS, TG_ARGV[] / Variables passed to the PL/pgSQL TRIGGER function
- TG_TAG / Variables passed to the PL/pgSQL TRIGGER function
- PL/Proxy
- about / PL/Proxy – the partitioning language
- installing / Installing PL/Proxy
- URL / Installing PL/Proxy
- syntax / The PL/Proxy language syntax
- CONNECT statement / CONNECT, CLUSTER, and RUN ON
- CLUSTER statement / CONNECT, CLUSTER, and RUN ON
- RUN ON statement / CONNECT, CLUSTER, and RUN ON
- SELECT statement / SELECT and TARGET
- TARGET statement / SELECT and TARGET
- SPLIT statement / SPLIT – distributing array elements over several partitions
- data, distributing / The distribution of data
- connection pooling / Connection Pooling
- PL/Proxy cluster
- configuring, functions used / Configuring the PL/Proxy cluster using functions
- configuring, SQL/MED used / Configuring the PL/Proxy cluster using SQL/MED
- PL/Python
- about / Why PL/Python?, Quick introduction to PL/Python
- function / A minimal PL/Python function
- data type conversions / Data type conversions
- documentation, URL / Data type conversions
- simple functions, writing / Writing simple functions in PL/Python, A simple function
- record, returning from Python function / Functions returning a record
- table functions / Table functions
- queries, running in database / Running queries in the database
- trigger functions, writing / Writing trigger functions in PL/Python
- exceptions, handling / Handling exceptions
- atomicity / Atomicity in Python
- debugging / Debugging PL/Python
- PL/Python, debugging
- about / Debugging PL/Python
- function progress tracking, plpy.notice() used / Using plpy.notice() to track the function's progress
- assert used / Using assert
- sys.stdout, redirecting / Redirecting sys.stdout and sys.stderr
- sys.stderr, redirecting / Redirecting sys.stdout and sys.stderr
- PL/Tcl
- installing / Installing PL/Tcl
- function / A simple PL/Tcl function
- Strict functions, null checking with / Null checking with Strict functions
- parameters / The parameter format
- arrays, passing / Passing and returning arrays
- arrays, returning / Passing and returning arrays
- composite-type arguments, passing / Passing composite-type arguments
- database, accessing / Accessing databases
- function, URL / Accessing databases, Writing PL/Tcl triggers
- triggers, writing / Writing PL/Tcl triggers
- untrusted Tcl / Untrusted Tcl
- PlpgGetNames() function / Returning a record
- plpy.notice()
- used, for tracking functions progress / Using plpy.notice() to track the function's progress
- plugins / Procedural languages
- polymorphic types / Other parameters
- PostgreSQL
- acquisition cost / Cost of acquisition
- developers, availability / Availability of developers
- licensing / Licensing
- predictability / Predictability
- community / Community
- procedural languages / Procedural languages
- controls / More control
- URL / More control
- documentation, URL / Returning cursors
- manual, URL / Additional resources for C
- internals, URL / Additional resources for C
- postgresql.conf file
- PostgreSQL documentation
- URL / Writing functions in C++
- PostgreSQL Extension Network
- PostgreSQL functions
- PostgreSQL license
- PostgreSQL Version 9.3 functions
- predictability
- about / Predictability
- procedural languages
- about / Procedural languages
- third-party tools / Third-party tools
- platform compatibility / Platform compatibility
- application design / Application design
- pseudotypes
- URL / Other parameters
- psycopg2 / Third-party tools
- Python Database API Specification v2.0 / Running queries in the database
- Python Database API Specification v2.0 (DB API 2) / Running queries in the database
- Python Imaging Library (PIL) module / Generating thumbnails when saving images
Q
- QCubed / Third-party tools
- queries, PL/Python
- running, in database / Running queries in the database
- simple queries, running / Running simple queries
- prepared queries, using / Using prepared queries
- prepared queries, caching / Caching prepared queries
- constructing / Constructing queries
- query results
- looping / Looping through query results
R
- RAISE NOTICE
- manual debugging with / Manual debugging with RAISE NOTICE
- advantages / The advantages of RAISE NOTICE
- URL / The advantages of RAISE NOTICE
- disadvantages / The disadvantages of RAISE NOTICE
- Read Committed / Transactions
- record
- returning / Returning a record
- returning, from Python function / Functions returning a record
- records
- and OUT parameters / OUT parameters and records
- returning / Returning records
- handling, as arguments / Handling records as arguments or returned values
- complex type single tuple, returning / Returning a single tuple of a complex type
- fields, extracting from argument type / Extracting fields from an argument tuple
- return tuple, constructing / Constructing a return tuple
- Datum / Interlude – what is Datum?
- set, returning / Returning a set of records
- replication
- Master-slave replication / Master-slave replication – moving reads to slave
- Multi-master replication / Multimaster replication
- return (a + b) / The simplest C function – return (a + b)
- RETURN SETOF variants
- RETURNS TABLE
- using / Using RETURNS TABLE
- return tuple
- constructing / Constructing a return tuple
- rollback / Doing something at commit/rollback
- rows
- returning, from function / Using a set returning function
- rowsets / Sets and arrays
- RUN ON statement / CONNECT, CLUSTER, and RUN ON
S
- schema changes
- preventing / Preventing schema changes
- SELECT command
- versus PERFORM command / PERFORM versus SELECT
- SELECT statement / SELECT and TARGET
- server
- data, partitioning across multiple servers / Data partitioning across multiple servers
- server programming
- about / Moving beyond simple functions
- best practices / Programming best practices
- keep it simple stupid (KISS) / KISS – keep it simple stupid
- don't repeat yourself (DRY) / DRY – don't repeat yourself
- you ain't gonna need it (YAGNI) / YAGNI – you ain't gonna need it
- service-oriented architecture (SOA) / SOA – service-oriented architecture
- service-oriented architecture (SOA) / SOA – service-oriented architecture
- set-returning function (table function)
- using / Using a set returning function
- set-returning functions (SRF) / Returning a record
- SETOF ANY
- returning / Returning SETOF ANY
- sets
- about / Sets and arrays
- returning / Returning sets
- integer sets, returning / Returning a set of integers
- set-returning function, using / Using a set returning function
- rows, returning from function / Using a set returning function
- sever programming
- about / Why program in the server?
- advantages / Wrapping up – why program in the server?, Ease of maintenance
- single-server chat
- specifications / Creating a simple single-server chat
- implementing / Creating a simple single-server chat
- skytools
- smtplib
- URL / Sending an e-mail
- sort orders
- custom / Custom sort orders
- SPI
- used, for sample C function / A sample C function using SPI
- SPI_* functions / More info on SPI_* functions
- SPI_exec() function / A sample C function using SPI
- SPLIT statement
- SQL/MED
- URL / The distribution of data
- used, for configuring PL/Proxy cluster / Configuring the PL/Proxy cluster using SQL/MED
- SQL database server
- about / Thinking out of the "SQL database server" box
- thumbnails, creating / Generating thumbnails when saving images
- e-mail, sending / Sending an e-mail
- directory contents, listing / Listing directory contents
- SQL queries
- running, inside database / Running queries and calling PostgreSQL functions
- sample C function, SPI used / A sample C function using SPI
- data changes, visibility / Visibility of data changes
- SPI_* functions / More info on SPI_* functions
- sql_drop event / Creating event triggers
- Strict functions
- null checking with / Null checking with Strict functions
- structured data
- about / Other ways to work with structured data
- data types, complex / Complex data types for the modern world – XML and JSON
- XML data type / XML data type and returning data as XML from functions
- data, returning as XML from functions / XML data type and returning data as XML from functions
- data, returning in JSON format / Returning data in the JSON format
- sys.stderr, PL/Python
- redirecting / Redirecting sys.stdout and sys.stderr
- sys.stdout, PL/Python
- redirecting / Redirecting sys.stdout and sys.stderr
T
- table functions / Table functions
- tables
- splitting, over multiple databases / Dealing with success – splitting tables over multiple databases
- Talend / Third-party tools
- TARGET statement / SELECT and TARGET
- Tcl
- transactions
- about / Transactions
- isolation methods, URL / Transactions
- trigger
- function, creating / Creating the trigger function
- creating / Creating the trigger
- simple trigger, creating / Working on a simple "Hey, I'm called" trigger
- auditing / The audit trigger
- DELETE trigger, disallowing / Disallowing DELETE
- cancel trigger / Disallowing DELETE
- BEFORE trigger / Disallowing DELETE
- AFTER trigger / Disallowing DELETE
- ERROR trigger / Disallowing DELETE
- TRUNCATE trigger, disallowing / Disallowing TRUNCATE
- NEW record, modifying / Modifying the NEW record
- timestamping / The timestamping trigger
- immutable fields trigger / The immutable fields trigger
- fire, controlling / Controlling when a trigger is called
- conditional triggers / Conditional triggers
- on specific field changes / Triggers on specific field changes
- function, visibility / Visibility
- rules, for using / Most importantly – use triggers cautiously!
- PL/pgSQL TRIGGER function / Variables passed to the PL/pgSQL TRIGGER function
- trigger functions, PL/Python
- writing / Writing trigger functions in PL/Python
- inputs, exploring / Exploring the inputs of a trigger
- log trigger / A log trigger
- triggers
- used, for managing related data / Managing related data with triggers
- triggers, PL/Perl
- writing / Writing PL/Perl triggers
- triggers, PL/Tcl
- writing / Writing PL/Tcl triggers
- OK value / Writing PL/Tcl triggers
- SKIP value / Writing PL/Tcl triggers
- LIST value / Writing PL/Tcl triggers
- OLD value / Writing PL/Tcl triggers
- nEW value / Writing PL/Tcl triggers
- $TG_name / Writing PL/Tcl triggers
- $TG_level / Writing PL/Tcl triggers
- $TG_when / Writing PL/Tcl triggers
- $TG_op / Writing PL/Tcl triggers
- $TG_table_name / Writing PL/Tcl triggers
- $OLD(i) / Writing PL/Tcl triggers
- $NEW(i) / Writing PL/Tcl triggers
- TRUNCATE trigger
- disallowing / Disallowing TRUNCATE
- type extensibility / Type extensibility
U
- untrusted languages
- untrusted Perl / Untrusted Perl
- untrusted Tcl / Untrusted Tcl
- user-defined aggregates
- creating / Creating user-defined aggregates
- example, URL / Creating user-defined aggregates
- URL / Creating user-defined aggregates
- User-defined functions (UDF)
- about / User-defined functions
V
- variable parameters
- URL / User-defined functions
- variables
- variadic argument lists / Variadic argument lists
- version 0 call conventions
- about / Version 0 call conventions
- URL / Version 0 call conventions
- views
- functions based / Functions based on views
- visibility
- URL / Visibility
- visibility rules
- VOLATILE function / Visibility
W
- wrappers
X
- XML data type
Y
- Yii / Third-party tools
- you ain't gonna need it (YAGNI) / YAGNI – you ain't gonna need it