Search PostgreSQL documentation

Pipeline ModeManual ChaptersPG1832.5. Pipeline Mode libpq pipeline mode allows applications to send a query without having to read the result of the previously sent query. Taking advantage of the pipeline mode, a client wi…Pipeline Mode PipeliningManual ChaptersPG1854.2.4. Pipelining Use of the extended query protocol allows pipelining, which means sending a series of queries without waiting for earlier ones to complete. This reduces the number of netw…Pipelining PL/Perl Event TriggersManual ChaptersPG1843.7. PL/Perl Event Triggers PL/Perl can be used to write event trigger functions. In an event trigger function, the hash reference $_TD contains information about the current trigger event.…PL/Perl Event Triggers PL/Perl Functions and ArgumentsManual ChaptersPG1843.1. PL/Perl Functions and Arguments To create a function in the PL/Perl language, use the standard CREATE FUNCTION syntax: CREATE FUNCTION funcname (argument-types) RETURNS return-type -- …PL/Perl Functions and Arguments PL/Perl TriggersManual ChaptersPG1843.6. PL/Perl Triggers PL/Perl can be used to write trigger functions. In a trigger function, the hash reference $_TD contains information about the current trigger event. $_TD is a global v…PL/Perl Triggers PL/Perl Under the HoodManual ChaptersPG1843.8. PL/Perl Under the HoodPL/Perl Under the Hood PL/Perl — Perl Procedural LanguageManual ChaptersPG18Chapter 43. PL/Perl — Perl Procedural Language PL/Perl is a loadable procedural language that enables you to write PostgreSQL functions and procedures in the Perl programming language. The m…PL/Perl — Perl Procedural Language PL/pgSQL under the HoodManual ChaptersPG1841.11. PL/pgSQL under the Hood This section discusses some implementation details that are frequently important for PL/pgSQL users to know.PL/pgSQL under the Hood PL/pgSQL — SQL Procedural LanguageManual ChaptersPG18Chapter 41. PL/pgSQL — SQL Procedural LanguagePL/pgSQL — SQL Procedural Language PL/Python FunctionsManual ChaptersPG1844.1. PL/Python Functions Functions in PL/Python are declared via the standard CREATE FUNCTION syntax: CREATE FUNCTION funcname (argument-list) RETURNS return-type AS $$ # PL/Python function…PL/Python Functions PL/Python — Python Procedural LanguageManual ChaptersPG18Chapter 44. PL/Python — Python Procedural Language The PL/Python procedural language allows PostgreSQL functions and procedures to be written in the Python language. To install PL/Python in …PL/Python — Python Procedural Language PL/Tcl ConfigurationManual ChaptersPG1842.11. PL/Tcl Configuration This section lists configuration parameters that affect PL/Tcl. pltcl.start_proc (string) # This parameter, if set to a nonempty string, specifies the name (possi…PL/Tcl Configuration PL/Tcl Functions and ArgumentsManual ChaptersPG1842.2. PL/Tcl Functions and Arguments To create a function in the PL/Tcl language, use the standard CREATE FUNCTION syntax: CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$…PL/Tcl Functions and Arguments PL/Tcl — Tcl Procedural LanguageManual ChaptersPG18Chapter 42. PL/Tcl — Tcl Procedural Language PL/Tcl is a loadable procedural language for the PostgreSQL database system that enables the Tcl language to be used to write PostgreSQL function…PL/Tcl — Tcl Procedural Language Plan CachingManual ChaptersPG1841.11.2. Plan Caching The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree the first time the function is called (within each session).…Plan Caching Planner Cost ConstantsManual ChaptersPG1819.7.2. Planner Cost Constants The cost variables described in this section are measured on an arbitrary scale. Only their relative values matter, hence scaling them all up or down by the sa…Planner Cost Constants Planner Method ConfigurationManual ChaptersPG1819.7.1. Planner Method Configuration These configuration parameters provide a crude method of influencing the query plans chosen by the query optimizer. If the default plan chosen by the opt…Planner Method Configuration Planner Statistics and SecurityManual ChaptersPG1869.3. Planner Statistics and Security Access to the table pg_statistic is restricted to superusers, so that ordinary users cannot learn about the contents of the tables of other users from i…Planner Statistics and Security Planner/OptimizerManual ChaptersPG1851.5. Planner/Optimizer The task of the planner/optimizer is to create an optimal execution plan. A given SQL query (and hence, a query tree) can be actually executed in a wide variety of di…Planner/Optimizer PlanningManual ChaptersPG1826.2.1. Planning It is usually wise to create the primary and standby servers so that they are as similar as possible, at least from the perspective of the database server. In particular, th…Planning Planning for High AvailabilityManual ChaptersPG1826.2.8.4. Planning for High Availability synchronous_standby_names specifies the number and names of synchronous standbys that transaction commits made when synchronous_commit is set to on, …Planning for High Availability Planning for PerformanceManual ChaptersPG1826.2.8.3. Planning for Performance Synchronous replication usually requires carefully planned and placed standby servers to ensure applications perform acceptably. Waiting doesn't utilize sy…Planning for Performance Platform and Client CompatibilityManual ChaptersPG1819.13.2. Platform and Client Compatibility transform_null_equals (boolean) # When on, expressions of the form expr = NULL (or NULL = expr) are treated as expr IS NULL, that is, they return t…Platform and Client Compatibility Platform-Specific NotesManual ChaptersPG1817.7. Platform-Specific Notes This section documents additional platform-specific issues regarding the installation and setup of PostgreSQL. Be sure to read the installation instructions, an…Platform-Specific Notes Pluggable JIT ProvidersManual ChaptersPG1830.4.2. Pluggable JIT Providers PostgreSQL provides a JIT implementation based on LLVM. The interface to the JIT provider is pluggable and the provider can be changed without recompiling (al…Pluggable JIT Providers Point-Based Earth DistancesManual ChaptersPG18F.14.2. Point-Based Earth Distances The second part of the module relies on representing Earth locations as values of type point, in which the first component is taken to represent longitude…Point-Based Earth Distances PointersManual ChaptersPG1834.4.4.3.4. Pointers You can declare pointers to the most common types. Note however that you cannot use pointers as target variables of queries without auto-allocation. See Section 34.7 for…Pointers PointsManual ChaptersPG188.8.1. Points Points are the fundamental two-dimensional building block for geometric types. Values of type point are specified using either of the following syntaxes: ( x , y ) x , y where …Points PolygonsManual ChaptersPG188.8.6. Polygons Polygons are represented by lists of points (the vertices of the polygon). Polygons are very similar to closed paths; the essential semantic difference is that a polygon is c…Polygons Polymorphic and Variadic AggregatesManual ChaptersPG1836.12.2. Polymorphic and Variadic Aggregates Aggregate functions can use polymorphic state transition functions or final functions, so that the same functions can be used to implement multip…Polymorphic and Variadic Aggregates Polymorphic Arguments and Return TypesManual ChaptersPG1836.10.10. Polymorphic Arguments and Return Types C-language functions can be declared to accept and return the polymorphic types described in Section 36.2.5. When a function's arguments or r…Polymorphic Arguments and Return Types Polymorphic SQL FunctionsManual ChaptersPG1836.5.11. Polymorphic SQL Functions SQL functions can be declared to accept and return the polymorphic types described in Section 36.2.5. Here is a polymorphic function make_array that builds…Polymorphic SQL Functions Polymorphic TypesManual ChaptersPG1836.2.5. Polymorphic Types Some pseudo-types of special interest are the polymorphic types, which are used to declare polymorphic functions. This powerful feature allows a single function def…Polymorphic Types Populating a DatabaseManual ChaptersPG1814.4. Populating a Database One might need to insert a large amount of data when first populating a database. This section contains some suggestions on how to make this process as efficient …Populating a Database Populating a Table With RowsManual ChaptersPG182.4. Populating a Table With Rows The INSERT statement is used to populate a table with rows: INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); Note that all data typ…Populating a Table With Rows PortabilityManual ChaptersPG185.10.7. Portability In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create sch…Portability Porting ExamplesManual ChaptersPG1841.13.1. Porting Examples Example 41.9 shows how to port a simple function from PL/SQL to PL/pgSQL. Example 41.9. Porting a Simple Function from PL/SQL to PL/pgSQL Here is an Oracle PL/SQL f…Porting Examples Porting from Oracle PL/SQLManual ChaptersPG1841.13. Porting from Oracle PL/SQL This section explains differences between PostgreSQL's PL/pgSQL language and Oracle's PL/SQL language, to help developers who port applications from Oracle®…Porting from Oracle PL/SQL Positional ParametersManual ChaptersPG184.2.2. Positional Parameters A positional parameter reference is used to indicate a value that is supplied externally to an SQL statement. Parameters are used in SQL function definitions and…Positional Parameters POSIX Regular ExpressionsManual ChaptersPG189.7.3. POSIX Regular Expressions Table 9.16 lists the available operators for pattern matching using POSIX regular expressions. Table 9.16. Regular Expression Match Operators Operator Descri…POSIX Regular Expressions