Search PostgreSQL documentation

Set Returning FunctionsManual ChaptersPG189.26. Set Returning Functions This section describes functions that possibly return more than one row. The most widely used functions in this class are series generating functions, as detail…Set Returning Functions SET ROLEManual ChaptersPG18SET ROLE SET ROLE — set the current user identifier of the current session Synopsis SET [ SESSION | LOCAL ] ROLE role_name SET [ SESSION | LOCAL ] ROLE NONE RESET ROLESET ROLE SET SESSION AUTHORIZATIONManual ChaptersPG18SET SESSION AUTHORIZATION SET SESSION AUTHORIZATION — set the session user identifier and the current user identifier of the current session Synopsis SET [ SESSION | LOCAL ] SESSION AUTHORIZ…SET SESSION AUTHORIZATION SET TRANSACTIONManual ChaptersPG18SET TRANSACTION SET TRANSACTION — set the characteristics of the current transaction Synopsis SET TRANSACTION transaction_mode [, ...] SET TRANSACTION SNAPSHOT snapshot_id SET SESSION CHARAC…SET TRANSACTION Set-Returning FunctionsManual ChaptersPG1844.2.5. Set-Returning Functions A PL/Python function can also return sets of scalar or composite types. There are several ways to achieve this because the returned object is internally turne…Set-Returning Functions Setting CallbacksManual ChaptersPG1834.8.1. Setting Callbacks One simple method to catch errors and warnings is to set a specific action to be executed whenever a particular condition occurs. In general: EXEC SQL WHENEVER cond…Setting Callbacks Setting ParametersManual ChaptersPG1819.1. Setting ParametersSetting Parameters Setting the Character SetManual ChaptersPG1823.3.2. Setting the Character Set initdb defines the default character set (encoding) for a PostgreSQL cluster. For example, initdb -E EUC_JP sets the default character set to EUC_JP (Extend…Setting the Character Set Setting Up a Standby ServerManual ChaptersPG1826.2.4. Setting Up a Standby Server To set up the standby server, restore the base backup taken from primary server (see Section 25.3.5). Create a file standby.signal in the standby's cluste…Setting Up a Standby Server Setting Up WAL ArchivingManual ChaptersPG1825.3.1. Setting Up WAL Archiving In an abstract sense, a running PostgreSQL system produces an indefinitely long sequence of WAL records. The system physically divides this sequence into WAL…Setting Up WAL Archiving SettingsManual ChaptersPG1819.5.1. Settings wal_level (enum) wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replica…Settings Shared LibrariesManual ChaptersPG1817.5.1. Shared Libraries On some systems with shared libraries you need to tell the system how to find the newly installed shared libraries. The systems on which this is not necessary includ…Shared Libraries Shared Library PreloadingManual ChaptersPG1819.11.3. Shared Library Preloading Several settings are available for preloading shared libraries into the server, in order to load additional functionality or achieve performance benefits. …Shared Library Preloading Shared MemoryManual ChaptersPG1836.10.11. Shared MemoryShared Memory Shared Memory and SemaphoresManual ChaptersPG1818.4.1. Shared Memory and Semaphores PostgreSQL requires the operating system to provide inter-process communication (IPC) features, specifically shared memory and semaphores. Unix-derived s…Shared Memory and Semaphores Sharing DataManual ChaptersPG1844.3. Sharing Data The global dictionary SD is available to store private data between repeated calls to the same function. The global dictionary GD is public data, that is available to all …Sharing Data Short OptionsManual ChaptersPG1819.18. Short Options For convenience there are also single letter command-line option switches available for some parameters. They are described in Table 19.5. Some of these options exist fo…Short Options Short VersionManual ChaptersPG1817.3.1. Short Version ./configure make su make install adduser postgres mkdir -p /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /usr/…Short Version Short VersionManual ChaptersPG1817.4.1. Short Version meson setup build --prefix=/usr/local/pgsql cd build ninja su ninja install adduser postgres mkdir -p /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - po…Short Version SHOWManual ChaptersPG18SHOW SHOW — show the value of a run-time parameter Synopsis SHOW name SHOW ALLSHOW Showing the Server StatusManual ChaptersPG18Showing the Server Status Here is sample status output from pg_ctl: $ pg_ctl status pg_ctl: server is running (PID: 13718) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" "-p" "54…Showing the Server Status Shutdown CallbackManual ChaptersPG1849.2.4. Shutdown Callback The shutdown_cb callback is called when the archiver process exits (e.g., after an error) or the value of archive_library changes. If no shutdown_cb is defined, no …Shutdown Callback Shutdown CallbackManual ChaptersPG1847.6.4.2. Shutdown Callback The optional shutdown_cb callback is called whenever a formerly active replication slot is not used anymore and can be used to deallocate resources private to the…Shutdown Callback Shutdown CallbackManual ChaptersPG1850.3.3. Shutdown Callback The shutdown_cb callback is executed when the server backend has finished validating tokens for the connection. If the validator module has any allocated state, thi…Shutdown Callback Shutting Down the ServerManual ChaptersPG1818.5. Shutting Down the Server There are several ways to shut down the database server. Under the hood, they all reduce to sending a signal to the supervisor postgres process. If you are usi…Shutting Down the Server SIMILAR TO Regular ExpressionsManual ChaptersPG189.7.2. SIMILAR TO Regular Expressions string SIMILAR TO pattern [ESCAPE escape-character] string NOT SIMILAR TO pattern [ESCAPE escape-character] The SIMILAR TO operator returns true or fals…SIMILAR TO Regular Expressions Simple CASEManual ChaptersPG1841.6.4.4. Simple CASE CASE search-expression WHEN expression [, expression [ ... ]] THEN statements [ WHEN expression [, expression [ ... ]] THEN statements ... ] [ ELSE statements ] END CAS…Simple CASE Simple DictionaryManual ChaptersPG1812.6.2. Simple Dictionary The simple dictionary template operates by converting the input token to lower case and checking it against a file of stop words. If it is found in the file then an…Simple Dictionary Simple LoopsManual ChaptersPG1841.6.5. Simple Loops With the LOOP, EXIT, CONTINUE, WHILE, FOR, and FOREACH statements, you can arrange for your PL/pgSQL function to repeat a series of commands.Simple Loops Simple QueryManual ChaptersPG1854.2.2. Simple Query A simple query cycle is initiated by the frontend sending a Query message to the backend. The message includes an SQL command (or commands) expressed as a text string. T…Simple Query Single-Column StatisticsManual ChaptersPG1814.2.1. Single-Column Statistics As we saw in the previous section, the query planner needs to estimate the number of rows retrieved by a query in order to make good choices of query plans. …Single-Column Statistics Single-Row ComparisonManual ChaptersPG189.24.6. Single-Row Comparison row_constructor operator (subquery) The left-hand side is a row constructor, as described in Section 4.2.13. The right-hand side is a parenthesized subquery, wh…Single-Row Comparison Single-User ModeManual ChaptersPG18Single-User Mode To start a single-user mode server, use a command like postgres --single -D /usr/local/pgsql/data other-options my_database Provide the correct path to the database director…Single-User Mode Snapshot Synchronization FunctionsManual ChaptersPG189.28.5. Snapshot Synchronization Functions PostgreSQL allows database sessions to synchronize their snapshots. A snapshot determines which data is visible to the transaction that is using th…Snapshot Synchronization Functions Snowball DictionaryManual ChaptersPG1812.6.6. Snowball Dictionary The Snowball dictionary template is based on a project by Martin Porter, inventor of the popular Porter's stemming algorithm for the English language. Snowball no…Snowball Dictionary SolarisManual ChaptersPG1817.7.4. Solaris PostgreSQL is well-supported on Solaris. The more up to date your operating system, the fewer issues you will experience.Solaris Some Notes about pg_dumpManual ChaptersPG1814.4.9. Some Notes about pg_dump Dump scripts generated by pg_dump automatically apply several, but not all, of the above guidelines. To restore a pg_dump dump as quickly as possible, you ne…Some Notes about pg_dump Sorting Rows (ORDER BY)Manual ChaptersPG187.5. Sorting Rows (ORDER BY) After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be ret…Sorting Rows (ORDER BY) SoundexManual ChaptersPG18F.16.1. Soundex The Soundex system is a method of matching similar-sounding names by converting them to the same code. It was initially used by the United States Census in 1880, 1900, and 19…Soundex Source CodeManual ChaptersPG18E.6.3.8. Source Code Separate the loading and running of injection points (Michael Paquier, Heikki Linnakangas) § § Injection points can now be created, but not run, via INJECTION_POINT_LOAD…Source Code