From 626b4416b9be9ac18faa0b441043243dd75653d3 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 2 Apr 2007 15:27:02 +0000 Subject: [PATCH] Put documentation on XML data type and functions in better positions. Add some index terms. --- doc/src/sgml/config.sgml | 4 +- doc/src/sgml/datatype.sgml | 274 +- doc/src/sgml/func.sgml | 7302 ++++++++++++++++++------------------ 3 files changed, 3795 insertions(+), 3785 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 07d1a879b3..57a618faa6 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -3591,7 +3591,7 @@ SELECT * FROM parent WHERE key = 2400; SET XML OPTION - XML option + XML option diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 1bf103c878..9e95f95f01 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,4 +1,4 @@ - + Data Types @@ -3190,6 +3190,144 @@ SELECT * FROM test; + + <acronym>XML</> Type + + + XML + + + + The data type xml can be used to store XML data. Its + advantage over storing XML data in a text field is that it + checks the input values for well-formedness, and there are support + functions to perform type-safe operations on it; see . + + + + In particular, the xml type can store well-formed + documents, as defined by the XML standard, as well + as content fragments, which are defined by the + production XMLDecl? content in the XML + standard. Roughly, this means that content fragments can have + more than one top-level element or character node. The expression + xmlvalue IS DOCUMENT + can be used to evaluate whether a particular xml + value is a full document or only a content fragment. + + + + To produce a value of type xml from character data, + use the function + xmlparse:xmlparse + +XMLPARSE ( { DOCUMENT | CONTENT } value) + + Examples: +Manual...') +XMLPARSE (CONTENT 'abcbarfoo') +]]> + While this is the only way to convert character strings into XML + values according to the SQL standard, the PostgreSQL-specific + syntaxes: +bar' +'bar'::xml +]]> + can also be used. + + + + The xml type does not validate its input values + against a possibly included document type declaration + (DTD).DTD + + + + The inverse operation, producing character string type values from + xml, uses the function + xmlserialize:xmlserialize + +XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type ) + + type can be one of + character, character varying, or + text (or an alias name for those). Again, according + to the SQL standard, this is the only way to convert between type + xml and character types, but PostgreSQL also allows + you to simply cast the value. + + + + When character string values are cast to or from type + xml without going through XMLPARSE or + XMLSERIALIZE, respectively, the choice of + DOCUMENT versus CONTENT is + determined by the XML option + XML option + session configuration parameter, which can be set using the + standard command + +SET XML OPTION { DOCUMENT | CONTENT }; + + or the more PostgreSQL-like syntax + +SET xmloption TO { DOCUMENT | CONTENT }; + + The default is CONTENT, so all forms of XML + data are allowed. + + + + Care must be taken when dealing with multiple character encodings + on the client, server, and in the XML data passed through them. + When using the text mode to pass queries to the server and query + results to the client (which is the normal mode), PostgreSQL + converts all character data passed between the client and the + server and vice versa to the character encoding of the respective + end; see . This includes string + representations of XML values, such as in the above examples. + This would ordinarily mean that encoding declarations contained in + XML data might become invalid as the character data is converted + to other encodings while travelling between client and server, + while the embedded encoding declaration is not changed. To cope + with this behavior, an encoding declaration contained in a + character string presented for input to the xml type + is ignored, and the content is always assumed + to be in the current server encoding. Consequently, for correct + processing, such character strings of XML data must be sent off + from the client in the current client encoding. It is the + responsibility of the client to either convert the document to the + current client encoding before sending it off to the server or to + adjust the client encoding appropriately. On output, values of + type xml will not have an encoding declaration, and + clients must assume that the data is in the current client + encoding. + + + + When using the binary mode to pass query parameters to the server + and query results back the the client, no character set conversion + is performed, so the situation is different. In this case, an + encoding declaration in the XML data will be observed, and if it + is absent, the data will be assumed to be in UTF-8 (as required by + the XML standard; note that PostgreSQL does not support UTF-16 at + all). On output, data will have an encoding declaration + specifying the client encoding, unless the client encoding is + UTF-8, in which case it will be omitted. + + + + Needless to say, processing XML data with PostgreSQL will be less + error-prone and more efficient if data encoding, client encoding, + and server encoding are the same. Since XML data is internally + processed in UTF-8, computations will be most efficient if the + server encoding is also UTF-8. + + + &array; &rowtypes; @@ -3579,138 +3717,4 @@ SELECT * FROM pg_attribute - - <acronym>XML</> Type - - - XML - - - - The data type xml can be used to store XML data. Its - advantage over storing XML data in a text field is that it - checks the input values for well-formedness, and there are support - functions to perform type-safe operations on it; see . - - - - In particular, the xml type can store well-formed - documents, as defined by the XML standard, as well - as content fragments, which are defined by the - production XMLDecl? content in the XML - standard. Roughly, this means that content fragments can have - more than one top-level element or character node. The expression - xmlvalue IS DOCUMENT - can be used to evaluate whether a particular xml - value is a full document or only a content fragment. - - - - To produce a value of type xml from character data, - use the function xmlparse: - -XMLPARSE ( { DOCUMENT | CONTENT } value) - - Examples: -Manual...') -XMLPARSE (CONTENT 'abcbarfoo') -]]> - While this is the only way to convert character strings into XML - values according to the SQL standard, the PostgreSQL-specific - syntaxes: -bar' -'bar'::xml -]]> - can also be used. - - - - The xml type does not validate its input values - against a possibly included document type declaration (DTD). - - - - The inverse operation, producing character string type values from - xml, uses the function - xmlserialize: - -XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type ) - - type can be one of - character, character varying, or - text (or an alias name for those). Again, according - to the SQL standard, this is the only way to convert between type - xml and character types, but PostgreSQL also allows - you to simply cast the value. - - - - When character string values are cast to or from type - xml without going through XMLPARSE or - XMLSERIALIZE, respectively, the choice of - DOCUMENT versus CONTENT is - determined by the XML option session configuration - parameter, which can be set using the standard command - -SET XML OPTION { DOCUMENT | CONTENT }; - - or the more PostgreSQL-like syntax - -SET xmloption TO { DOCUMENT | CONTENT }; - - The default is CONTENT, so all forms of XML - data are allowed. - - - - Care must be taken when dealing with multiple character encodings - on the client, server, and in the XML data passed through them. - When using the text mode to pass queries to the server and query - results to the client (which is the normal mode), PostgreSQL - converts all character data passed between the client and the - server and vice versa to the character encoding of the respective - end; see . This includes string - representations of XML values, such as in the above examples. - This would ordinarily mean that encoding declarations contained in - XML data might become invalid as the character data is converted - to other encodings while travelling between client and server, - while the embedded encoding declaration is not changed. To cope - with this behavior, an encoding declaration contained in a - character string presented for input to the xml type - is ignored, and the content is always assumed - to be in the current server encoding. Consequently, for correct - processing, such character strings of XML data must be sent off - from the client in the current client encoding. It is the - responsibility of the client to either convert the document to the - current client encoding before sending it off to the server or to - adjust the client encoding appropriately. On output, values of - type xml will not have an encoding declaration, and - clients must assume that the data is in the current client - encoding. - - - - When using the binary mode to pass query parameters to the server - and query results back the the client, no character set conversion - is performed, so the situation is different. In this case, an - encoding declaration in the XML data will be observed, and if it - is absent, the data will be assumed to be in UTF-8 (as required by - the XML standard; note that PostgreSQL does not support UTF-16 at - all). On output, data will have an encoding declaration - specifying the client encoding, unless the client encoding is - UTF-8, in which case it will be omitted. - - - - Needless to say, processing XML data with PostgreSQL will be less - error-prone and more efficient if data encoding, client encoding, - and server encoding are the same. Since XML data is internally - processed in UTF-8, computations will be most efficient if the - server encoding is also UTF-8. - - - diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 0baf152594..cc872d8234 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -7502,4302 +7502,4308 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple - - Sequence Manipulation Functions - - - sequence - - - nextval - - - currval - - - lastval - - - setval - + + XML Functions - This section describes PostgreSQL's functions - for operating on sequence objects. - Sequence objects (also called sequence generators or - just sequences) are special single-row tables created with - CREATE SEQUENCE. A sequence object is usually used to - generate unique identifiers for rows of a table. The sequence functions, - listed in , - provide simple, multiuser-safe methods for obtaining successive - sequence values from sequence objects. + The functions and function-like expressions described in this + section operate on values of type xml. Check for information about the xml + type. The function-like expressions xmlparse + and xmlserialize for converting to and from + type xml are not repeated here. - - Sequence Functions - - - Function Return Type Description - - - - - currval(regclass) - bigint - Return value most recently obtained with - nextval for specified sequence - - - nextval(regclass) - bigint - Advance sequence and return new value - - - setval(regclass, bigint) - bigint - Set sequence's current value - - - setval(regclass, bigint, boolean) - bigint - Set sequence's current value and is_called flag - - - -
- - - The sequence to be operated on by a sequence-function call is specified by - a regclass argument, which is just the OID of the sequence in the - pg_class system catalog. You do not have to look up the - OID by hand, however, since the regclass data type's input - converter will do the work for you. Just write the sequence name enclosed - in single quotes, so that it looks like a literal constant. To - achieve some compatibility with the handling of ordinary - SQL names, the string will be converted to lowercase - unless it contains double quotes around the sequence name. Thus: - -nextval('foo') operates on sequence foo -nextval('FOO') operates on sequence foo -nextval('"Foo"') operates on sequence Foo - - The sequence name can be schema-qualified if necessary: - -nextval('myschema.foo') operates on myschema.foo -nextval('"myschema".foo') same as above -nextval('foo') searches search path for foo - - See for more information about - regclass. - + + Producing XML Content - - Before PostgreSQL 8.1, the arguments of the - sequence functions were of type text, not regclass, and - the above-described conversion from a text string to an OID value would - happen at run time during each call. For backwards compatibility, this - facility still exists, but internally it is now handled as an implicit - coercion from text to regclass before the function is - invoked. + A set of functions and function-like expressions are available for + producing XML content from SQL data. As such, they are + particularly suitable for formatting query results into XML + documents for processing in client applications. - - When you write the argument of a sequence function as an unadorned - literal string, it becomes a constant of type regclass. - Since this is really just an OID, it will track the originally - identified sequence despite later renaming, schema reassignment, - etc. This early binding behavior is usually desirable for - sequence references in column defaults and views. But sometimes you will - want late binding where the sequence reference is resolved - at run time. To get late-binding behavior, force the constant to be - stored as a text constant instead of regclass: - -nextval('foo'::text) foo is looked up at runtime - - Note that late binding was the only behavior supported in - PostgreSQL releases before 8.1, so you - might need to do this to preserve the semantics of old applications. - + + <literal>xmlcomment</literal> - - Of course, the argument of a sequence function can be an expression - as well as a constant. If it is a text expression then the implicit - coercion will result in a run-time lookup. - - + + xmlcomment + - - The available sequence functions are: + +xmlcomment(text) + - - - nextval - - - Advance the sequence object to its next value and return that - value. This is done atomically: even if multiple sessions - execute nextval concurrently, each will safely receive - a distinct sequence value. - - - + + The function xmlcomment creates an XML value + containing an XML comment with the specified text as content. + The text cannot contain -- or end with a + - so that the resulting construct is a valid + XML comment. If the argument is null, the result is null. + - - currval - - - Return the value most recently obtained by nextval - for this sequence in the current session. (An error is - reported if nextval has never been called for this - sequence in this session.) Notice that because this is returning - a session-local value, it gives a predictable answer whether or not - other sessions have executed nextval since the - current session did. - - - + + Example: + - lastval - - - Return the value most recently returned by - nextval in the current session. This function is - identical to currval, except that instead - of taking the sequence name as an argument it fetches the - value of the last sequence that nextval - was used on in the current session. It is an error to call - lastval if nextval - has not yet been called in the current session. - - - + xmlcomment +-------------- + +]]> + + - - setval - - - Reset the sequence object's counter value. The two-parameter - form sets the sequence's last_value field to the specified - value and sets its is_called field to true, - meaning that the next nextval will advance the sequence - before returning a value. In the three-parameter form, - is_called can be set either true or - false. If it's set to false, - the next nextval will return exactly the specified - value, and sequence advancement commences with the following - nextval. For example, + + <literal>xmlconcat</literal> - -SELECT setval('foo', 42); Next nextval will return 43 -SELECT setval('foo', 42, true); Same as above -SELECT setval('foo', 42, false); Next nextval will return 42 - + + xmlconcat + - The result returned by setval is just the value of its - second argument. - - - - - + + xmlconcat(xml, ...) + + + + The function xmlconcat concatenates a list + of individual XML values to create a single value containing an + XML content fragment. Null values are omitted; the result is + only null if there are no nonnull arguments. + - - If a sequence object has been created with default parameters, - nextval calls on it will return successive values - beginning with 1. Other behaviors can be obtained by using - special parameters in the command; - see its command reference page for more information. - + + Example: +', 'foo'); - - - To avoid blocking of concurrent transactions that obtain numbers from the - same sequence, a nextval operation is never rolled back; - that is, once a value has been fetched it is considered used, even if the - transaction that did the nextval later aborts. This means - that aborted transactions might leave unused holes in the - sequence of assigned values. setval operations are never - rolled back, either. - - + xmlconcat +---------------------- + foo +]]> + - + + XML declarations, if present are combined as follows. If all + argument values have the same XML version declaration, that + version is used in the result, else no version is used. If all + argument values have the standalone declaration value + yes, then that value is used in the result. If + all argument values have a standalone declaration value and at + least one is no, then that is used in the result. + Else the result will have no standalone declaration. If the + result is determined to require a standalone declaration but no + version declaration, a version declaration with version 1.0 will + be used because XML requires an XML declaration to contain a + version declaration. Encoding declarations are ignored and + removed in all cases. + + + Example: +', ''); - - Conditional Expressions + xmlconcat +----------------------------------- + +]]> + + + + + <literal>xmlelement</literal> + + + xmlelement + + + + xmlelement(name name , xmlattributes(value AS attname , ... ) , content, ...) + + + + The xmlelement expression produces an XML + element with the given name, attributes, and content. + - - CASE - + + Examples: + - conditional expression - + xmlelement +------------ + - - This section describes the SQL-compliant conditional expressions - available in PostgreSQL. - +SELECT xmlelement(name foo, xmlattributes('xyz' as bar)); - - - If your needs go beyond the capabilities of these conditional - expressions you might want to consider writing a stored procedure - in a more expressive programming language. - - + xmlelement +------------------ + - - <literal>CASE</> +SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent'); - - The SQL CASE expression is a - generic conditional expression, similar to if/else statements in - other languages: + xmlelement +------------------------------------- + content +]]> + - -CASE WHEN condition THEN result - WHEN ... - ELSE result -END - + + Element and attribute names that are not valid XML names are + escaped by replacing the offending characters by the sequence + _xHHHH_, where + HHHH is the character's Unicode + codepoint in hexadecimal notation. For example: +CASE clauses can be used wherever - an expression is valid. condition is an - expression that returns a boolean result. If the result is true - then the value of the CASE expression is the - result that follows the condition. If the result is false any - subsequent WHEN clauses are searched in the same - manner. If no WHEN - condition is true then the value of the - case expression is the result in the - ELSE clause. If the ELSE clause is - omitted and no condition matches, the result is null. - + xmlelement +---------------------------------- + +]]> + - - An example: + + An explicit attribute name need not be specified if the attribute + value is a column reference, in which case the column's name will + be used as attribute name by default. In any other case, the + attribute must be given an explicit name. So this example is + valid: -SELECT * FROM test; +CREATE TABLE test (a xml, b xml); +SELECT xmlelement(name test, xmlattributes(a, b)) FROM test; + + But these are not: + +SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test; +SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test; + + - a ---- - 1 - 2 - 3 + + Element content, if specified, will be formatted according to + data type. If the content is itself of type xml, + complex XML documents can be constructed. For example: + +]]> -SELECT a, - CASE WHEN a=1 THEN 'one' - WHEN a=2 THEN 'two' - ELSE 'other' - END - FROM test; + Content of other types will be formatted into valid XML character + data. This means in particular that the characters <, >, + and & will be converted to entities. Binary data (data type + bytea) will be represented in base64 or hex + encoding, depending on the setting of the configuration parameter + . The particular behavior for + individual data types is expected evolve in order to align the + SQL and PostgreSQL data types with the XML Schema specification, + at which point a more precise description will appear. + + + + + <literal>xmlforest</literal> + + + xmlforest + + + + xmlforest(content AS name , ...) + + + + The xmlforest expression produces an XML + forest (sequence) of elements using the given names and content. + - a | case ----+------- - 1 | one - 2 | two - 3 | other - - + + Examples: + - The data types of all the result - expressions must be convertible to a single output type. - See for more detail. - + xmlforest +------------------------------ + abc123 - - The following simple CASE expression is a - specialized variant of the general form above: - -CASE expression - WHEN value THEN result - WHEN ... - ELSE result -END - +SELECT xmlforest(table_name, column_name) FROM information_schema.columns WHERE table_schema = 'pg_catalog'; - The - expression is computed and compared to - all the value specifications in the - WHEN clauses until one is found that is equal. If - no match is found, the result in the - ELSE clause (or a null value) is returned. This is similar - to the switch statement in C. - + xmlforest +------------------------------------------------------------------------------------------- + pg_authidrolname + pg_authidrolsuper + ... +]]> - - The example above can be written using the simple - CASE syntax: - -SELECT a, - CASE a WHEN 1 THEN 'one' - WHEN 2 THEN 'two' - ELSE 'other' - END - FROM test; + As seen in the second example, the element name can be omitted if + the content value is a column reference, in which case the column + name is used by default. Otherwise, a name must be specified. + - a | case ----+------- - 1 | one - 2 | two - 3 | other - - - - - A CASE expression does not evaluate any subexpressions - that are not needed to determine the result. For example, this is a - possible way of avoiding a division-by-zero failure: - -SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END; - - - - - - <literal>COALESCE</> - - - COALESCE - + + Element names that are not valid XML names are escaped as shown + for xmlelement above. Similarly, content + data is escaped to make valid XML content, unless it is already + of type xml. + - - NVL - + + Note that XML forests are not valid XML documents if they consist + of more than one element. So it might be useful to wrap + xmlforest expressions in + xmlelement. + + + + + <literal>xmlpi</literal> + + + xmlpi + + + + xmlpi(name target , content) + + + + The xmlpi expression creates an XML + processing instruction. The content, if present, must not + contain the character sequence ?>. + - - IFNULL - + + Example: + -COALESCE(value , ...) - + xmlpi +----------------------------- + +]]> + + + + + <literal>xmlroot</literal> + + + xmlroot + + + + xmlroot(xml, version text|no value , standalone yes|no|no value) + + + + The xmlroot expression alters the properties + of the root node of an XML value. If a version is specified, + this replaces the value in the version declaration, if a + standalone value is specified, this replaces the value in the + standalone declaration. + - - The COALESCE function returns the first of its - arguments that is not null. Null is returned only if all arguments - are null. It is often used to substitute a default value for - null values when data is retrieved for display, for example: - -SELECT COALESCE(description, short_description, '(none)') ... - - + +abc'), version '1.0', standalone yes); - - Like a CASE expression, COALESCE will - not evaluate arguments that are not needed to determine the result; - that is, arguments to the right of the first non-null argument are - not evaluated. This SQL-standard function provides capabilities similar - to NVL and IFNULL, which are used in some other - database systems. - - + xmlroot +---------------------------------------- + + abc +]]> + + - - <literal>NULLIF</> + + XML Predicates - - NULLIF - + + IS DOCUMENT + -NULLIF(value1, value2) +xml IS DOCUMENT - - The NULLIF function returns a null value if - value1 and value2 - are equal; otherwise it returns value1. - This can be used to perform the inverse operation of the - COALESCE example given above: - -SELECT NULLIF(value, '(none)') ... - - - - If value1 is (none), return a null, - otherwise return value1. - - + + The expression IS DOCUMENT returns true if the + argument XML value is a proper XML document, false if it is not + (that is, it is a content fragment), or null if the argument is + null. See about the difference + between documents and content fragments. + + - - <literal>GREATEST</literal> and <literal>LEAST</literal> + + Mapping Tables to XML - - GREATEST - - - LEAST - + + XML export + + + The following functions map the contents of relational tables to + XML values. They can be thought of as XML export functionality. -GREATEST(value , ...) - - -LEAST(value , ...) +table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text) +query_to_xml(query text, nulls boolean, tableforest boolean, targetns text) +cursor_to_xml(cursor refcursor, count int, nulls boolean, tableforest boolean, targetns text) + The return type of each function is xml. + - The GREATEST and LEAST functions select the - largest or smallest value from a list of any number of expressions. - The expressions must all be convertible to a common data type, which - will be the type of the result - (see for details). NULL values - in the list are ignored. The result will be NULL only if all the - expressions evaluate to NULL. + table_to_xml maps the content of the named + table, passed as parameter tbl. The + regclass accepts strings identifying tables using the + usual notation, including optional schema qualifications and + double quotes. query_to_xml executes the + query whose text is passed as parameter + query and maps the result set. + cursor_to_xml fetches the indicated number of + rows from the cursor specified by the parameter + cursor. This variant is recommendable if + large tables have to be mapped, because the result value is built + up in memory by each function. - Note that GREATEST and LEAST are not in - the SQL standard, but are a common extension. - - - + If tableforest is false, then the resulting + XML document looks like this: + + + data + data + + + ... + - - Array Functions and Operators + ... + +]]> - - shows the operators - available for array types. - + If tableforest is true, the result is an + XML content fragment that looks like this: + + data + data + - - <type>array</type> Operators - - - - Operator - Description - Example - Result - - - - - = - equal - ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] - t - + + ... + - - <> - not equal - ARRAY[1,2,3] <> ARRAY[1,2,4] - t - +... +]]> - - < - less than - ARRAY[1,2,3] < ARRAY[1,2,4] - t - + If no table name is avaible, that is, when mapping a query or a + cursor, the string table is used in the first + format, row in the second format. + - - > - greater than - ARRAY[1,4,3] > ARRAY[1,2,4] - t - + + The choice between these formats is up to the user. The first + format is a proper XML document, which will be important in many + applications. The second format tends to be more useful in the + cursor_to_xml function if the result values are to be + reassembled into one document later on. The functions for + producing XML content discussed above, in particular + xmlelement, can be used to alter the results + to taste. + - - <= - less than or equal - ARRAY[1,2,3] <= ARRAY[1,2,3] - t - + + The data values are mapping in the same way as described for the + function xmlelement above. + - - >= - greater than or equal - ARRAY[1,4,3] >= ARRAY[1,4,3] - t - + + The parameter nulls determines whether null + values should be included in the output. If true, null values in + columns are represented as + +]]> + where xsi is the XML namespace prefix for XML + Schema Instance. An appropriate namespace declaration will be + added to the result value. If false, columns containing null + values are simply omitted from the output. + - - @> - contains - ARRAY[1,4,3] @> ARRAY[3,1] - t - + + The parameter targetns specifies the + desired XML namespace of the result. If no particular namespace + is wanted, an empty string should be passed. + - - <@ - is contained by - ARRAY[2,7] <@ ARRAY[1,7,4,2,6] - t - + + The following functions return XML Schema documents describing the + mappings made by the data mappings produced by the corresponding + functions above. + +table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text) +query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text) +cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text) + + It is essential that the same parameters are passed in order to + obtain matching XML data mappings and XML Schema documents. + - - && - overlap (have elements in common) - ARRAY[1,4,3] && ARRAY[2,1] - t - + + The following functions produce XML data mappings and the + corresponding XML Schema in one document (or forest), linked + together. They can be useful where self-contained and + self-describing results are wanted. + +table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text) +query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text) + + - - || - array-to-array concatenation - ARRAY[1,2,3] || ARRAY[4,5,6] - {1,2,3,4,5,6} - + + In addition, the following functions are available to produce + analogous mappings of entire schemas or the entire current + database. + +schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text) +schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text) +schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text) - - || - array-to-array concatenation - ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] - {{1,2,3},{4,5,6},{7,8,9}} - +database_to_xml(nulls boolean, tableforest boolean, targetns text) +database_to_xmlschema(nulls boolean, tableforest boolean, targetns text) +database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text) + - - || - element-to-array concatenation - 3 || ARRAY[4,5,6] - {3,4,5,6} - + Note that these potentially produce a lot of data, which needs to + be built up in memory. When requesting content mappings of large + schemas or databases, it may be worthwhile to consider mapping the + tables separately instead, possibly even through a cursor. + - - || - array-to-element concatenation - ARRAY[4,5,6] || 7 - {4,5,6,7} - - - -
+ + The result of a schema content mapping looks like this: - - Array comparisons compare the array contents element-by-element, - using the default B-Tree comparison function for the element data type. - In multidimensional arrays the elements are visited in row-major order - (last subscript varies most rapidly). - If the contents of two arrays are equal but the dimensionality is - different, the first difference in the dimensionality information - determines the sort order. (This is a change from versions of - PostgreSQL prior to 8.2: older versions would claim - that two arrays with the same contents were equal, even if the - number of dimensions or subscript ranges were different.) - + - - See for more details about array operator - behavior. - +table1-mapping - - shows the functions - available for use with array types. See - for more discussion and examples of the use of these functions. - +table2-mapping - - <type>array</type> Functions - - - - Function - Return Type - Description - Example - Result - - - - - - - array_append(anyarray, anyelement) - - - anyarray - append an element to the end of an array - array_append(ARRAY[1,2], 3) - {1,2,3} - - - - - array_cat(anyarray, anyarray) - - - anyarray - concatenate two arrays - array_cat(ARRAY[1,2,3], ARRAY[4,5]) - {1,2,3,4,5} - - - - - array_dims(anyarray) - - - text - returns a text representation of array's dimensions - array_dims(ARRAY[[1,2,3], [4,5,6]]) - [1:2][1:3] - - - - - array_lower(anyarray, int) - - - int - returns lower bound of the requested array dimension - array_lower('[0:2]={1,2,3}'::int[], 1) - 0 - - - - - array_prepend(anyelement, anyarray) - - - anyarray - append an element to the beginning of an array - array_prepend(1, ARRAY[2,3]) - {1,2,3} - - - - - array_to_string(anyarray, text) - - - text - concatenates array elements using provided delimiter - array_to_string(ARRAY[1, 2, 3], '~^~') - 1~^~2~^~3 - - - - - array_upper(anyarray, int) - - - int - returns upper bound of the requested array dimension - array_upper(ARRAY[1,2,3,4], 1) - 4 - - - - - string_to_array(text, text) - - - text[] - splits string into array elements using provided delimiter - string_to_array('xx~^~yy~^~zz', '~^~') - {xx,yy,zz} - - - -
- +... - - Aggregate Functions +]]> - - aggregate function - built-in - + where the format of a table mapping depends on the + tableforest parameter as explained above. + - - Aggregate functions compute a single result - value from a set of input values. The built-in aggregate functions - are listed in - and - . - The special syntax considerations for aggregate - functions are explained in . - Consult for additional introductory - information. - + + The result of a database content mapping looks like this: - - General-Purpose Aggregate Functions + - - - - Function - Argument Type - Return Type - Description - - + + ... + - - - - - average - - avg(expression) - - - smallint, int, - bigint, real, double - precision, numeric, or interval - - - numeric for any integer type argument, - double precision for a floating-point argument, - otherwise the same as the argument data type - - the average (arithmetic mean) of all input values - + + ... + - - - - bit_and - - bit_and(expression) - - - smallint, int, bigint, or - bit - - - same as argument data type - - the bitwise AND of all non-null input values, or null if none - +... - - - - bit_or - - bit_or(expression) - - - smallint, int, bigint, or - bit - - - same as argument data type - - the bitwise OR of all non-null input values, or null if none - +]]> - - - - bool_and - - bool_and(expression) - - - bool - - - bool - - true if all input values are true, otherwise false - + where the schema mapping is as above. + - - - - bool_or - - bool_or(expression) - - - bool - - - bool - - true if at least one input value is true, otherwise false - + + As an example for using the output produced by these functions, + shows an XSLT stylesheet that + converts the output of + table_to_xml_and_xmlschema to an HTML + document containing a tabular rendition of the table data. In a + similar manner, the result data of these functions can be + converted into other XML-based formats. + - - count(*) - - bigint - number of input rows - +
+ XSLT stylesheet for converting SQL/XML output to HTML + + - - count(expression) - any - bigint - - number of input rows for which the value of expression is not null - - + - - - - every - - every(expression) - - - bool - - - bool - - equivalent to bool_and - + + + + - - max(expression) - any array, numeric, string, or date/time type - same as argument type - - maximum value of expression across all input - values - - + + + <xsl:value-of select="name(current())"/> + + +
+ + + + + - - min(expression) - any array, numeric, string, or date/time type - same as argument type - - minimum value of expression across all input - values - - + + + + + + + +
+ + + - - sum(expression) - - smallint, int, - bigint, real, double - precision, numeric, or - interval - - - bigint for smallint or - int arguments, numeric for - bigint arguments, double precision - for floating-point arguments, otherwise the same as the - argument data type - - sum of expression across all input values - - - - - - - It should be noted that except for count, - these functions return a null value when no rows are selected. In - particular, sum of no rows returns null, not - zero as one might expect. The coalesce function can be - used to substitute zero for null when necessary. - + +]]> + + - - - ANY - - - SOME - - - Boolean aggregates bool_and and - bool_or correspond to standard SQL aggregates - every and any or - some. - As for any and some, - it seems that there is an ambiguity built into the standard syntax: - -SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...; - - Here ANY can be considered both as leading - to a subquery or as an aggregate if the select expression returns 1 row. - Thus the standard name cannot be given to these aggregates. - - + + Processing XML - - Users accustomed to working with other SQL database management - systems might be surprised by the performance of the - count aggregate when it is applied to the - entire table. A query like: - -SELECT count(*) FROM sometable; - - will be executed by PostgreSQL using a - sequential scan of the entire table. + XML support is not just the existence of an + xml data type, but a variety of features supported by + a database system. These capabilities include import/export, + indexing, searching, transforming, and XML to + SQL mapping. PostgreSQL supports some + but not all of these XML capabilities. For an + overview of XML use in databases, see . - + + + Indexing + - - shows - aggregate functions typically used in statistical analysis. - (These are separated out merely to avoid cluttering the listing - of more-commonly-used aggregates.) Where the description mentions - N, it means the - number of input rows for which all the input expressions are non-null. - In all cases, null is returned if the computation is meaningless, - for example when N is zero. - + + contrib/xml2/ functions can be used in expression + indexes to index specific XML fields. To index the + full contents of XML documents, the full-text + indexing tool contrib/tsearch2/ can be used. Of + course, Tsearch2 indexes have no XML awareness so + additional contrib/xml2/ checks should be added to + queries. + + + - - statistics - - - linear regression - + + Searching + - - Aggregate Functions for Statistics + + XPath searches are implemented using contrib/xml2/. + It processes XML text documents and returns results + based on the requested query. + + + - - - - Function - Argument Type - Return Type - Description - - + + Transforming + - + + contrib/xml2/ supports XSLT (Extensible + Stylesheet Language Transformation). + + + - - - - correlation - - corr(Y, X) - - - double precision - - - double precision - - correlation coefficient - + + XML to SQL Mapping + - - - - covariance - population - - covar_pop(Y, X) - - - double precision - - - double precision - - population covariance - + + This involves converting XML data to and from + relational structures. PostgreSQL has no + internal support for such mapping, and relies on external tools + to do such conversions. + + + + + + - - - - covariance - sample - - covar_samp(Y, X) - - - double precision - - - double precision - - sample covariance - - - - regr_avgx(Y, X) - - - double precision - - - double precision - - average of the independent variable - (sum(X)/N) - + + Sequence Manipulation Functions - - - regr_avgy(Y, X) - - - double precision - - - double precision - - average of the dependent variable - (sum(Y)/N) - + + sequence + + + nextval + + + currval + + + lastval + + + setval + - - - regr_count(Y, X) - - - double precision - - - bigint - - number of input rows in which both expressions are nonnull - + + This section describes PostgreSQL's functions + for operating on sequence objects. + Sequence objects (also called sequence generators or + just sequences) are special single-row tables created with + CREATE SEQUENCE. A sequence object is usually used to + generate unique identifiers for rows of a table. The sequence functions, + listed in , + provide simple, multiuser-safe methods for obtaining successive + sequence values from sequence objects. + - - - - regression intercept - - regr_intercept(Y, X) - - - double precision - - - double precision - - y-intercept of the least-squares-fit linear equation - determined by the (X, Y) pairs - - - - - regr_r2(Y, X) - - - double precision - - - double precision - - square of the correlation coefficient - - - - - - regression slope - - regr_slope(Y, X) - - - double precision - - - double precision - - slope of the least-squares-fit linear equation determined - by the (X, - Y) pairs - - - - - regr_sxx(Y, X) - - - double precision - - - double precision - - sum(X^2) - sum(X)^2/N (sum of - squares of the independent variable) - - - - - regr_sxy(Y, X) - - - double precision - - - double precision - - sum(X*Y) - sum(X) * sum(Y)/N (sum of - products of independent times dependent - variable) - - - - - regr_syy(Y, X) - - - double precision - - - double precision - - sum(Y^2) - sum(Y)^2/N (sum of - squares of the dependent variable) - - - - - - standard deviation - - stddev(expression) - - - smallint, int, - bigint, real, double - precision, or numeric - - - double precision for floating-point arguments, - otherwise numeric - - historical alias for stddev_samp - - - - - - standard deviation - population - - stddev_pop(expression) - - - smallint, int, - bigint, real, double - precision, or numeric - - - double precision for floating-point arguments, - otherwise numeric - - population standard deviation of the input values - - - - - - standard deviation - sample - - stddev_samp(expression) - - - smallint, int, - bigint, real, double - precision, or numeric - - - double precision for floating-point arguments, - otherwise numeric - - sample standard deviation of the input values - - - - - - variance - - variance(expression) - - - smallint, int, - bigint, real, double - precision, or numeric - - - double precision for floating-point arguments, - otherwise numeric - - historical alias for var_samp - - - - - - variance - population - - var_pop(expression) - - - smallint, int, - bigint, real, double - precision, or numeric - - - double precision for floating-point arguments, - otherwise numeric - - population variance of the input values (square of the population standard deviation) - - - - - - variance - sample - - var_samp(expression) - - - smallint, int, - bigint, real, double - precision, or numeric - - - double precision for floating-point arguments, - otherwise numeric - - sample variance of the input values (square of the sample standard deviation) - - - -
- - - - - - Subquery Expressions - - - EXISTS - - - - IN - - - - NOT IN - - - - ANY - - - - ALL - - - - SOME - + + Sequence Functions + + + Function Return Type Description + - - subquery - + + + currval(regclass) + bigint + Return value most recently obtained with + nextval for specified sequence + + + nextval(regclass) + bigint + Advance sequence and return new value + + + setval(regclass, bigint) + bigint + Set sequence's current value + + + setval(regclass, bigint, boolean) + bigint + Set sequence's current value and is_called flag + + + +
- This section describes the SQL-compliant subquery - expressions available in PostgreSQL. - All of the expression forms documented in this section return - Boolean (true/false) results. + The sequence to be operated on by a sequence-function call is specified by + a regclass argument, which is just the OID of the sequence in the + pg_class system catalog. You do not have to look up the + OID by hand, however, since the regclass data type's input + converter will do the work for you. Just write the sequence name enclosed + in single quotes, so that it looks like a literal constant. To + achieve some compatibility with the handling of ordinary + SQL names, the string will be converted to lowercase + unless it contains double quotes around the sequence name. Thus: + +nextval('foo') operates on sequence foo +nextval('FOO') operates on sequence foo +nextval('"Foo"') operates on sequence Foo + + The sequence name can be schema-qualified if necessary: + +nextval('myschema.foo') operates on myschema.foo +nextval('"myschema".foo') same as above +nextval('foo') searches search path for foo + + See for more information about + regclass. - - <literal>EXISTS</literal> + + + Before PostgreSQL 8.1, the arguments of the + sequence functions were of type text, not regclass, and + the above-described conversion from a text string to an OID value would + happen at run time during each call. For backwards compatibility, this + facility still exists, but internally it is now handled as an implicit + coercion from text to regclass before the function is + invoked. + - -EXISTS (subquery) - + + When you write the argument of a sequence function as an unadorned + literal string, it becomes a constant of type regclass. + Since this is really just an OID, it will track the originally + identified sequence despite later renaming, schema reassignment, + etc. This early binding behavior is usually desirable for + sequence references in column defaults and views. But sometimes you will + want late binding where the sequence reference is resolved + at run time. To get late-binding behavior, force the constant to be + stored as a text constant instead of regclass: + +nextval('foo'::text) foo is looked up at runtime + + Note that late binding was the only behavior supported in + PostgreSQL releases before 8.1, so you + might need to do this to preserve the semantics of old applications. + - - The argument of EXISTS is an arbitrary SELECT statement, - or subquery. The - subquery is evaluated to determine whether it returns any rows. - If it returns at least one row, the result of EXISTS is - true; if the subquery returns no rows, the result of EXISTS - is false. - + + Of course, the argument of a sequence function can be an expression + as well as a constant. If it is a text expression then the implicit + coercion will result in a run-time lookup. + + - The subquery can refer to variables from the surrounding query, - which will act as constants during any one evaluation of the subquery. - + The available sequence functions are: + + + + nextval + + + Advance the sequence object to its next value and return that + value. This is done atomically: even if multiple sessions + execute nextval concurrently, each will safely receive + a distinct sequence value. + + + + + + currval + + + Return the value most recently obtained by nextval + for this sequence in the current session. (An error is + reported if nextval has never been called for this + sequence in this session.) Notice that because this is returning + a session-local value, it gives a predictable answer whether or not + other sessions have executed nextval since the + current session did. + + + - - The subquery will generally only be executed far enough to determine - whether at least one row is returned, not all the way to completion. - It is unwise to write a subquery that has any side effects (such as - calling sequence functions); whether the side effects occur or not - might be difficult to predict. - + + lastval + + + Return the value most recently returned by + nextval in the current session. This function is + identical to currval, except that instead + of taking the sequence name as an argument it fetches the + value of the last sequence that nextval + was used on in the current session. It is an error to call + lastval if nextval + has not yet been called in the current session. + + + - - Since the result depends only on whether any rows are returned, - and not on the contents of those rows, the output list of the - subquery is normally uninteresting. A common coding convention is - to write all EXISTS tests in the form - EXISTS(SELECT 1 WHERE ...). There are exceptions to - this rule however, such as subqueries that use INTERSECT. - + + setval + + + Reset the sequence object's counter value. The two-parameter + form sets the sequence's last_value field to the specified + value and sets its is_called field to true, + meaning that the next nextval will advance the sequence + before returning a value. In the three-parameter form, + is_called can be set either true or + false. If it's set to false, + the next nextval will return exactly the specified + value, and sequence advancement commences with the following + nextval. For example, - - This simple example is like an inner join on col2, but - it produces at most one output row for each tab1 row, - even if there are multiple matching tab2 rows: -SELECT col1 FROM tab1 - WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2); +SELECT setval('foo', 42); Next nextval will return 43 +SELECT setval('foo', 42, true); Same as above +SELECT setval('foo', 42, false); Next nextval will return 42 - - - - - <literal>IN</literal> - - -expression IN (subquery) - - - The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression - is evaluated and compared to each row of the subquery result. - The result of IN is true if any equal subquery row is found. - The result is false if no equal row is found (including the special - case where the subquery returns no rows). + The result returned by setval is just the value of its + second argument. + + + + - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand row yields - null, the result of the IN construct will be null, not false. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + If a sequence object has been created with default parameters, + nextval calls on it will return successive values + beginning with 1. Other behaviors can be obtained by using + special parameters in the command; + see its command reference page for more information. - - As with EXISTS, it's unwise to assume that the subquery will - be evaluated completely. - + + + To avoid blocking of concurrent transactions that obtain numbers from the + same sequence, a nextval operation is never rolled back; + that is, once a value has been fetched it is considered used, even if the + transaction that did the nextval later aborts. This means + that aborted transactions might leave unused holes in the + sequence of assigned values. setval operations are never + rolled back, either. + + - -row_constructor IN (subquery) - + - - The left-hand side of this form of IN is a row constructor, - as described in . - The right-hand side is a parenthesized - subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are - evaluated and compared row-wise to each row of the subquery result. - The result of IN is true if any equal subquery row is found. - The result is false if no equal row is found (including the special - case where the subquery returns no rows). - - - As usual, null values in the rows are combined per - the normal rules of SQL Boolean expressions. Two rows are considered - equal if all their corresponding members are non-null and equal; the rows - are unequal if any corresponding members are non-null and unequal; - otherwise the result of that row comparison is unknown (null). - If all the per-row results are either unequal or null, with at least one - null, then the result of IN is null. - - + + Conditional Expressions - - <literal>NOT IN</literal> + + CASE + - -expression NOT IN (subquery) - + + conditional expression + - The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression - is evaluated and compared to each row of the subquery result. - The result of NOT IN is true if only unequal subquery rows - are found (including the special case where the subquery returns no rows). - The result is false if any equal row is found. + This section describes the SQL-compliant conditional expressions + available in PostgreSQL. - - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand row yields - null, the result of the NOT IN construct will be null, not true. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. - + + + If your needs go beyond the capabilities of these conditional + expressions you might want to consider writing a stored procedure + in a more expressive programming language. + + + + + <literal>CASE</> - As with EXISTS, it's unwise to assume that the subquery will - be evaluated completely. - + The SQL CASE expression is a + generic conditional expression, similar to if/else statements in + other languages: -row_constructor NOT IN (subquery) +CASE WHEN condition THEN result + WHEN ... + ELSE result +END - - The left-hand side of this form of NOT IN is a row constructor, - as described in . - The right-hand side is a parenthesized - subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are - evaluated and compared row-wise to each row of the subquery result. - The result of NOT IN is true if only unequal subquery rows - are found (including the special case where the subquery returns no rows). - The result is false if any equal row is found. - - - - As usual, null values in the rows are combined per - the normal rules of SQL Boolean expressions. Two rows are considered - equal if all their corresponding members are non-null and equal; the rows - are unequal if any corresponding members are non-null and unequal; - otherwise the result of that row comparison is unknown (null). - If all the per-row results are either unequal or null, with at least one - null, then the result of NOT IN is null. + CASE clauses can be used wherever + an expression is valid. condition is an + expression that returns a boolean result. If the result is true + then the value of the CASE expression is the + result that follows the condition. If the result is false any + subsequent WHEN clauses are searched in the same + manner. If no WHEN + condition is true then the value of the + case expression is the result in the + ELSE clause. If the ELSE clause is + omitted and no condition matches, the result is null. - - - <literal>ANY</literal>/<literal>SOME</literal> + + An example: + +SELECT * FROM test; - -expression operator ANY (subquery) -expression operator SOME (subquery) - + a +--- + 1 + 2 + 3 - - The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression - is evaluated and compared to each row of the subquery result using the - given operator, which must yield a Boolean - result. - The result of ANY is true if any true result is obtained. - The result is false if no true result is found (including the special - case where the subquery returns no rows). - - - SOME is a synonym for ANY. - IN is equivalent to = ANY. - +SELECT a, + CASE WHEN a=1 THEN 'one' + WHEN a=2 THEN 'two' + ELSE 'other' + END + FROM test; - - Note that if there are no successes and at least one right-hand row yields - null for the operator's result, the result of the ANY construct - will be null, not false. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. - + a | case +---+------- + 1 | one + 2 | two + 3 | other + + - As with EXISTS, it's unwise to assume that the subquery will - be evaluated completely. + The data types of all the result + expressions must be convertible to a single output type. + See for more detail. + + The following simple CASE expression is a + specialized variant of the general form above: + -row_constructor operator ANY (subquery) -row_constructor operator SOME (subquery) +CASE expression + WHEN value THEN result + WHEN ... + ELSE result +END - - The left-hand side of this form of ANY is a row constructor, - as described in . - The right-hand side is a parenthesized - subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are - evaluated and compared row-wise to each row of the subquery result, - using the given operator. - The result of ANY is true if the comparison - returns true for any subquery row. - The result is false if the comparison returns false for every - subquery row (including the special case where the subquery returns no - rows). - The result is NULL if the comparison does not return true for any row, - and it returns NULL for at least one row. + The + expression is computed and compared to + all the value specifications in the + WHEN clauses until one is found that is equal. If + no match is found, the result in the + ELSE clause (or a null value) is returned. This is similar + to the switch statement in C. - - See for details about the meaning - of a row-wise comparison. - + + The example above can be written using the simple + CASE syntax: + +SELECT a, + CASE a WHEN 1 THEN 'one' + WHEN 2 THEN 'two' + ELSE 'other' + END + FROM test; + + a | case +---+------- + 1 | one + 2 | two + 3 | other + + + + + A CASE expression does not evaluate any subexpressions + that are not needed to determine the result. For example, this is a + possible way of avoiding a division-by-zero failure: + +SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END; + + - <literal>ALL</literal> + <literal>COALESCE</> + + + COALESCE + + + + NVL + + + + IFNULL + -expression operator ALL (subquery) +COALESCE(value , ...) - The right-hand side is a parenthesized - subquery, which must return exactly one column. The left-hand expression - is evaluated and compared to each row of the subquery result using the - given operator, which must yield a Boolean - result. - The result of ALL is true if all rows yield true - (including the special case where the subquery returns no rows). - The result is false if any false result is found. - The result is NULL if the comparison does not return false for any row, - and it returns NULL for at least one row. + The COALESCE function returns the first of its + arguments that is not null. Null is returned only if all arguments + are null. It is often used to substitute a default value for + null values when data is retrieved for display, for example: + +SELECT COALESCE(description, short_description, '(none)') ... + - - NOT IN is equivalent to <> ALL. - + + Like a CASE expression, COALESCE will + not evaluate arguments that are not needed to determine the result; + that is, arguments to the right of the first non-null argument are + not evaluated. This SQL-standard function provides capabilities similar + to NVL and IFNULL, which are used in some other + database systems. + + - - As with EXISTS, it's unwise to assume that the subquery will - be evaluated completely. - + + <literal>NULLIF</> + + + NULLIF + -row_constructor operator ALL (subquery) +NULLIF(value1, value2) - The left-hand side of this form of ALL is a row constructor, - as described in . - The right-hand side is a parenthesized - subquery, which must return exactly as many columns as there are - expressions in the left-hand row. The left-hand expressions are - evaluated and compared row-wise to each row of the subquery result, - using the given operator. - The result of ALL is true if the comparison - returns true for all subquery rows (including the special - case where the subquery returns no rows). - The result is false if the comparison returns false for any - subquery row. - The result is NULL if the comparison does not return false for any - subquery row, and it returns NULL for at least one row. + The NULLIF function returns a null value if + value1 and value2 + are equal; otherwise it returns value1. + This can be used to perform the inverse operation of the + COALESCE example given above: + +SELECT NULLIF(value, '(none)') ... + - - See for details about the meaning - of a row-wise comparison. + If value1 is (none), return a null, + otherwise return value1. + - Row-wise Comparison + <literal>GREATEST</literal> and <literal>LEAST</literal> - - comparison - subquery result row - + + GREATEST + + + LEAST + -row_constructor operator (subquery) +GREATEST(value , ...) + + +LEAST(value , ...) - - The left-hand side is a row constructor, - as described in . - The right-hand side is a parenthesized subquery, which must return exactly - as many columns as there are expressions in the left-hand row. Furthermore, - the subquery cannot return more than one row. (If it returns zero rows, - the result is taken to be null.) The left-hand side is evaluated and - compared row-wise to the single subquery result row. - + + The GREATEST and LEAST functions select the + largest or smallest value from a list of any number of expressions. + The expressions must all be convertible to a common data type, which + will be the type of the result + (see for details). NULL values + in the list are ignored. The result will be NULL only if all the + expressions evaluate to NULL. + - - See for details about the meaning - of a row-wise comparison. - + + Note that GREATEST and LEAST are not in + the SQL standard, but are a common extension. + - - Row and Array Comparisons + + Array Functions and Operators - - IN - + + shows the operators + available for array types. + + + + <type>array</type> Operators + + + + Operator + Description + Example + Result + + + + + = + equal + ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] + t + + + + <> + not equal + ARRAY[1,2,3] <> ARRAY[1,2,4] + t + + + + < + less than + ARRAY[1,2,3] < ARRAY[1,2,4] + t + + + + > + greater than + ARRAY[1,4,3] > ARRAY[1,2,4] + t + + + + <= + less than or equal + ARRAY[1,2,3] <= ARRAY[1,2,3] + t + - - NOT IN - + + >= + greater than or equal + ARRAY[1,4,3] >= ARRAY[1,4,3] + t + - - ANY - + + @> + contains + ARRAY[1,4,3] @> ARRAY[3,1] + t + - - ALL - + + <@ + is contained by + ARRAY[2,7] <@ ARRAY[1,7,4,2,6] + t + - - SOME - + + && + overlap (have elements in common) + ARRAY[1,4,3] && ARRAY[2,1] + t + - - row-wise comparison - + + || + array-to-array concatenation + ARRAY[1,2,3] || ARRAY[4,5,6] + {1,2,3,4,5,6} + - - comparison - row-wise - + + || + array-to-array concatenation + ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] + {{1,2,3},{4,5,6},{7,8,9}} + - - IS DISTINCT FROM - + + || + element-to-array concatenation + 3 || ARRAY[4,5,6] + {3,4,5,6} + - - IS NOT DISTINCT FROM - + + || + array-to-element concatenation + ARRAY[4,5,6] || 7 + {4,5,6,7} + + + +
- This section describes several specialized constructs for making - multiple comparisons between groups of values. These forms are - syntactically related to the subquery forms of the previous section, - but do not involve subqueries. - The forms involving array subexpressions are - PostgreSQL extensions; the rest are - SQL-compliant. - All of the expression forms documented in this section return - Boolean (true/false) results. + Array comparisons compare the array contents element-by-element, + using the default B-Tree comparison function for the element data type. + In multidimensional arrays the elements are visited in row-major order + (last subscript varies most rapidly). + If the contents of two arrays are equal but the dimensionality is + different, the first difference in the dimensionality information + determines the sort order. (This is a change from versions of + PostgreSQL prior to 8.2: older versions would claim + that two arrays with the same contents were equal, even if the + number of dimensions or subscript ranges were different.) - - <literal>IN</literal> - - -expression IN (value , ...) - - - The right-hand side is a parenthesized list - of scalar expressions. The result is true if the left-hand expression's - result is equal to any of the right-hand expressions. This is a shorthand - notation for - - -expression = value1 -OR -expression = value2 -OR -... - + See for more details about array operator + behavior. - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand expression yields - null, the result of the IN construct will be null, not false. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + shows the functions + available for use with array types. See + for more discussion and examples of the use of these functions. - - - - <literal>NOT IN</literal> - -expression NOT IN (value , ...) - + + <type>array</type> Functions + + + + Function + Return Type + Description + Example + Result + + + + + + + array_append(anyarray, anyelement) + + + anyarray + append an element to the end of an array + array_append(ARRAY[1,2], 3) + {1,2,3} + + + + + array_cat(anyarray, anyarray) + + + anyarray + concatenate two arrays + array_cat(ARRAY[1,2,3], ARRAY[4,5]) + {1,2,3,4,5} + + + + + array_dims(anyarray) + + + text + returns a text representation of array's dimensions + array_dims(ARRAY[[1,2,3], [4,5,6]]) + [1:2][1:3] + + + + + array_lower(anyarray, int) + + + int + returns lower bound of the requested array dimension + array_lower('[0:2]={1,2,3}'::int[], 1) + 0 + + + + + array_prepend(anyelement, anyarray) + + + anyarray + append an element to the beginning of an array + array_prepend(1, ARRAY[2,3]) + {1,2,3} + + + + + array_to_string(anyarray, text) + + + text + concatenates array elements using provided delimiter + array_to_string(ARRAY[1, 2, 3], '~^~') + 1~^~2~^~3 + + + + + array_upper(anyarray, int) + + + int + returns upper bound of the requested array dimension + array_upper(ARRAY[1,2,3,4], 1) + 4 + + + + + string_to_array(text, text) + + + text[] + splits string into array elements using provided delimiter + string_to_array('xx~^~yy~^~zz', '~^~') + {xx,yy,zz} + + + +
+ - - The right-hand side is a parenthesized list - of scalar expressions. The result is true if the left-hand expression's - result is unequal to all of the right-hand expressions. This is a shorthand - notation for + + Aggregate Functions - -expression <> value1 -AND -expression <> value2 -AND -... - - + + aggregate function + built-in + - Note that if the left-hand expression yields null, or if there are - no equal right-hand values and at least one right-hand expression yields - null, the result of the NOT IN construct will be null, not true - as one might naively expect. - This is in accordance with SQL's normal rules for Boolean combinations - of null values. + Aggregate functions compute a single result + value from a set of input values. The built-in aggregate functions + are listed in + and + . + The special syntax considerations for aggregate + functions are explained in . + Consult for additional introductory + information. - - - x NOT IN y is equivalent to NOT (x IN y) in all - cases. However, null values are much more likely to trip up the novice when - working with NOT IN than when working with IN. - It's best to express your condition positively if possible. - - - + + General-Purpose Aggregate Functions - - <literal>ANY</literal>/<literal>SOME</literal> (array) + + + + Function + Argument Type + Return Type + Description + + - -expression operator ANY (array expression) -expression operator SOME (array expression) - + + + + + average + + avg(expression) + + + smallint, int, + bigint, real, double + precision, numeric, or interval + + + numeric for any integer type argument, + double precision for a floating-point argument, + otherwise the same as the argument data type + + the average (arithmetic mean) of all input values + - - The right-hand side is a parenthesized expression, which must yield an - array value. - The left-hand expression - is evaluated and compared to each element of the array using the - given operator, which must yield a Boolean - result. - The result of ANY is true if any true result is obtained. - The result is false if no true result is found (including the special - case where the array has zero elements). - + + + + bit_and + + bit_and(expression) + + + smallint, int, bigint, or + bit + + + same as argument data type + + the bitwise AND of all non-null input values, or null if none + - - If the array expression yields a null array, the result of - ANY will be null. If the left-hand expression yields null, - the result of ANY is ordinarily null (though a non-strict - comparison operator could possibly yield a different result). - Also, if the right-hand array contains any null elements and no true - comparison result is obtained, the result of ANY - will be null, not false (again, assuming a strict comparison operator). - This is in accordance with SQL's normal rules for Boolean combinations - of null values. - + + + + bit_or + + bit_or(expression) + + + smallint, int, bigint, or + bit + + + same as argument data type + + the bitwise OR of all non-null input values, or null if none + - - SOME is a synonym for ANY. - - + + + + bool_and + + bool_and(expression) + + + bool + + + bool + + true if all input values are true, otherwise false + - - <literal>ALL</literal> (array) + + + + bool_or + + bool_or(expression) + + + bool + + + bool + + true if at least one input value is true, otherwise false + - -expression operator ALL (array expression) - + + count(*) + + bigint + number of input rows + - - The right-hand side is a parenthesized expression, which must yield an - array value. - The left-hand expression - is evaluated and compared to each element of the array using the - given operator, which must yield a Boolean - result. - The result of ALL is true if all comparisons yield true - (including the special case where the array has zero elements). - The result is false if any false result is found. - + + count(expression) + any + bigint + + number of input rows for which the value of expression is not null + + - - If the array expression yields a null array, the result of - ALL will be null. If the left-hand expression yields null, - the result of ALL is ordinarily null (though a non-strict - comparison operator could possibly yield a different result). - Also, if the right-hand array contains any null elements and no false - comparison result is obtained, the result of ALL - will be null, not true (again, assuming a strict comparison operator). - This is in accordance with SQL's normal rules for Boolean combinations - of null values. - - + + + + every + + every(expression) + + + bool + + + bool + + equivalent to bool_and + - - Row-wise Comparison + + max(expression) + any array, numeric, string, or date/time type + same as argument type + + maximum value of expression across all input + values + + - -row_constructor operator row_constructor - + + min(expression) + any array, numeric, string, or date/time type + same as argument type + + minimum value of expression across all input + values + + - - Each side is a row constructor, - as described in . - The two row values must have the same number of fields. - Each side is evaluated and they are compared row-wise. Row comparisons - are allowed when the operator is - =, - <>, - <, - <=, - > or - >=, - or has semantics similar to one of these. (To be specific, an operator - can be a row comparison operator if it is a member of a B-Tree operator - class, or is the negator of the = member of a B-Tree operator - class.) - + + sum(expression) + + smallint, int, + bigint, real, double + precision, numeric, or + interval + + + bigint for smallint or + int arguments, numeric for + bigint arguments, double precision + for floating-point arguments, otherwise the same as the + argument data type + + sum of expression across all input values + + + +
- The = and <> cases work slightly differently - from the others. Two rows are considered - equal if all their corresponding members are non-null and equal; the rows - are unequal if any corresponding members are non-null and unequal; - otherwise the result of the row comparison is unknown (null). + It should be noted that except for count, + these functions return a null value when no rows are selected. In + particular, sum of no rows returns null, not + zero as one might expect. The coalesce function can be + used to substitute zero for null when necessary. - - For the <, <=, > and - >= cases, the row elements are compared left-to-right, - stopping as soon as an unequal or null pair of elements is found. - If either of this pair of elements is null, the result of the - row comparison is unknown (null); otherwise comparison of this pair - of elements determines the result. For example, - ROW(1,2,NULL) < ROW(1,3,0) - yields true, not null, because the third pair of elements are not - considered. - + + + ANY + + + SOME + + + Boolean aggregates bool_and and + bool_or correspond to standard SQL aggregates + every and any or + some. + As for any and some, + it seems that there is an ambiguity built into the standard syntax: + +SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...; + + Here ANY can be considered both as leading + to a subquery or as an aggregate if the select expression returns 1 row. + Thus the standard name cannot be given to these aggregates. + + - Prior to PostgreSQL 8.2, the - <, <=, > and >= - cases were not handled per SQL specification. A comparison like - ROW(a,b) < ROW(c,d) - was implemented as - a < c AND b < d - whereas the correct behavior is equivalent to - a < c OR (a = c AND b < d). + Users accustomed to working with other SQL database management + systems might be surprised by the performance of the + count aggregate when it is applied to the + entire table. A query like: + +SELECT count(*) FROM sometable; + + will be executed by PostgreSQL using a + sequential scan of the entire table. - -row_constructor IS DISTINCT FROM row_constructor - - This construct is similar to a <> row comparison, - but it does not yield null for null inputs. Instead, any null value is - considered unequal to (distinct from) any non-null value, and any two - nulls are considered equal (not distinct). Thus the result will always - be either true or false, never null. + shows + aggregate functions typically used in statistical analysis. + (These are separated out merely to avoid cluttering the listing + of more-commonly-used aggregates.) Where the description mentions + N, it means the + number of input rows for which all the input expressions are non-null. + In all cases, null is returned if the computation is meaningless, + for example when N is zero. - -row_constructor IS NOT DISTINCT FROM row_constructor - + + statistics + + + linear regression + - - This construct is similar to a = row comparison, - but it does not yield null for null inputs. Instead, any null value is - considered unequal to (distinct from) any non-null value, and any two - nulls are considered equal (not distinct). Thus the result will always - be either true or false, never null. - + + Aggregate Functions for Statistics + + + + + Function + Argument Type + Return Type + Description + + + + + + + + + correlation + + corr(Y, X) + + + double precision + + + double precision + + correlation coefficient + + + + + + covariance + population + + covar_pop(Y, X) + + + double precision + + + double precision + + population covariance + + + + + + covariance + sample + + covar_samp(Y, X) + + + double precision + + + double precision + + sample covariance + + + + + regr_avgx(Y, X) + + + double precision + + + double precision + + average of the independent variable + (sum(X)/N) + + + + + regr_avgy(Y, X) + + + double precision + + + double precision + + average of the dependent variable + (sum(Y)/N) + + + + + regr_count(Y, X) + + + double precision + + + bigint + + number of input rows in which both expressions are nonnull + + + + + + regression intercept + + regr_intercept(Y, X) + + + double precision + + + double precision + + y-intercept of the least-squares-fit linear equation + determined by the (X, Y) pairs + + + + + regr_r2(Y, X) + + + double precision + + + double precision + + square of the correlation coefficient + + + + + + regression slope + + regr_slope(Y, X) + + + double precision + + + double precision + + slope of the least-squares-fit linear equation determined + by the (X, + Y) pairs + - - + + + regr_sxx(Y, X) + + + double precision + + + double precision + + sum(X^2) - sum(X)^2/N (sum of + squares of the independent variable) + - - Set Returning Functions + + + regr_sxy(Y, X) + + + double precision + + + double precision + + sum(X*Y) - sum(X) * sum(Y)/N (sum of + products of independent times dependent + variable) + - - set returning functions - functions - + + + regr_syy(Y, X) + + + double precision + + + double precision + + sum(Y^2) - sum(Y)^2/N (sum of + squares of the dependent variable) + - - generate_series - + + + + standard deviation + + stddev(expression) + + + smallint, int, + bigint, real, double + precision, or numeric + + + double precision for floating-point arguments, + otherwise numeric + + historical alias for stddev_samp + - - This section describes functions that possibly return more than one row. - Currently the only functions in this class are series generating functions, - as detailed in . - + + + + standard deviation + population + + stddev_pop(expression) + + + smallint, int, + bigint, real, double + precision, or numeric + + + double precision for floating-point arguments, + otherwise numeric + + population standard deviation of the input values + -
- Series Generating Functions - - - Function - Argument Type - Return Type - Description + + + standard deviation + sample + + stddev_samp(expression) + + + smallint, int, + bigint, real, double + precision, or numeric + + + double precision for floating-point arguments, + otherwise numeric + + sample standard deviation of the input values - - - generate_series(start, stop) - int or bigint - setof int or setof bigint (same as argument type) - Generate a series of values, from start to stop - with a step size of one + + variance + + variance(expression) + + smallint, int, + bigint, real, double + precision, or numeric + + + double precision for floating-point arguments, + otherwise numeric + + historical alias for var_samp - generate_series(start, stop, step) - int or bigint - setof int or setof bigint (same as argument type) - Generate a series of values, from start to stop - with a step size of step + + variance + population + + var_pop(expression) + + + smallint, int, + bigint, real, double + precision, or numeric + + + double precision for floating-point arguments, + otherwise numeric + population variance of the input values (square of the population standard deviation) + + + + variance + sample + + var_samp(expression) + + + smallint, int, + bigint, real, double + precision, or numeric + + + double precision for floating-point arguments, + otherwise numeric + + sample variance of the input values (square of the sample standard deviation) + -
- - - When step is positive, zero rows are returned if - start is greater than stop. - Conversely, when step is negative, zero rows are - returned if start is less than stop. - Zero rows are also returned for NULL inputs. It is an error - for step to be zero. Some examples follow: - -select * from generate_series(2,4); - generate_series ------------------ - 2 - 3 - 4 -(3 rows) - -select * from generate_series(5,1,-2); - generate_series ------------------ - 5 - 3 - 1 -(3 rows) - -select * from generate_series(4,3); - generate_series ------------------ -(0 rows) - -select current_date + s.a as dates from generate_series(0,14,7) as s(a); - dates ------------- - 2004-02-05 - 2004-02-12 - 2004-02-19 -(3 rows) - - - - - - System Information Functions - - - shows several - functions that extract session and system information. - - - - Session Information Functions - - - Name Return Type Description - - - - - current_database() - name - name of current database - +
- - current_schema() - name - name of current schema - + - - current_schemas(boolean) - name[] - names of schemas in search path optionally including implicit schemas - - - current_user - name - user name of current execution context - + + Subquery Expressions - - inet_client_addr() - inet - address of the remote connection - + + EXISTS + - - inet_client_port() - int - port of the remote connection - + + IN + - - inet_server_addr() - inet - address of the local connection - + + NOT IN + - - inet_server_port() - int - port of the local connection - + + ANY + - - pg_my_temp_schema() - oid - OID of session's temporary schema, or 0 if none - + + ALL + - - pg_is_other_temp_schema(oid) - boolean - is schema another session's temporary schema? - + + SOME + - - pg_postmaster_start_time() - timestamp with time zone - server start time - + + subquery + - - session_user - name - session user name - + + This section describes the SQL-compliant subquery + expressions available in PostgreSQL. + All of the expression forms documented in this section return + Boolean (true/false) results. + - - user - name - equivalent to current_user - + + <literal>EXISTS</literal> - - version() - text - PostgreSQL version information - - - - + +EXISTS (subquery) + - - user - current - + + The argument of EXISTS is an arbitrary SELECT statement, + or subquery. The + subquery is evaluated to determine whether it returns any rows. + If it returns at least one row, the result of EXISTS is + true; if the subquery returns no rows, the result of EXISTS + is false. + - - schema - current - + + The subquery can refer to variables from the surrounding query, + which will act as constants during any one evaluation of the subquery. + - - search path - current - + + The subquery will generally only be executed far enough to determine + whether at least one row is returned, not all the way to completion. + It is unwise to write a subquery that has any side effects (such as + calling sequence functions); whether the side effects occur or not + might be difficult to predict. + - - The session_user is normally the user who initiated - the current database connection; but superusers can change this setting - with . - The current_user is the user identifier - that is applicable for permission checking. Normally, it is equal - to the session user, but it can be changed with - . - It also changes during the execution of - functions with the attribute SECURITY DEFINER. - In Unix parlance, the session user is the real user and - the current user is the effective user. - + + Since the result depends only on whether any rows are returned, + and not on the contents of those rows, the output list of the + subquery is normally uninteresting. A common coding convention is + to write all EXISTS tests in the form + EXISTS(SELECT 1 WHERE ...). There are exceptions to + this rule however, such as subqueries that use INTERSECT. + - - - current_user, session_user, and - user have special syntactic status in SQL: - they must be called without trailing parentheses. - - + + This simple example is like an inner join on col2, but + it produces at most one output row for each tab1 row, + even if there are multiple matching tab2 rows: + +SELECT col1 FROM tab1 + WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2); + + + - - current_schema returns the name of the schema that is - at the front of the search path (or a null value if the search path is - empty). This is the schema that will be used for any tables or - other named objects that are created without specifying a target schema. - current_schemas(boolean) returns an array of the names of all - schemas presently in the search path. The Boolean option determines whether or not - implicitly included system schemas such as pg_catalog are included in the search - path returned. - + + <literal>IN</literal> - - - The search path can be altered at run time. The command is: - -SET search_path TO schema , schema, ... - - - + +expression IN (subquery) + - - inet_client_addr - + + The right-hand side is a parenthesized + subquery, which must return exactly one column. The left-hand expression + is evaluated and compared to each row of the subquery result. + The result of IN is true if any equal subquery row is found. + The result is false if no equal row is found (including the special + case where the subquery returns no rows). + - - inet_client_port - + + Note that if the left-hand expression yields null, or if there are + no equal right-hand values and at least one right-hand row yields + null, the result of the IN construct will be null, not false. + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + - - inet_server_addr - + + As with EXISTS, it's unwise to assume that the subquery will + be evaluated completely. + - - inet_server_port - + +row_constructor IN (subquery) + + + + The left-hand side of this form of IN is a row constructor, + as described in . + The right-hand side is a parenthesized + subquery, which must return exactly as many columns as there are + expressions in the left-hand row. The left-hand expressions are + evaluated and compared row-wise to each row of the subquery result. + The result of IN is true if any equal subquery row is found. + The result is false if no equal row is found (including the special + case where the subquery returns no rows). + + + + As usual, null values in the rows are combined per + the normal rules of SQL Boolean expressions. Two rows are considered + equal if all their corresponding members are non-null and equal; the rows + are unequal if any corresponding members are non-null and unequal; + otherwise the result of that row comparison is unknown (null). + If all the per-row results are either unequal or null, with at least one + null, then the result of IN is null. + + + + + <literal>NOT IN</literal> - - inet_client_addr returns the IP address of the - current client, and inet_client_port returns the - port number. - inet_server_addr returns the IP address on which - the server accepted the current connection, and - inet_server_port returns the port number. - All these functions return NULL if the current connection is via a - Unix-domain socket. - + +expression NOT IN (subquery) + - - pg_my_temp_schema - + + The right-hand side is a parenthesized + subquery, which must return exactly one column. The left-hand expression + is evaluated and compared to each row of the subquery result. + The result of NOT IN is true if only unequal subquery rows + are found (including the special case where the subquery returns no rows). + The result is false if any equal row is found. + - - pg_is_other_temp_schema - + + Note that if the left-hand expression yields null, or if there are + no equal right-hand values and at least one right-hand row yields + null, the result of the NOT IN construct will be null, not true. + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + - - pg_my_temp_schema returns the OID of the current - session's temporary schema, or 0 if it has none (because it has not - created any temporary tables). - pg_is_other_temp_schema returns true if the - given OID is the OID of any other session's temporary schema. - (This can be useful, for example, to exclude other sessions' temporary - tables from a catalog display.) - + + As with EXISTS, it's unwise to assume that the subquery will + be evaluated completely. + - - pg_postmaster_start_time - + +row_constructor NOT IN (subquery) + - - pg_postmaster_start_time returns the - timestamp with time zone when the - server started. - + + The left-hand side of this form of NOT IN is a row constructor, + as described in . + The right-hand side is a parenthesized + subquery, which must return exactly as many columns as there are + expressions in the left-hand row. The left-hand expressions are + evaluated and compared row-wise to each row of the subquery result. + The result of NOT IN is true if only unequal subquery rows + are found (including the special case where the subquery returns no rows). + The result is false if any equal row is found. + - - version - + + As usual, null values in the rows are combined per + the normal rules of SQL Boolean expressions. Two rows are considered + equal if all their corresponding members are non-null and equal; the rows + are unequal if any corresponding members are non-null and unequal; + otherwise the result of that row comparison is unknown (null). + If all the per-row results are either unequal or null, with at least one + null, then the result of NOT IN is null. + + - - version returns a string describing the - PostgreSQL server's version. - + + <literal>ANY</literal>/<literal>SOME</literal> - - privilege - querying - + +expression operator ANY (subquery) +expression operator SOME (subquery) + - lists functions that - allow the user to query object access privileges programmatically. - See for more information about - privileges. + The right-hand side is a parenthesized + subquery, which must return exactly one column. The left-hand expression + is evaluated and compared to each row of the subquery result using the + given operator, which must yield a Boolean + result. + The result of ANY is true if any true result is obtained. + The result is false if no true result is found (including the special + case where the subquery returns no rows). - - Access Privilege Inquiry Functions - - - Name Return Type Description - + + SOME is a synonym for ANY. + IN is equivalent to = ANY. + - - - has_database_privilege(user, - database, - privilege) - - boolean - does user have privilege for database - - - has_database_privilege(database, - privilege) - - boolean - does current user have privilege for database - - - has_function_privilege(user, - function, - privilege) - - boolean - does user have privilege for function - - - has_function_privilege(function, - privilege) - - boolean - does current user have privilege for function - - - has_language_privilege(user, - language, - privilege) - - boolean - does user have privilege for language - - - has_language_privilege(language, - privilege) - - boolean - does current user have privilege for language - - - has_schema_privilege(user, - schema, - privilege) - - boolean - does user have privilege for schema - - - has_schema_privilege(schema, - privilege) - - boolean - does current user have privilege for schema - - - has_table_privilege(user, - table, - privilege) - - boolean - does user have privilege for table - - - has_table_privilege(table, - privilege) - - boolean - does current user have privilege for table - - - has_tablespace_privilege(user, - tablespace, - privilege) - - boolean - does user have privilege for tablespace - - - has_tablespace_privilege(tablespace, - privilege) - - boolean - does current user have privilege for tablespace - - - pg_has_role(user, - role, - privilege) - - boolean - does user have privilege for role - - - pg_has_role(role, - privilege) - - boolean - does current user have privilege for role - - - -
+ + Note that if there are no successes and at least one right-hand row yields + null for the operator's result, the result of the ANY construct + will be null, not false. + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + - - has_database_privilege - - - has_function_privilege - - - has_language_privilege - - - has_schema_privilege - - - has_table_privilege - - - has_tablespace_privilege - - - pg_has_role - + + As with EXISTS, it's unwise to assume that the subquery will + be evaluated completely. + - - has_database_privilege checks whether a user - can access a database in a particular way. The possibilities for its - arguments are analogous to has_table_privilege. - The desired access privilege type must evaluate to - CREATE, - CONNECT, - TEMPORARY, or - TEMP (which is equivalent to - TEMPORARY). - + +row_constructor operator ANY (subquery) +row_constructor operator SOME (subquery) + - - has_function_privilege checks whether a user - can access a function in a particular way. The possibilities for its - arguments are analogous to has_table_privilege. - When specifying a function by a text string rather than by OID, - the allowed input is the same as for the regprocedure data type - (see ). - The desired access privilege type must evaluate to - EXECUTE. - An example is: - -SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); - - + + The left-hand side of this form of ANY is a row constructor, + as described in . + The right-hand side is a parenthesized + subquery, which must return exactly as many columns as there are + expressions in the left-hand row. The left-hand expressions are + evaluated and compared row-wise to each row of the subquery result, + using the given operator. + The result of ANY is true if the comparison + returns true for any subquery row. + The result is false if the comparison returns false for every + subquery row (including the special case where the subquery returns no + rows). + The result is NULL if the comparison does not return true for any row, + and it returns NULL for at least one row. + - - has_language_privilege checks whether a user - can access a procedural language in a particular way. The possibilities - for its arguments are analogous to has_table_privilege. - The desired access privilege type must evaluate to - USAGE. - + + See for details about the meaning + of a row-wise comparison. + + - - has_schema_privilege checks whether a user - can access a schema in a particular way. The possibilities for its - arguments are analogous to has_table_privilege. - The desired access privilege type must evaluate to - CREATE or - USAGE. - + + <literal>ALL</literal> - - has_table_privilege checks whether a user - can access a table in a particular way. The user can be - specified by name or by OID - (pg_authid.oid), or if the argument is - omitted - current_user is assumed. The table can be specified - by name or by OID. (Thus, there are actually six variants of - has_table_privilege, which can be distinguished by - the number and types of their arguments.) When specifying by name, - the name can be schema-qualified if necessary. - The desired access privilege type - is specified by a text string, which must evaluate to one of the - values SELECT, INSERT, - UPDATE, DELETE, - REFERENCES, or TRIGGER. - (Case of the string is not significant, however.) - An example is: - -SELECT has_table_privilege('myschema.mytable', 'select'); - - + +expression operator ALL (subquery) + - - has_tablespace_privilege checks whether a user - can access a tablespace in a particular way. The possibilities for its - arguments are analogous to has_table_privilege. - The desired access privilege type must evaluate to - CREATE. - + + The right-hand side is a parenthesized + subquery, which must return exactly one column. The left-hand expression + is evaluated and compared to each row of the subquery result using the + given operator, which must yield a Boolean + result. + The result of ALL is true if all rows yield true + (including the special case where the subquery returns no rows). + The result is false if any false result is found. + The result is NULL if the comparison does not return false for any row, + and it returns NULL for at least one row. + - - pg_has_role checks whether a user - can access a role in a particular way. The possibilities for its - arguments are analogous to has_table_privilege. - The desired access privilege type must evaluate to - MEMBER or - USAGE. - MEMBER denotes direct or indirect membership in - the role (that is, the right to do SET ROLE), while - USAGE denotes whether the privileges of the role - are immediately available without doing SET ROLE. - + + NOT IN is equivalent to <> ALL. + - To test whether a user holds a grant option on the privilege, - append WITH GRANT OPTION to the privilege key - word; for example 'UPDATE WITH GRANT OPTION'. + As with EXISTS, it's unwise to assume that the subquery will + be evaluated completely. + +row_constructor operator ALL (subquery) + + - shows functions that - determine whether a certain object is visible in the - current schema search path. A table is said to be visible if its - containing schema is in the search path and no table of the same - name appears earlier in the search path. This is equivalent to the - statement that the table can be referenced by name without explicit - schema qualification. For example, to list the names of all - visible tables: - -SELECT relname FROM pg_class WHERE pg_table_is_visible(oid); - + The left-hand side of this form of ALL is a row constructor, + as described in . + The right-hand side is a parenthesized + subquery, which must return exactly as many columns as there are + expressions in the left-hand row. The left-hand expressions are + evaluated and compared row-wise to each row of the subquery result, + using the given operator. + The result of ALL is true if the comparison + returns true for all subquery rows (including the special + case where the subquery returns no rows). + The result is false if the comparison returns false for any + subquery row. + The result is NULL if the comparison does not return false for any + subquery row, and it returns NULL for at least one row. - - Schema Visibility Inquiry Functions - - - Name Return Type Description - + + See for details about the meaning + of a row-wise comparison. + + - - - pg_conversion_is_visible(conversion_oid) - - boolean - is conversion visible in search path - - - pg_function_is_visible(function_oid) - - boolean - is function visible in search path - - - pg_operator_is_visible(operator_oid) - - boolean - is operator visible in search path - - - pg_opclass_is_visible(opclass_oid) - - boolean - is operator class visible in search path - - - pg_table_is_visible(table_oid) - - boolean - is table visible in search path - - - pg_type_is_visible(type_oid) - - boolean - is type (or domain) visible in search path - - - -
+ + Row-wise Comparison - - pg_conversion_is_visible - - - pg_function_is_visible - - - pg_operator_is_visible - - - pg_opclass_is_visible - - - pg_table_is_visible - - - pg_type_is_visible + + comparison + subquery result row - - pg_conversion_is_visible, - pg_function_is_visible, - pg_operator_is_visible, - pg_opclass_is_visible, - pg_table_is_visible, and - pg_type_is_visible perform the visibility check for - conversions, functions, operators, operator classes, tables, and - types. Note that pg_table_is_visible can also be used - with views, indexes and sequences; pg_type_is_visible - can also be used with domains. For functions and operators, an object in - the search path is visible if there is no object of the same name - and argument data type(s) earlier in the path. For operator - classes, both name and associated index access method are considered. - + +row_constructor operator (subquery) + + + + The left-hand side is a row constructor, + as described in . + The right-hand side is a parenthesized subquery, which must return exactly + as many columns as there are expressions in the left-hand row. Furthermore, + the subquery cannot return more than one row. (If it returns zero rows, + the result is taken to be null.) The left-hand side is evaluated and + compared row-wise to the single subquery result row. + + + + See for details about the meaning + of a row-wise comparison. + + + + - - All these functions require object OIDs to identify the object to be - checked. If you want to test an object by name, it is convenient to use - the OID alias types (regclass, regtype, - regprocedure, or regoperator), for example: - -SELECT pg_type_is_visible('myschema.widget'::regtype); - - Note that it would not make much sense to test an unqualified name in - this way — if the name can be recognized at all, it must be visible. - + + Row and Array Comparisons - - format_type - + + IN + - - pg_get_viewdef - + + NOT IN + - - pg_get_ruledef - + + ANY + - - pg_get_indexdef - + + ALL + - - pg_get_triggerdef - + + SOME + - - pg_get_constraintdef - + + row-wise comparison + - - pg_get_expr - + + comparison + row-wise + - - pg_get_userbyid - + + IS DISTINCT FROM + - - pg_get_serial_sequence - + + IS NOT DISTINCT FROM + - - pg_tablespace_databases - + + This section describes several specialized constructs for making + multiple comparisons between groups of values. These forms are + syntactically related to the subquery forms of the previous section, + but do not involve subqueries. + The forms involving array subexpressions are + PostgreSQL extensions; the rest are + SQL-compliant. + All of the expression forms documented in this section return + Boolean (true/false) results. + + + + <literal>IN</literal> + + +expression IN (value , ...) + - lists functions that - extract information from the system catalogs. + The right-hand side is a parenthesized list + of scalar expressions. The result is true if the left-hand expression's + result is equal to any of the right-hand expressions. This is a shorthand + notation for + + +expression = value1 +OR +expression = value2 +OR +... + - - System Catalog Information Functions - - - Name Return Type Description - + + Note that if the left-hand expression yields null, or if there are + no equal right-hand values and at least one right-hand expression yields + null, the result of the IN construct will be null, not false. + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + + - - - format_type(type_oid, typemod) - text - get SQL name of a data type - - - pg_get_constraintdef(constraint_oid) - text - get definition of a constraint - - - pg_get_constraintdef(constraint_oid, pretty_bool) - text - get definition of a constraint - - - pg_get_expr(expr_text, relation_oid) - text - decompile internal form of an expression, assuming that any Vars - in it refer to the relation indicated by the second parameter - - - pg_get_expr(expr_text, relation_oid, pretty_bool) - text - decompile internal form of an expression, assuming that any Vars - in it refer to the relation indicated by the second parameter - - - pg_get_indexdef(index_oid) - text - get CREATE INDEX command for index - - - pg_get_indexdef(index_oid, column_no, pretty_bool) - text - get CREATE INDEX command for index, - or definition of just one index column when - column_no is not zero - - - pg_get_ruledef(rule_oid) - text - get CREATE RULE command for rule - - - pg_get_ruledef(rule_oid, pretty_bool) - text - get CREATE RULE command for rule - - - pg_get_serial_sequence(table_name, column_name) - text - get name of the sequence that a serial or bigserial column - uses - - - pg_get_triggerdef(trigger_oid) - text - get CREATE [ CONSTRAINT ] TRIGGER command for trigger - - - pg_get_userbyid(roleid) - name - get role name with given ID - - - pg_get_viewdef(view_name) - text - get underlying SELECT command for view (deprecated) - - - pg_get_viewdef(view_name, pretty_bool) - text - get underlying SELECT command for view (deprecated) - - - pg_get_viewdef(view_oid) - text - get underlying SELECT command for view - - - pg_get_viewdef(view_oid, pretty_bool) - text - get underlying SELECT command for view - - - pg_tablespace_databases(tablespace_oid) - setof oid - get the set of database OIDs that have objects in the tablespace - - - -
+ + <literal>NOT IN</literal> + + +expression NOT IN (value , ...) + + + + The right-hand side is a parenthesized list + of scalar expressions. The result is true if the left-hand expression's + result is unequal to all of the right-hand expressions. This is a shorthand + notation for + + +expression <> value1 +AND +expression <> value2 +AND +... + + + + + Note that if the left-hand expression yields null, or if there are + no equal right-hand values and at least one right-hand expression yields + null, the result of the NOT IN construct will be null, not true + as one might naively expect. + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + + - format_type returns the SQL name of a data type that - is identified by its type OID and possibly a type modifier. Pass NULL - for the type modifier if no specific modifier is known. + x NOT IN y is equivalent to NOT (x IN y) in all + cases. However, null values are much more likely to trip up the novice when + working with NOT IN than when working with IN. + It's best to express your condition positively if possible. + + + + + <literal>ANY</literal>/<literal>SOME</literal> (array) + + +expression operator ANY (array expression) +expression operator SOME (array expression) + - pg_get_constraintdef, - pg_get_indexdef, pg_get_ruledef, - and pg_get_triggerdef, respectively reconstruct the - creating command for a constraint, index, rule, or trigger. (Note that this - is a decompiled reconstruction, not the original text of the command.) - pg_get_expr decompiles the internal form of an - individual expression, such as the default value for a column. It can be - useful when examining the contents of system catalogs. - pg_get_viewdef reconstructs the SELECT - query that defines a view. Most of these functions come in two variants, - one of which can optionally pretty-print the result. The - pretty-printed format is more readable, but the default format is more - likely to be interpreted the same way by future versions of - PostgreSQL; avoid using pretty-printed output for dump - purposes. Passing false for the pretty-print parameter yields - the same result as the variant that does not have the parameter at all. + The right-hand side is a parenthesized expression, which must yield an + array value. + The left-hand expression + is evaluated and compared to each element of the array using the + given operator, which must yield a Boolean + result. + The result of ANY is true if any true result is obtained. + The result is false if no true result is found (including the special + case where the array has zero elements). - pg_get_serial_sequence returns the name of the - sequence associated with a column, or NULL if no sequence is associated - with the column. The first input parameter is a table name with - optional schema, and the second parameter is a column name. Because - the first parameter is potentially a schema and table, it is not treated - as a double-quoted identifier, meaning it is lowercased by default, - while the second parameter, being just a column name, is treated as - double-quoted and has its case preserved. The function returns a value - suitably formatted for passing to the sequence functions (see ). This association can be modified or - removed with ALTER SEQUENCE OWNED BY. (The function - probably should have been called - pg_get_owned_sequence; its name reflects the fact - that it's typically used with serial or bigserial - columns.) + If the array expression yields a null array, the result of + ANY will be null. If the left-hand expression yields null, + the result of ANY is ordinarily null (though a non-strict + comparison operator could possibly yield a different result). + Also, if the right-hand array contains any null elements and no true + comparison result is obtained, the result of ANY + will be null, not false (again, assuming a strict comparison operator). + This is in accordance with SQL's normal rules for Boolean combinations + of null values. - pg_get_userbyid extracts a role's name given - its OID. + SOME is a synonym for ANY. + + + + <literal>ALL</literal> (array) + + +expression operator ALL (array expression) + - pg_tablespace_databases allows a tablespace to be - examined. It returns the set of OIDs of databases that have objects stored - in the tablespace. If this function returns any rows, the tablespace is not - empty and cannot be dropped. To display the specific objects populating the - tablespace, you will need to connect to the databases identified by - pg_tablespace_databases and query their - pg_class catalogs. + The right-hand side is a parenthesized expression, which must yield an + array value. + The left-hand expression + is evaluated and compared to each element of the array using the + given operator, which must yield a Boolean + result. + The result of ALL is true if all comparisons yield true + (including the special case where the array has zero elements). + The result is false if any false result is found. - - col_description - + + If the array expression yields a null array, the result of + ALL will be null. If the left-hand expression yields null, + the result of ALL is ordinarily null (though a non-strict + comparison operator could possibly yield a different result). + Also, if the right-hand array contains any null elements and no false + comparison result is obtained, the result of ALL + will be null, not true (again, assuming a strict comparison operator). + This is in accordance with SQL's normal rules for Boolean combinations + of null values. + + - - obj_description - + + Row-wise Comparison - - shobj_description - + +row_constructor operator row_constructor + - - comment - about database objects - + + Each side is a row constructor, + as described in . + The two row values must have the same number of fields. + Each side is evaluated and they are compared row-wise. Row comparisons + are allowed when the operator is + =, + <>, + <, + <=, + > or + >=, + or has semantics similar to one of these. (To be specific, an operator + can be a row comparison operator if it is a member of a B-Tree operator + class, or is the negator of the = member of a B-Tree operator + class.) + + + + The = and <> cases work slightly differently + from the others. Two rows are considered + equal if all their corresponding members are non-null and equal; the rows + are unequal if any corresponding members are non-null and unequal; + otherwise the result of the row comparison is unknown (null). + + + + For the <, <=, > and + >= cases, the row elements are compared left-to-right, + stopping as soon as an unequal or null pair of elements is found. + If either of this pair of elements is null, the result of the + row comparison is unknown (null); otherwise comparison of this pair + of elements determines the result. For example, + ROW(1,2,NULL) < ROW(1,3,0) + yields true, not null, because the third pair of elements are not + considered. + + - The functions shown in - extract comments previously stored with the command. A null value is returned if no - comment could be found matching the specified parameters. + Prior to PostgreSQL 8.2, the + <, <=, > and >= + cases were not handled per SQL specification. A comparison like + ROW(a,b) < ROW(c,d) + was implemented as + a < c AND b < d + whereas the correct behavior is equivalent to + a < c OR (a = c AND b < d). + - - Comment Information Functions - - - Name Return Type Description - + +row_constructor IS DISTINCT FROM row_constructor + - - - col_description(table_oid, column_number) - text - get comment for a table column - - - obj_description(object_oid, catalog_name) - text - get comment for a database object - - - obj_description(object_oid) - text - get comment for a database object (deprecated) - - - shobj_description(object_oid, catalog_name) - text - get comment for a shared database object - - - -
+ + This construct is similar to a <> row comparison, + but it does not yield null for null inputs. Instead, any null value is + considered unequal to (distinct from) any non-null value, and any two + nulls are considered equal (not distinct). Thus the result will always + be either true or false, never null. + - - col_description returns the comment for a table column, - which is specified by the OID of its table and its column number. - obj_description cannot be used for table columns since - columns do not have OIDs of their own. - + +row_constructor IS NOT DISTINCT FROM row_constructor + - - The two-parameter form of obj_description returns the - comment for a database object specified by its OID and the name of the - containing system catalog. For example, - obj_description(123456,'pg_class') - would retrieve the comment for a table with OID 123456. - The one-parameter form of obj_description requires only - the object OID. It is now deprecated since there is no guarantee that - OIDs are unique across different system catalogs; therefore, the wrong - comment could be returned. - + + This construct is similar to a = row comparison, + but it does not yield null for null inputs. Instead, any null value is + considered unequal to (distinct from) any non-null value, and any two + nulls are considered equal (not distinct). Thus the result will always + be either true or false, never null. + - - shobj_description is used just like - obj_description only that it is used for retrieving - comments on shared objects. Some system catalogs are global to all - databases within each cluster and their descriptions are stored globally - as well. - - + + + + + Set Returning Functions - - System Administration Functions + + set returning functions + functions + + + + generate_series + - shows the functions - available to query and alter run-time configuration parameters. + This section describes functions that possibly return more than one row. + Currently the only functions in this class are series generating functions, + as detailed in . - - Configuration Settings Functions - - - Name Return Type Description - - - - - - current_setting(setting_name) - - text - current value of setting - - - - set_config(setting_name, - new_value, - is_local) - - text - set parameter and return new value - - - -
+ + Series Generating Functions + + + + Function + Argument Type + Return Type + Description + + - - SET - + + + generate_series(start, stop) + int or bigint + setof int or setof bigint (same as argument type) + + Generate a series of values, from start to stop + with a step size of one + + - - SHOW - + + generate_series(start, stop, step) + int or bigint + setof int or setof bigint (same as argument type) + + Generate a series of values, from start to stop + with a step size of step + + - - configuration - of the server - functions - + + +
- - The function current_setting yields the - current value of the setting setting_name. - It corresponds to the SQL command - SHOW. An example: + + When step is positive, zero rows are returned if + start is greater than stop. + Conversely, when step is negative, zero rows are + returned if start is less than stop. + Zero rows are also returned for NULL inputs. It is an error + for step to be zero. Some examples follow: -SELECT current_setting('datestyle'); +select * from generate_series(2,4); + generate_series +----------------- + 2 + 3 + 4 +(3 rows) - current_setting +select * from generate_series(5,1,-2); + generate_series ----------------- - ISO, MDY -(1 row) - - + 5 + 3 + 1 +(3 rows) - - set_config sets the parameter - setting_name to - new_value. If - is_local is true, the - new value will only apply to the current transaction. If you want - the new value to apply for the current session, use - false instead. The function corresponds to the - SQL command SET. An example: - -SELECT set_config('log_statement_stats', 'off', false); +select * from generate_series(4,3); + generate_series +----------------- +(0 rows) - set_config +select current_date + s.a as dates from generate_series(0,14,7) as s(a); + dates ------------ - off -(1 row) + 2004-02-05 + 2004-02-12 + 2004-02-19 +(3 rows) - - - - pg_cancel_backend - - - pg_reload_conf - - - pg_rotate_logfile - + + - - signal - backend processes - + + System Information Functions - - The functions shown in send control signals to - other server processes. Use of these functions is restricted - to superusers. - + + shows several + functions that extract session and system information. + - - Server Signalling Functions +
+ Session Information Functions - Name Return Type Description - + Name Return Type Description - - pg_cancel_backend(pid int) - - boolean - Cancel a backend's current query - - - - pg_reload_conf() - - boolean - Cause server processes to reload their configuration files - - - - pg_rotate_logfile() - - boolean - Rotate server's log file + current_database() + name + name of current database - - -
- - - Each of these functions returns true if - successful and false otherwise. - - - - pg_cancel_backend sends a query cancel - (SIGINT) signal to a backend process identified by - process ID. The process ID of an active backend can be found from - the procpid column in the - pg_stat_activity view, or by listing the - postgres processes on the server with - ps. - - - - pg_reload_conf sends a SIGHUP signal - to the server, causing the configuration files - to be reloaded by all server processes. - - - - pg_rotate_logfile signals the log-file manager to switch - to a new output file immediately. This works only when - redirect_stderr is used for logging, since otherwise there - is no log-file manager subprocess. - - - pg_start_backup - - - pg_stop_backup - - - pg_switch_xlog - - - pg_current_xlog_location - - - pg_current_xlog_insert_location - - - pg_xlogfile_name_offset - - - pg_xlogfile_name - - - backup - + + current_schema() + name + name of current schema + - - The functions shown in assist in making on-line backups. - Use of the first three functions is restricted to superusers. - + + current_schemas(boolean) + name[] + names of schemas in search path optionally including implicit schemas + - - Backup Control Functions - - - Name Return Type Description + + current_user + name + user name of current execution context - - - - pg_start_backup(label text) - - text - Set up for performing on-line backup + inet_client_addr() + inet + address of the remote connection + - - pg_stop_backup() - - text - Finish performing on-line backup + inet_client_port() + int + port of the remote connection + - - pg_switch_xlog() - - text - Force switch to a new transaction log file + inet_server_addr() + inet + address of the local connection + - - pg_current_xlog_location() - - text - Get current transaction log write location + inet_server_port() + int + port of the local connection + - - pg_current_xlog_insert_location() - - text - Get current transaction log insert location + pg_my_temp_schema() + oid + OID of session's temporary schema, or 0 if none + - - pg_xlogfile_name_offset(location text) - - text, integer - Convert transaction log location string to file name and decimal byte offset within file + pg_is_other_temp_schema(oid) + boolean + is schema another session's temporary schema? + - - pg_xlogfile_name(location text) - + pg_postmaster_start_time() + timestamp with time zone + server start time + + + + session_user + name + session user name + + + + user + name + equivalent to current_user + + + + version() text - Convert transaction log location string to file name + PostgreSQL version information
- - pg_start_backup accepts a single parameter which is an - arbitrary user-defined label for the backup. (Typically this would be - the name under which the backup dump file will be stored.) The function - writes a backup label file into the database cluster's data directory, - and then returns the backup's starting transaction log location as text. The user - need not pay any attention to this result value, but it is provided in - case it is of use. - -postgres=# select pg_start_backup('label_goes_here'); - pg_start_backup ------------------ - 0/D4445B8 -(1 row) - - + + user + current + + + + schema + current + + + + search path + current + - pg_stop_backup removes the label file created by - pg_start_backup, and instead creates a backup history file in - the transaction log archive area. The history file includes the label given to - pg_start_backup, the starting and ending transaction log locations for - the backup, and the starting and ending times of the backup. The return - value is the backup's ending transaction log location (which again might be of little - interest). After noting the ending location, the current transaction log insertion - point is automatically advanced to the next transaction log file, so that the - ending transaction log file can be archived immediately to complete the backup. + The session_user is normally the user who initiated + the current database connection; but superusers can change this setting + with . + The current_user is the user identifier + that is applicable for permission checking. Normally, it is equal + to the session user, but it can be changed with + . + It also changes during the execution of + functions with the attribute SECURITY DEFINER. + In Unix parlance, the session user is the real user and + the current user is the effective user. + + + current_user, session_user, and + user have special syntactic status in SQL: + they must be called without trailing parentheses. + + + - pg_switch_xlog moves to the next transaction log file, allowing the - current file to be archived (assuming you are using continuous archiving). - The result is the ending transaction log location within the just-completed transaction log file. - If there has been no transaction log activity since the last transaction log switch, - pg_switch_xlog does nothing and returns the end location - of the previous transaction log file. + current_schema returns the name of the schema that is + at the front of the search path (or a null value if the search path is + empty). This is the schema that will be used for any tables or + other named objects that are created without specifying a target schema. + current_schemas(boolean) returns an array of the names of all + schemas presently in the search path. The Boolean option determines whether or not + implicitly included system schemas such as pg_catalog are included in the search + path returned. + + + The search path can be altered at run time. The command is: + +SET search_path TO schema , schema, ... + + + + + + inet_client_addr + + + + inet_client_port + + + + inet_server_addr + + + + inet_server_port + + - pg_current_xlog_location displays the current transaction log write - location in the same format used by the above functions. Similarly - pg_current_xlog_insert_location displays the current transaction log - insertion point. The insertion point is the logical end of transaction log - at any instant, while the write location is the end of what has actually - been written out from the server's internal buffers. The write location - is the end of what can be examined from outside the server, and is usually - what you want if you are interested in archiving partially-complete transaction log - files. The insertion point is made available primarily for server - debugging purposes. These are both read-only operations and do not - require superuser permissions. + inet_client_addr returns the IP address of the + current client, and inet_client_port returns the + port number. + inet_server_addr returns the IP address on which + the server accepted the current connection, and + inet_server_port returns the port number. + All these functions return NULL if the current connection is via a + Unix-domain socket. + + pg_my_temp_schema + + + + pg_is_other_temp_schema + + - You can use pg_xlogfile_name_offset to extract the - corresponding transaction log file name and byte offset from the results of any of the - above functions. For example: - -postgres=# select * from pg_xlogfile_name_offset(pg_stop_backup()); - file_name | file_offset ---------------------------+------------- - 00000001000000000000000D | 4039624 -(1 row) - - Similarly, pg_xlogfile_name extracts just the transaction log file name. - When the given transction log location is exactly at an transaction log file boundary, both - these functions return the name of the preceding transaction log file. - This is usually the desired behavior for managing transaction log archiving - behavior, since the preceding file is the last one that currently - needs to be archived. + pg_my_temp_schema returns the OID of the current + session's temporary schema, or 0 if it has none (because it has not + created any temporary tables). + pg_is_other_temp_schema returns true if the + given OID is the OID of any other session's temporary schema. + (This can be useful, for example, to exclude other sessions' temporary + tables from a catalog display.) + + pg_postmaster_start_time + + - For details about proper usage of these functions, see - . + pg_postmaster_start_time returns the + timestamp with time zone when the + server started. + + version + + - The functions shown in calculate - the actual disk space usage of database objects. + version returns a string describing the + PostgreSQL server's version. - - pg_column_size - - - pg_database_size - - - pg_relation_size - - - pg_size_pretty - - - pg_tablespace_size - - - pg_total_relation_size - + + privilege + querying + - - Database Object Size Functions + + lists functions that + allow the user to query object access privileges programmatically. + See for more information about + privileges. + + +
+ Access Privilege Inquiry Functions - Name Return Type Description - + Name Return Type Description - pg_column_size(any) - int - Number of bytes used to store a particular value (possibly compressed) + has_database_privilege(user, + database, + privilege) + + boolean + does user have privilege for database - - pg_database_size(oid) - - bigint - Disk space used by the database with the specified OID + has_database_privilege(database, + privilege) + + boolean + does current user have privilege for database - - pg_database_size(name) - - bigint - Disk space used by the database with the specified name + has_function_privilege(user, + function, + privilege) + + boolean + does user have privilege for function - - pg_relation_size(oid) - - bigint - Disk space used by the table or index with the specified OID + has_function_privilege(function, + privilege) + + boolean + does current user have privilege for function - - pg_relation_size(text) - - bigint - - Disk space used by the table or index with the specified name. - The table name can be qualified with a schema name + has_language_privilege(user, + language, + privilege) + boolean + does user have privilege for language - - pg_size_pretty(bigint) - - text - Converts a size in bytes into a human-readable format with size units + has_language_privilege(language, + privilege) + + boolean + does current user have privilege for language - - pg_tablespace_size(oid) - - bigint - Disk space used by the tablespace with the specified OID + has_schema_privilege(user, + schema, + privilege) + + boolean + does user have privilege for schema - - pg_tablespace_size(name) - - bigint - Disk space used by the tablespace with the specified name + has_schema_privilege(schema, + privilege) + + boolean + does current user have privilege for schema - - pg_total_relation_size(oid) - - bigint - - Total disk space used by the table with the specified OID, - including indexes and toasted data + has_table_privilege(user, + table, + privilege) + boolean + does user have privilege for table - - pg_total_relation_size(text) - - bigint - - Total disk space used by the table with the specified name, - including indexes and toasted data. The table name can be - qualified with a schema name + has_table_privilege(table, + privilege) + + boolean + does current user have privilege for table + + + has_tablespace_privilege(user, + tablespace, + privilege) + + boolean + does user have privilege for tablespace + + + has_tablespace_privilege(tablespace, + privilege) + + boolean + does current user have privilege for tablespace + + + pg_has_role(user, + role, + privilege) + + boolean + does user have privilege for role + + + pg_has_role(role, + privilege) + boolean + does current user have privilege for role
+ + has_database_privilege + + + has_function_privilege + + + has_language_privilege + + + has_schema_privilege + + + has_table_privilege + + + has_tablespace_privilege + + + pg_has_role + + - pg_column_size shows the space used to store any individual - data value. + has_database_privilege checks whether a user + can access a database in a particular way. The possibilities for its + arguments are analogous to has_table_privilege. + The desired access privilege type must evaluate to + CREATE, + CONNECT, + TEMPORARY, or + TEMP (which is equivalent to + TEMPORARY). - pg_database_size and pg_tablespace_size - accept the OID or name of a database or tablespace, and return the total - disk space used therein. + has_function_privilege checks whether a user + can access a function in a particular way. The possibilities for its + arguments are analogous to has_table_privilege. + When specifying a function by a text string rather than by OID, + the allowed input is the same as for the regprocedure data type + (see ). + The desired access privilege type must evaluate to + EXECUTE. + An example is: + +SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); + + + + + has_language_privilege checks whether a user + can access a procedural language in a particular way. The possibilities + for its arguments are analogous to has_table_privilege. + The desired access privilege type must evaluate to + USAGE. + + + + has_schema_privilege checks whether a user + can access a schema in a particular way. The possibilities for its + arguments are analogous to has_table_privilege. + The desired access privilege type must evaluate to + CREATE or + USAGE. + + + + has_table_privilege checks whether a user + can access a table in a particular way. The user can be + specified by name or by OID + (pg_authid.oid), or if the argument is + omitted + current_user is assumed. The table can be specified + by name or by OID. (Thus, there are actually six variants of + has_table_privilege, which can be distinguished by + the number and types of their arguments.) When specifying by name, + the name can be schema-qualified if necessary. + The desired access privilege type + is specified by a text string, which must evaluate to one of the + values SELECT, INSERT, + UPDATE, DELETE, + REFERENCES, or TRIGGER. + (Case of the string is not significant, however.) + An example is: + +SELECT has_table_privilege('myschema.mytable', 'select'); + - pg_relation_size accepts the OID or name of a table, index or - toast table, and returns the size in bytes. + has_tablespace_privilege checks whether a user + can access a tablespace in a particular way. The possibilities for its + arguments are analogous to has_table_privilege. + The desired access privilege type must evaluate to + CREATE. - pg_size_pretty can be used to format the result of one of - the other functions in a human-readable way, using kB, MB, GB or TB as - appropriate. + pg_has_role checks whether a user + can access a role in a particular way. The possibilities for its + arguments are analogous to has_table_privilege. + The desired access privilege type must evaluate to + MEMBER or + USAGE. + MEMBER denotes direct or indirect membership in + the role (that is, the right to do SET ROLE), while + USAGE denotes whether the privileges of the role + are immediately available without doing SET ROLE. - - pg_total_relation_size accepts the OID or name of a - table or toast table, and returns the size in bytes of the data - and all associated indexes and toast tables. - + + To test whether a user holds a grant option on the privilege, + append WITH GRANT OPTION to the privilege key + word; for example 'UPDATE WITH GRANT OPTION'. + - - The functions shown in provide native file access to - files on the machine hosting the server. Only files within the - database cluster directory and the log_directory can be - accessed. Use a relative path for files within the cluster directory, - and a path matching the log_directory configuration setting - for log files. Use of these functions is restricted to superusers. - + + shows functions that + determine whether a certain object is visible in the + current schema search path. A table is said to be visible if its + containing schema is in the search path and no table of the same + name appears earlier in the search path. This is equivalent to the + statement that the table can be referenced by name without explicit + schema qualification. For example, to list the names of all + visible tables: + +SELECT relname FROM pg_class WHERE pg_table_is_visible(oid); + + - - Generic File Access Functions +
+ Schema Visibility Inquiry Functions - Name Return Type Description - + Name Return Type Description - - pg_ls_dir(dirname text) + pg_conversion_is_visible(conversion_oid) - setof text - List the contents of a directory + boolean + is conversion visible in search path - - pg_read_file(filename text, offset bigint, length bigint) + pg_function_is_visible(function_oid) - text - Return the contents of a text file + boolean + is function visible in search path - - pg_stat_file(filename text) + pg_operator_is_visible(operator_oid) - record - Return information about a file + boolean + is operator visible in search path + + + pg_opclass_is_visible(opclass_oid) + + boolean + is operator class visible in search path + + + pg_table_is_visible(table_oid) + + boolean + is table visible in search path + + + pg_type_is_visible(type_oid) + + boolean + is type (or domain) visible in search path
- - pg_ls_dir + + pg_conversion_is_visible - - pg_ls_dir returns all the names in the specified - directory, except the special entries . and - ... - - - - pg_read_file + + pg_function_is_visible + + + pg_operator_is_visible + + + pg_opclass_is_visible + + + pg_table_is_visible + + pg_type_is_visible + + - pg_read_file returns part of a text file, starting - at the given offset, returning at most length - bytes (less if the end of file is reached first). If offset - is negative, it is relative to the end of the file. + pg_conversion_is_visible, + pg_function_is_visible, + pg_operator_is_visible, + pg_opclass_is_visible, + pg_table_is_visible, and + pg_type_is_visible perform the visibility check for + conversions, functions, operators, operator classes, tables, and + types. Note that pg_table_is_visible can also be used + with views, indexes and sequences; pg_type_is_visible + can also be used with domains. For functions and operators, an object in + the search path is visible if there is no object of the same name + and argument data type(s) earlier in the path. For operator + classes, both name and associated index access method are considered. - - pg_stat_file - - pg_stat_file returns a record containing the file - size, last accessed time stamp, last modified time stamp, - last file status change time stamp (Unix platforms only), - file creation time stamp (Windows only), and a boolean - indicating if it is a directory. Typical usages include: + All these functions require object OIDs to identify the object to be + checked. If you want to test an object by name, it is convenient to use + the OID alias types (regclass, regtype, + regprocedure, or regoperator), for example: -SELECT * FROM pg_stat_file('filename'); -SELECT (pg_stat_file('filename')).modification; +SELECT pg_type_is_visible('myschema.widget'::regtype); + Note that it would not make much sense to test an unqualified name in + this way — if the name can be recognized at all, it must be visible. - - The functions shown in manage - advisory locks. For details about proper usage of these functions, see - . - + + format_type + - - Advisory Lock Functions + + pg_get_viewdef + + + + pg_get_ruledef + + + + pg_get_indexdef + + + + pg_get_triggerdef + + + + pg_get_constraintdef + + + + pg_get_expr + + + + pg_get_userbyid + + + + pg_get_serial_sequence + + + + pg_tablespace_databases + + + + lists functions that + extract information from the system catalogs. + + +
+ System Catalog Information Functions - Name Return Type Description - + Name Return Type Description - - pg_advisory_lock(key bigint) - - void - Obtain exclusive advisory lock + format_type(type_oid, typemod) + text + get SQL name of a data type + + + pg_get_constraintdef(constraint_oid) + text + get definition of a constraint + + + pg_get_constraintdef(constraint_oid, pretty_bool) + text + get definition of a constraint + + + pg_get_expr(expr_text, relation_oid) + text + decompile internal form of an expression, assuming that any Vars + in it refer to the relation indicated by the second parameter + + + pg_get_expr(expr_text, relation_oid, pretty_bool) + text + decompile internal form of an expression, assuming that any Vars + in it refer to the relation indicated by the second parameter - - pg_advisory_lock(key1 int, key2 int) - - void - Obtain exclusive advisory lock + pg_get_indexdef(index_oid) + text + get CREATE INDEX command for index - - - pg_advisory_lock_shared(key bigint) - - void - Obtain shared advisory lock + pg_get_indexdef(index_oid, column_no, pretty_bool) + text + get CREATE INDEX command for index, + or definition of just one index column when + column_no is not zero - - pg_advisory_lock_shared(key1 int, key2 int) - - void - Obtain shared advisory lock + pg_get_ruledef(rule_oid) + text + get CREATE RULE command for rule - - - pg_try_advisory_lock(key bigint) - - boolean - Obtain exclusive advisory lock if available + pg_get_ruledef(rule_oid, pretty_bool) + text + get CREATE RULE command for rule - - pg_try_advisory_lock(key1 int, key2 int) - - boolean - Obtain exclusive advisory lock if available + pg_get_serial_sequence(table_name, column_name) + text + get name of the sequence that a serial or bigserial column + uses - - - pg_try_advisory_lock_shared(key bigint) - - boolean - Obtain shared advisory lock if available + pg_get_triggerdef(trigger_oid) + text + get CREATE [ CONSTRAINT ] TRIGGER command for trigger - - pg_try_advisory_lock_shared(key1 int, key2 int) - - boolean - Obtain shared advisory lock if available + pg_get_userbyid(roleid) + name + get role name with given ID - - - pg_advisory_unlock(key bigint) - - boolean - Release an exclusive advisory lock + pg_get_viewdef(view_name) + text + get underlying SELECT command for view (deprecated) - - pg_advisory_unlock(key1 int, key2 int) - - boolean - Release an exclusive advisory lock + pg_get_viewdef(view_name, pretty_bool) + text + get underlying SELECT command for view (deprecated) - - - pg_advisory_unlock_shared(key bigint) - - boolean - Release a shared advisory lock + pg_get_viewdef(view_oid) + text + get underlying SELECT command for view - - pg_advisory_unlock_shared(key1 int, key2 int) - - boolean - Release a shared advisory lock + pg_get_viewdef(view_oid, pretty_bool) + text + get underlying SELECT command for view - - - pg_advisory_unlock_all() - - void - Release all advisory locks held by the current session + pg_tablespace_databases(tablespace_oid) + setof oid + get the set of database OIDs that have objects in the tablespace -
- - pg_advisory_lock - - - pg_advisory_lock locks an application-defined resource, - which can be identified either by a single 64-bit key value or two - 32-bit key values (note that these two key spaces do not overlap). If - another session already holds a lock on the same resource, the - function will wait until the resource becomes available. The lock - is exclusive. Multiple lock requests stack, so that if the same resource - is locked three times it must be also unlocked three times to be - released for other sessions' use. - + + format_type returns the SQL name of a data type that + is identified by its type OID and possibly a type modifier. Pass NULL + for the type modifier if no specific modifier is known. + - - pg_advisory_lock_shared + + pg_get_constraintdef, + pg_get_indexdef, pg_get_ruledef, + and pg_get_triggerdef, respectively reconstruct the + creating command for a constraint, index, rule, or trigger. (Note that this + is a decompiled reconstruction, not the original text of the command.) + pg_get_expr decompiles the internal form of an + individual expression, such as the default value for a column. It can be + useful when examining the contents of system catalogs. + pg_get_viewdef reconstructs the SELECT + query that defines a view. Most of these functions come in two variants, + one of which can optionally pretty-print the result. The + pretty-printed format is more readable, but the default format is more + likely to be interpreted the same way by future versions of + PostgreSQL; avoid using pretty-printed output for dump + purposes. Passing false for the pretty-print parameter yields + the same result as the variant that does not have the parameter at all. + + + + pg_get_serial_sequence returns the name of the + sequence associated with a column, or NULL if no sequence is associated + with the column. The first input parameter is a table name with + optional schema, and the second parameter is a column name. Because + the first parameter is potentially a schema and table, it is not treated + as a double-quoted identifier, meaning it is lowercased by default, + while the second parameter, being just a column name, is treated as + double-quoted and has its case preserved. The function returns a value + suitably formatted for passing to the sequence functions (see ). This association can be modified or + removed with ALTER SEQUENCE OWNED BY. (The function + probably should have been called + pg_get_owned_sequence; its name reflects the fact + that it's typically used with serial or bigserial + columns.) + + + + pg_get_userbyid extracts a role's name given + its OID. + + + + pg_tablespace_databases allows a tablespace to be + examined. It returns the set of OIDs of databases that have objects stored + in the tablespace. If this function returns any rows, the tablespace is not + empty and cannot be dropped. To display the specific objects populating the + tablespace, you will need to connect to the databases identified by + pg_tablespace_databases and query their + pg_class catalogs. + + + + col_description - - pg_advisory_lock_shared works the same as - pg_advisory_lock, - except the lock can be shared with other sessions requesting shared locks. - Only would-be exclusive lockers are locked out. - - - pg_try_advisory_lock + + obj_description - - pg_try_advisory_lock is similar to - pg_advisory_lock, except the function will not wait for the - lock to become available. It will either obtain the lock immediately and - return true, or return false if the lock cannot be - acquired now. - - - pg_try_advisory_lock_shared + + shobj_description - - pg_try_advisory_lock_shared works the same as - pg_try_advisory_lock, except it attempts to acquire - shared rather than exclusive lock. - - - pg_advisory_unlock + + comment + about database objects + - pg_advisory_unlock will release a previously-acquired - exclusive advisory lock. It - will return true if the lock is successfully released. - If the lock was in fact not held, it will return false, - and in addition, an SQL warning will be raised by the server. + The functions shown in + extract comments previously stored with the command. A null value is returned if no + comment could be found matching the specified parameters. - - pg_advisory_unlock_shared - + + Comment Information Functions + + + Name Return Type Description + + + + + col_description(table_oid, column_number) + text + get comment for a table column + + + obj_description(object_oid, catalog_name) + text + get comment for a database object + + + obj_description(object_oid) + text + get comment for a database object (deprecated) + + + shobj_description(object_oid, catalog_name) + text + get comment for a shared database object + + + +
+ - pg_advisory_unlock_shared works the same as - pg_advisory_unlock, - except to release a shared advisory lock. + col_description returns the comment for a table column, + which is specified by the OID of its table and its column number. + obj_description cannot be used for table columns since + columns do not have OIDs of their own. - - pg_advisory_unlock_all - - pg_advisory_unlock_all will release all advisory locks - held by the current session. (This function is implicitly invoked - at session end, even if the client disconnects ungracefully.) + The two-parameter form of obj_description returns the + comment for a database object specified by its OID and the name of the + containing system catalog. For example, + obj_description(123456,'pg_class') + would retrieve the comment for a table with OID 123456. + The one-parameter form of obj_description requires only + the object OID. It is now deprecated since there is no guarantee that + OIDs are unique across different system catalogs; therefore, the wrong + comment could be returned. + + shobj_description is used just like + obj_description only that it is used for retrieving + comments on shared objects. Some system catalogs are global to all + databases within each cluster and their descriptions are stored globally + as well. + - - XML Functions + + System Administration Functions - The functions and function-like expressions described in this - section operate on values of type xml. Check for information about the xml - type. The function-like expressions xmlparse - and xmlserialize for converting to and from - type xml are not repeated here. + shows the functions + available to query and alter run-time configuration parameters. - - Producing XML Content - - - A set of functions and function-like expressions are available for - producing XML content from SQL data. As such, they are - particularly suitable for formatting query results into XML - documents for processing in client applications. - - - - <literal>xmlcomment</literal> - - - xmlcomment - - - -xmlcomment(text) - - - - The function xmlcomment creates an XML value - containing an XML comment with the specified text as content. - The text cannot contain -- or end with a - - so that the resulting construct is a valid - XML comment. If the argument is null, the result is null. - - - - Example: - -]]> - - - - - <literal>xmlconcat</literal> - - - xmlconcat - - - - xmlconcat(xml, ...) - - - - The function xmlconcat concatenates a list - of individual XML values to create a single value containing an - XML content fragment. Null values are omitted; the result is - only null if there are no nonnull arguments. - - - - Example: -', 'foo'); - - xmlconcat ----------------------- - foo -]]> - - - - XML declarations, if present are combined as follows. If all - argument values have the same XML version declaration, that - version is used in the result, else no version is used. If all - argument values have the standalone declaration value - yes, then that value is used in the result. If - all argument values have a standalone declaration value and at - least one is no, then that is used in the result. - Else the result will have no standalone declaration. If the - result is determined to require a standalone declaration but no - version declaration, a version declaration with version 1.0 will - be used because XML requires an XML declaration to contain a - version declaration. Encoding declarations are ignored and - removed in all cases. - + + Configuration Settings Functions + + + Name Return Type Description + - - Example: -', ''); + + + + current_setting(setting_name) + + text + current value of setting + + + + set_config(setting_name, + new_value, + is_local) + + text + set parameter and return new value + + + +
- xmlconcat ------------------------------------ - -]]> - - - - - <literal>xmlelement</literal> - - - xmlelement + + SET - - - xmlelement(name name , xmlattributes(value AS attname , ... ) , content, ...) - - - - The xmlelement expression produces an XML - element with the given name, attributes, and content. - - - - Examples: - -SELECT xmlelement(name foo, xmlattributes('xyz' as bar)); - - xmlelement ------------------- - - -SELECT xmlelement(name foo, xmlattributes(current_date as bar), 'cont', 'ent'); + + SHOW + - xmlelement -------------------------------------- - content -]]> - + + configuration + of the server + functions + - - Element and attribute names that are not valid XML names are - escaped by replacing the offending characters by the sequence - _xHHHH_, where - HHHH is the character's Unicode - codepoint in hexadecimal notation. For example: - + The function current_setting yields the + current value of the setting setting_name. + It corresponds to the SQL command + SHOW. An example: + +SELECT current_setting('datestyle'); - xmlelement ----------------------------------- - -]]> - + current_setting +----------------- + ISO, MDY +(1 row) + + - - An explicit attribute name need not be specified if the attribute - value is a column reference, in which case the column's name will - be used as attribute name by default. In any other case, the - attribute must be given an explicit name. So this example is - valid: - -CREATE TABLE test (a xml, b xml); -SELECT xmlelement(name test, xmlattributes(a, b)) FROM test; - - But these are not: - -SELECT xmlelement(name test, xmlattributes('constant'), a, b) FROM test; -SELECT xmlelement(name test, xmlattributes(func(a, b))) FROM test; - - + + set_config sets the parameter + setting_name to + new_value. If + is_local is true, the + new value will only apply to the current transaction. If you want + the new value to apply for the current session, use + false instead. The function corresponds to the + SQL command SET. An example: + +SELECT set_config('log_statement_stats', 'off', false); - - Element content, if specified, will be formatted according to - data type. If the content is itself of type xml, - complex XML documents can be constructed. For example: - + - xmlelement ----------------------------------------------- - -]]> + + pg_cancel_backend + + + pg_reload_conf + + + pg_rotate_logfile + - Content of other types will be formatted into valid XML character - data. This means in particular that the characters <, >, - and & will be converted to entities. Binary data (data type - bytea) will be represented in base64 or hex - encoding, depending on the setting of the configuration parameter - . The particular behavior for - individual data types is expected evolve in order to align the - SQL and PostgreSQL data types with the XML Schema specification, - at which point a more precise description will appear. - - - - - <literal>xmlforest</literal> - - - xmlforest + + signal + backend processes - - - xmlforest(content AS name , ...) - - - - The xmlforest expression produces an XML - forest (sequence) of elements using the given names and content. - - - Examples: - + The functions shown in send control signals to + other server processes. Use of these functions is restricted + to superusers. + - xmlforest ------------------------------- - abc123 + + Server Signalling Functions + + + Name Return Type Description + + + + + + pg_cancel_backend(pid int) + + boolean + Cancel a backend's current query + + + + pg_reload_conf() + + boolean + Cause server processes to reload their configuration files + + + + pg_rotate_logfile() + + boolean + Rotate server's log file + + + +
-SELECT xmlforest(table_name, column_name) FROM information_schema.columns WHERE table_schema = 'pg_catalog'; + + Each of these functions returns true if + successful and false otherwise. + - xmlforest -------------------------------------------------------------------------------------------- - pg_authidrolname - pg_authidrolsuper - ... -]]> + + pg_cancel_backend sends a query cancel + (SIGINT) signal to a backend process identified by + process ID. The process ID of an active backend can be found from + the procpid column in the + pg_stat_activity view, or by listing the + postgres processes on the server with + ps. + - As seen in the second example, the element name can be omitted if - the content value is a column reference, in which case the column - name is used by default. Otherwise, a name must be specified. - + + pg_reload_conf sends a SIGHUP signal + to the server, causing the configuration files + to be reloaded by all server processes. + - - Element names that are not valid XML names are escaped as shown - for xmlelement above. Similarly, content - data is escaped to make valid XML content, unless it is already - of type xml. - + + pg_rotate_logfile signals the log-file manager to switch + to a new output file immediately. This works only when + redirect_stderr is used for logging, since otherwise there + is no log-file manager subprocess. + - - Note that XML forests are not valid XML documents if they consist - of more than one element. So it might be useful to wrap - xmlforest expressions in - xmlelement. - - - - - <literal>xmlpi</literal> - - - xmlpi + + pg_start_backup - - - xmlpi(name target , content) - - - - The xmlpi expression creates an XML - processing instruction. The content, if present, must not - contain the character sequence ?>. - + + pg_stop_backup + + + pg_switch_xlog + + + pg_current_xlog_location + + + pg_current_xlog_insert_location + + + pg_xlogfile_name_offset + + + pg_xlogfile_name + + + backup + + + + The functions shown in assist in making on-line backups. + Use of the first three functions is restricted to superusers. + + + + Backup Control Functions + + + Name Return Type Description + + + + + + + pg_start_backup(label text) + + text + Set up for performing on-line backup + + + + pg_stop_backup() + + text + Finish performing on-line backup + + + + pg_switch_xlog() + + text + Force switch to a new transaction log file + + + + pg_current_xlog_location() + + text + Get current transaction log write location + + + + pg_current_xlog_insert_location() + + text + Get current transaction log insert location + + + + pg_xlogfile_name_offset(location text) + + text, integer + Convert transaction log location string to file name and decimal byte offset within file + + + + pg_xlogfile_name(location text) + + text + Convert transaction log location string to file name + + + +
- - Example: - + pg_start_backup accepts a single parameter which is an + arbitrary user-defined label for the backup. (Typically this would be + the name under which the backup dump file will be stored.) The function + writes a backup label file into the database cluster's data directory, + and then returns the backup's starting transaction log location as text. The user + need not pay any attention to this result value, but it is provided in + case it is of use. + +postgres=# select pg_start_backup('label_goes_here'); + pg_start_backup +----------------- + 0/D4445B8 +(1 row) + + - xmlpi ------------------------------ - -]]> - - - - - <literal>xmlroot</literal> - - - xmlroot - - - - xmlroot(xml, version text|no value , standalone yes|no|no value) - - - - The xmlroot expression alters the properties - of the root node of an XML value. If a version is specified, - this replaces the value in the version declaration, if a - standalone value is specified, this replaces the value in the - standalone declaration. - + + pg_stop_backup removes the label file created by + pg_start_backup, and instead creates a backup history file in + the transaction log archive area. The history file includes the label given to + pg_start_backup, the starting and ending transaction log locations for + the backup, and the starting and ending times of the backup. The return + value is the backup's ending transaction log location (which again might be of little + interest). After noting the ending location, the current transaction log insertion + point is automatically advanced to the next transaction log file, so that the + ending transaction log file can be archived immediately to complete the backup. + - -abc'), version '1.0', standalone yes); + + pg_switch_xlog moves to the next transaction log file, allowing the + current file to be archived (assuming you are using continuous archiving). + The result is the ending transaction log location within the just-completed transaction log file. + If there has been no transaction log activity since the last transaction log switch, + pg_switch_xlog does nothing and returns the end location + of the previous transaction log file. + - xmlroot ----------------------------------------- - - abc -]]> - - + + pg_current_xlog_location displays the current transaction log write + location in the same format used by the above functions. Similarly + pg_current_xlog_insert_location displays the current transaction log + insertion point. The insertion point is the logical end of transaction log + at any instant, while the write location is the end of what has actually + been written out from the server's internal buffers. The write location + is the end of what can be examined from outside the server, and is usually + what you want if you are interested in archiving partially-complete transaction log + files. The insertion point is made available primarily for server + debugging purposes. These are both read-only operations and do not + require superuser permissions. + - - XML Predicates + + You can use pg_xlogfile_name_offset to extract the + corresponding transaction log file name and byte offset from the results of any of the + above functions. For example: + +postgres=# select * from pg_xlogfile_name_offset(pg_stop_backup()); + file_name | file_offset +--------------------------+------------- + 00000001000000000000000D | 4039624 +(1 row) + + Similarly, pg_xlogfile_name extracts just the transaction log file name. + When the given transction log location is exactly at an transaction log file boundary, both + these functions return the name of the preceding transaction log file. + This is usually the desired behavior for managing transaction log archiving + behavior, since the preceding file is the last one that currently + needs to be archived. + - - IS DOCUMENT - + + For details about proper usage of these functions, see + . + - -xml IS DOCUMENT - + + The functions shown in calculate + the actual disk space usage of database objects. + - - The expression IS DOCUMENT returns true if the - argument XML value is a proper XML document, false if it is not - (that is, it is a content fragment), or null if the argument is - null. See about the difference - between documents and content fragments. - - - + + pg_column_size + + + pg_database_size + + + pg_relation_size + + + pg_size_pretty + + + pg_tablespace_size + + + pg_total_relation_size + - - Mapping Tables to XML + + Database Object Size Functions + + + Name Return Type Description + + - - The following functions map the contents of relational tables to - XML values. They can be thought of as XML export functionality. - -table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text) -query_to_xml(query text, nulls boolean, tableforest boolean, targetns text) -cursor_to_xml(cursor refcursor, count int, nulls boolean, tableforest boolean, targetns text) - - The return type of each function is xml. + + + pg_column_size(any) + int + Number of bytes used to store a particular value (possibly compressed) + + + + pg_database_size(oid) + + bigint + Disk space used by the database with the specified OID + + + + pg_database_size(name) + + bigint + Disk space used by the database with the specified name + + + + pg_relation_size(oid) + + bigint + Disk space used by the table or index with the specified OID + + + + pg_relation_size(text) + + bigint + + Disk space used by the table or index with the specified name. + The table name can be qualified with a schema name + + + + + pg_size_pretty(bigint) + + text + Converts a size in bytes into a human-readable format with size units + + + + pg_tablespace_size(oid) + + bigint + Disk space used by the tablespace with the specified OID + + + + pg_tablespace_size(name) + + bigint + Disk space used by the tablespace with the specified name + + + + pg_total_relation_size(oid) + + bigint + + Total disk space used by the table with the specified OID, + including indexes and toasted data + + + + + pg_total_relation_size(text) + + bigint + + Total disk space used by the table with the specified name, + including indexes and toasted data. The table name can be + qualified with a schema name + + + + +
+ + + pg_column_size shows the space used to store any individual + data value. - table_to_xml maps the content of the named - table, passed as parameter tbl. The - regclass accepts strings identifying tables using the - usual notation, including optional schema qualifications and - double quotes. query_to_xml executes the - query whose text is passed as parameter - query and maps the result set. - cursor_to_xml fetches the indicated number of - rows from the cursor specified by the parameter - cursor. This variant is recommendable if - large tables have to be mapped, because the result value is built - up in memory by each function. + pg_database_size and pg_tablespace_size + accept the OID or name of a database or tablespace, and return the total + disk space used therein. - If tableforest is false, then the resulting - XML document looks like this: - - - data - data - + pg_relation_size accepts the OID or name of a table, index or + toast table, and returns the size in bytes. + - - ... - + + pg_size_pretty can be used to format the result of one of + the other functions in a human-readable way, using kB, MB, GB or TB as + appropriate. + - ... - -]]> + + pg_total_relation_size accepts the OID or name of a + table or toast table, and returns the size in bytes of the data + and all associated indexes and toast tables. + - If tableforest is true, the result is an - XML content fragment that looks like this: - - data - data - + + The functions shown in provide native file access to + files on the machine hosting the server. Only files within the + database cluster directory and the log_directory can be + accessed. Use a relative path for files within the cluster directory, + and a path matching the log_directory configuration setting + for log files. Use of these functions is restricted to superusers. + - - ... - + + Generic File Access Functions + + + Name Return Type Description + + -... -]]> + + + + pg_ls_dir(dirname text) + + setof text + List the contents of a directory + + + + pg_read_file(filename text, offset bigint, length bigint) + + text + Return the contents of a text file + + + + pg_stat_file(filename text) + + record + Return information about a file + + + +
- If no table name is avaible, that is, when mapping a query or a - cursor, the string table is used in the first - format, row in the second format. + + pg_ls_dir + + + pg_ls_dir returns all the names in the specified + directory, except the special entries . and + ... + + pg_read_file + - The choice between these formats is up to the user. The first - format is a proper XML document, which will be important in many - applications. The second format tends to be more useful in the - cursor_to_xml function if the result values are to be - reassembled into one document later on. The functions for - producing XML content discussed above, in particular - xmlelement, can be used to alter the results - to taste. + pg_read_file returns part of a text file, starting + at the given offset, returning at most length + bytes (less if the end of file is reached first). If offset + is negative, it is relative to the end of the file. + + pg_stat_file + - The data values are mapping in the same way as described for the - function xmlelement above. + pg_stat_file returns a record containing the file + size, last accessed time stamp, last modified time stamp, + last file status change time stamp (Unix platforms only), + file creation time stamp (Windows only), and a boolean + indicating if it is a directory. Typical usages include: + +SELECT * FROM pg_stat_file('filename'); +SELECT (pg_stat_file('filename')).modification; + - The parameter nulls determines whether null - values should be included in the output. If true, null values in - columns are represented as - -]]> - where xsi is the XML namespace prefix for XML - Schema Instance. An appropriate namespace declaration will be - added to the result value. If false, columns containing null - values are simply omitted from the output. + The functions shown in manage + advisory locks. For details about proper usage of these functions, see + . - - The parameter targetns specifies the - desired XML namespace of the result. If no particular namespace - is wanted, an empty string should be passed. - + + Advisory Lock Functions + + + Name Return Type Description + + + + + + + pg_advisory_lock(key bigint) + + void + Obtain exclusive advisory lock + + + + pg_advisory_lock(key1 int, key2 int) + + void + Obtain exclusive advisory lock + + + + + pg_advisory_lock_shared(key bigint) + + void + Obtain shared advisory lock + + + + pg_advisory_lock_shared(key1 int, key2 int) + + void + Obtain shared advisory lock + + + + + pg_try_advisory_lock(key bigint) + + boolean + Obtain exclusive advisory lock if available + + + + pg_try_advisory_lock(key1 int, key2 int) + + boolean + Obtain exclusive advisory lock if available + + + + + pg_try_advisory_lock_shared(key bigint) + + boolean + Obtain shared advisory lock if available + + + + pg_try_advisory_lock_shared(key1 int, key2 int) + + boolean + Obtain shared advisory lock if available + + + + + pg_advisory_unlock(key bigint) + + boolean + Release an exclusive advisory lock + + + + pg_advisory_unlock(key1 int, key2 int) + + boolean + Release an exclusive advisory lock + + + + + pg_advisory_unlock_shared(key bigint) + + boolean + Release a shared advisory lock + + + + pg_advisory_unlock_shared(key1 int, key2 int) + + boolean + Release a shared advisory lock + + + + + pg_advisory_unlock_all() + + void + Release all advisory locks held by the current session + + + + +
+ + pg_advisory_lock + - The following functions return XML Schema documents describing the - mappings made by the data mappings produced by the corresponding - functions above. - -table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text) -query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text) -cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text) - - It is essential that the same parameters are passed in order to - obtain matching XML data mappings and XML Schema documents. + pg_advisory_lock locks an application-defined resource, + which can be identified either by a single 64-bit key value or two + 32-bit key values (note that these two key spaces do not overlap). If + another session already holds a lock on the same resource, the + function will wait until the resource becomes available. The lock + is exclusive. Multiple lock requests stack, so that if the same resource + is locked three times it must be also unlocked three times to be + released for other sessions' use. + + pg_advisory_lock_shared + - The following functions produce XML data mappings and the - corresponding XML Schema in one document (or forest), linked - together. They can be useful where self-contained and - self-describing results are wanted. - -table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text) -query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text) - + pg_advisory_lock_shared works the same as + pg_advisory_lock, + except the lock can be shared with other sessions requesting shared locks. + Only would-be exclusive lockers are locked out. + + pg_try_advisory_lock + - In addition, the following functions are available to produce - analogous mappings of entire schemas or the entire current - database. - -schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text) -schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text) -schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text) - -database_to_xml(nulls boolean, tableforest boolean, targetns text) -database_to_xmlschema(nulls boolean, tableforest boolean, targetns text) -database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text) - - - Note that these potentially produce a lot of data, which needs to - be built up in memory. When requesting content mappings of large - schemas or databases, it may be worthwhile to consider mapping the - tables separately instead, possibly even through a cursor. + pg_try_advisory_lock is similar to + pg_advisory_lock, except the function will not wait for the + lock to become available. It will either obtain the lock immediately and + return true, or return false if the lock cannot be + acquired now. + + pg_try_advisory_lock_shared + - The result of a schema content mapping looks like this: - - - -table1-mapping - -table2-mapping - -... - -]]> - - where the format of a table mapping depends on the - tableforest parameter as explained above. + pg_try_advisory_lock_shared works the same as + pg_try_advisory_lock, except it attempts to acquire + shared rather than exclusive lock. + + pg_advisory_unlock + - The result of a database content mapping looks like this: - - - - - ... - - - - ... - - -... - -]]> - - where the schema mapping is as above. + pg_advisory_unlock will release a previously-acquired + exclusive advisory lock. It + will return true if the lock is successfully released. + If the lock was in fact not held, it will return false, + and in addition, an SQL warning will be raised by the server. + + pg_advisory_unlock_shared + - As an example for using the output produced by these functions, - shows an XSLT stylesheet that - converts the output of - table_to_xml_and_xmlschema to an HTML - document containing a tabular rendition of the table data. In a - similar manner, the result data of these functions can be - converted into other XML-based formats. + pg_advisory_unlock_shared works the same as + pg_advisory_unlock, + except to release a shared advisory lock. -
- XSLT stylesheet for converting SQL/XML output to HTML - - - - - - - - - - - - - <xsl:value-of select="name(current())"/> - - - - - - - - - - - - - - - - -
- - - - - -]]> -
- - - - Processing XML - + + pg_advisory_unlock_all + - XML support is not just the existence of an - xml data type, but a variety of features supported by - a database system. These capabilities include import/export, - indexing, searching, transforming, and XML to - SQL mapping. PostgreSQL supports some - but not all of these XML capabilities. For an - overview of XML use in databases, see . + pg_advisory_unlock_all will release all advisory locks + held by the current session. (This function is implicitly invoked + at session end, even if the client disconnects ungracefully.) - - - Indexing - - - - contrib/xml2/ functions can be used in expression - indexes to index specific XML fields. To index the - full contents of XML documents, the full-text - indexing tool contrib/tsearch2/ can be used. Of - course, Tsearch2 indexes have no XML awareness so - additional contrib/xml2/ checks should be added to - queries. - - - - - - Searching - - - - XPath searches are implemented using contrib/xml2/. - It processes XML text documents and returns results - based on the requested query. - - - - - - Transforming - - - - contrib/xml2/ supports XSLT (Extensible - Stylesheet Language Transformation). - - - - - - XML to SQL Mapping - + - - This involves converting XML data to and from - relational structures. PostgreSQL has no - internal support for such mapping, and relies on external tools - to do such conversions. - - - - - - -- 2.40.0