Class Sql.pgsql

Inheritance graph

Description

This is an interface to the PostgreSQL database server. This module is independent of any external libraries. Note that you do not need to have a PostgreSQL server running on your host to use this module: you can connect to the database over a TCP/IP socket on a different host.

This module replaces the functionality of the older Sql.postgres and Postgres.postgres modules.

This module supports the following features:

PostgreSQL network protocol version 3, authentication methods currently supported are: cleartext, md5 and scram (recommended).
Optional asynchronous query interface through callbacks.
Streaming queries which do not buffer the whole resultset in memory.
Automatic binary transfers to and from the database for most common datatypes (amongst others: integer, text and bytea types).
Automatic character set conversion and native wide string support. Supports UTF8/Unicode for multibyte characters, and all single-byte character sets supported by the database.
SQL-injection protection by allowing just one statement per query and ignoring anything after the first (unquoted) semicolon in the query.
COPY support for streaming up- and download.
Accurate error messages.
Automatic precompilation of complex queries (session cache).
Multiple simultaneous queries on the same database connection.
Cancelling of long running queries by force or by timeout.
Event driven NOTIFY.
SSL encrypted connections (optional or forced).

Check the PostgreSQL documentation for further details.

Note

Multiple simultaneous queries on the same database connection are a feature that none of the other database drivers for Pike support. So, although it's efficient, its use will make switching database drivers difficult.

Method create

Description

With no arguments, this function initialises a connection to the PostgreSQL backend. Since PostgreSQL requires a database to be selected, it will try to connect to the default database. The connection may fail however, for a variety of reasons; in this case the most likely reason is because you don't have sufficient privileges to connect to that database. So use of this particular syntax is discouraged.

Parameter host

Should either contain "hostname" or "hostname:portname". This allows you to specify the TCP/IP port to connect to. If the parameter is 0 or "", it will try to connect to localhost, default port.

Parameter database

Specifies the database to connect to. Not specifying this is only supported if the PostgreSQL backend has a default database configured. If you do not want to connect to any live database, you can use "template1".

Parameter options

Currently supports at least the following:

`"reconnect" : int`	Set it to zero to disable automatic reconnects upon losing the connection to the database. Not setting it, or setting it to one, will cause one timed reconnect to take place. Setting it to -1 will cause the system to try and reconnect indefinitely.
`"use_ssl" : int`	If the database supports and allows SSL connections, the session will be SSL encrypted, if not, the connection will fallback to plain unencrypted.
`"force_ssl" : int`	If the database supports and allows SSL connections, the session will be SSL encrypted, if not, the connection will abort.
`"text_query" : int`	Send queries to and retrieve results from the database using text instead of the, generally more efficient, default native binary method. Turning this on will allow multiple statements per query separated by semicolons (not recommended).
`"sync_parse" : int`	Set it to zero to turn synchronous parsing off for statements. Setting this to off can cause surprises because statements could be parsed before the previous statements have been executed (e.g. references to temporary tables created in the preceding statement), but it can speed up parsing due to increased parallelism.
`"cache_autoprepared_statements" : int`	If set to one, it enables the automatic statement prepare and cache logic; caching prepared statements can be problematic when stored procedures and tables are redefined which leave stale references in the already cached prepared statements. The default is off, because PostgreSQL 10.1 (at least) has a bug that makes it spike to 100% CPU sometimes when this is on.
`"client_encoding" : string`	Character encoding for the client side, it defaults to using the default encoding specified by the database, e.g. `"UTF8"` or `"SQL_ASCII"`.
`"standard_conforming_strings" : string`	When on, backslashes in strings must not be escaped any longer, quote() automatically adjusts quoting strategy accordingly.
`"escape_string_warning" : string`	When on, a warning is issued if a backslash (\) appears in an ordinary string literal and `"standard_conforming_strings"` is off, defaults to on.

For the numerous other options please check the PostgreSQL manual.

Note

You need to have a database selected before using the SQL-object, otherwise you'll get exceptions when you try to query it. Also notice that this function can raise exceptions if the db server doesn't respond, if the database doesn't exist or is not accessible to you.

Note

It is possible that the exception from a failed connect will not be triggered on this call (because the connect proceeds asynchronously in the background), but on the first attempt to actually use the database instead.