LibPQ API

mutable struct Connection

A connection to a PostgreSQL database.

Fields:

• conn

  A pointer to a libpq PGconn object (C_NULL if closed)

• encoding

  libpq client encoding (string encoding of returned data)

• uid_counter

  Integer counter for generating connection-level unique identifiers

• type_map

  Connection-level type correspondence map

• func_map

  Connection-level conversion functions

• closed

  True if the connection is closed and the PGconn object has been cleaned up

execute(
    {jl_conn::Connection, query::AbstractString | stmt::Statement},
    [parameters::AbstractVector,]
    throw_error::Bool=true,
    column_types::AbstractDict=ColumnTypeMap(),
    type_map::AbstractDict=LibPQ.PQTypeMap(),
    conversions::AbstractDict=LibPQ.PQConversions(),
) -> Result

Run a query on the PostgreSQL database and return a Result. If throw_error is true, throw an error and clear the result if the query results in a fatal error or unreadable response.

The query may be passed as Connection and AbstractString (SQL) arguments, or as a Statement.

execute optionally takes a parameters vector which passes query parameters as strings to PostgreSQL.

column_types accepts type overrides for columns in the result which take priority over those in type_map. For information on the column_types, type_map, and conversions arguments, see Type Conversions.

prepare(jl_conn::Connection, query::AbstractString) -> Statement

Create a prepared statement on the PostgreSQL server using libpq. The statement is given an generated unique name using unique_id.

status(jl_conn::Connection) -> libpq_c.ConnStatusType

Return the status of the PostgreSQL database connection according to libpq. Only CONNECTION_OK and CONNECTION_BAD are valid for blocking connections, and only blocking connections are supported right now.

See also: error_message

close(jl_conn::Connection)

Close the PostgreSQL database connection and free the memory used by the PGconn object. This function calls PQfinish, but only if jl_conn.closed is false, to avoid a double-free.

isopen(jl_conn::Connection) -> Bool

Check whether a connection is open.

reset!(jl_conn::Connection; throw_error=true)

Reset the communication to the PostgreSQL server. The PGconn object will be recreated using identical connection parameters.

See handle_new_connection for information on the throw_error argument.

show(io::IO, jl_conn::Connection)

Display a Connection by showing the connection status and each connection option.

mutable struct Result <: DataStreams.Data.Source

A result from a PostgreSQL database query

Fields:

• result

  A pointer to a libpq PGresult object (C_NULL if cleared)

• column_oids

  PostgreSQL Oids for each column in the result

• column_types

  Julia types for each column in the result

• not_null

  Whether to expect NULL for each column (whether output data can have missing)

• column_funcs

  Conversions from PostgreSQL data to Julia types for each column in the result

status(jl_result::Result) -> libpq_c.ExecStatusType

Return the status of a result's corresponding database query according to libpq. Only CONNECTION_OK and CONNECTION_BAD are valid for blocking connections, and only blocking connections are supported right now.

See also: error_message

close(jl_result::Result)

Clean up the memory used by the PGresult object. The Result will no longer be usable.

isopen(jl_result::Result)

Determine whether the given Result has been closed, i.e. whether the memory associated with the underlying PGresult object has been cleared.

num_rows(jl_result::Result) -> Int

Return the number of rows in the query result. This will be 0 if the query would never return data.

num_columns(jl_result::Result) -> Int

Return the number of columns in the query result. This will be 0 if the query would never return data.

num_affected_rows(jl_result::Result) -> Int

Return the number of rows affected by the command returning the result. This is useful for counting the rows affected by operations such as INSERT, UPDATE and DELETE that do not return rows but affect them. This will be 0 if the query does not affect any row.

show(io::IO, jl_result::Result)

Show a PostgreSQL result and whether it has been cleared.

struct Statement

A PostgreSQL prepared statement

Fields:

• jl_conn

  A Connection for which this statement is valid. It may become invalid if the connection is reset.

• name

  An autogenerated neame for the prepared statement (using unique_id

• query

  The query string of the prepared statement

• description

  A Result containing a description of the prepared statement

• num_params

  The number of parameters accepted by this statement according to description

num_columns(stmt::Statement) -> Int

Return the number of columns that would be returned by executing the prepared statement.

num_params(stmt::Statement) -> Int

Return the number of parameters in the prepared statement.

show(io::IO, jl_result::Statement)

Show a PostgreSQL prepared statement and its query.

Statement(sch::Data.Schema, ::Type{Data.Row}, append, connection::Connection, query::AbstractString) -> Statement

Construct a Statement for use in streaming with DataStreams. This function is called by Data.stream!(source, Statement, connection, query).

fetch!(sink::Union{T, Type{T}}, result::Result, args...; kwargs...) where {T} -> T

Stream data to sink or a new structure of type T using Data.stream!. Any trailing args or kwargs are passed to Data.stream!. result is cleared upon completion.

handle_new_connection(jl_conn::Connection; throw_error=true) -> Connection

Check status and handle errors for newly-created connections. Also set the client encoding (23.3. Character Set Support) to jl_conn.encoding.

If throw_error is true, an error will be thrown if the connection's status is CONNECTION_BAD and the PGconn object will be cleaned up. Otherwise, a warning will be shown and the user should call close or reset! on the returned Connection.

server_version(jl_conn::Connection) -> VersionNumber

Get the PostgreSQL version of the server.

See 33.2. Connection Status Functions for information on the integer returned by PQserverVersion that is parsed by this function.

See @pqv_str for information on how this packages represents PostgreSQL version numbers.

encoding(jl_conn::Connection) -> String

Return the client encoding name for the current connection (see Table 23.1. PostgreSQL Character Sets for possible values).

Currently all Julia connections are set to use UTF8 as this makes conversion to and from String straighforward.

See also: set_encoding!, reset_encoding!

set_encoding!(jl_conn::Connection, encoding::String)

Set the client encoding for the current connection (see Table 23.1. PostgreSQL Character Sets for possible values).

Currently all Julia connections are set to use UTF8 as this makes conversion to and from String straighforward. Other encodings are not explicitly handled by this package and will probably be very buggy.

See also: encoding, reset_encoding!

reset_encoding!(jl_conn::Connection, encoding::String)

Reset the client encoding for the current connection to jl_conn.encoding.

See also: encoding, set_encoding!

transaction_status(jl_conn::Connection) -> libpq_c.PGTransactionStatusType

Return the PostgreSQL database server's current in-transaction status for the connection. See for information on the meaning of the possible return values.

unique_id(jl_conn::Connection, prefix::AbstractString="") -> String

Return a valid PostgreSQL identifier that is unique for the current connection. This is mostly used to create names for prepared statements.

error_message(jl_conn::Connection) -> String

Return the error message most recently generated by an operation on the connection. Includes a trailing newline.

struct ConnectionOption

A Julia representation of a PostgreSQL connection option (PQconninfoOption).

Fields:

• keyword

  The name of the option

• envvar

  The name of the fallback environment variable for this option

• compiled

  The PostgreSQL compiled-in default for this option

• val

  The value of the option if set

• label

  The label of the option for display

• disptype

  Indicator for how to display the option (see ConninfoDisplay)

• dispsize

  The size of field to provide for entry of the option value (not used here)

conninfo(jl_conn::Connection) -> Vector{ConnectionOption}

Get all connection options for a connection.

conninfo(str::AbstractString) -> Vector{ConnectionOption}

Parse connection options from a connection string (either a URI or key-value pairs).

Indicator for how to display a PostgreSQL connection option (PQconninfoOption).

Possible values are:

• Normal (libpq: ""): display as is
• Password (libpq: "*"): hide the value of this field
• Debug (libpq: "D"): don't show by default

parse(::Type{ConninfoDisplay}, str::AbstractString) -> ConninfoDisplay

Parse a ConninfoDisplay from a string. See ConninfoDisplay.

handle_result(jl_result::Result; throw_error::Bool=true) -> Result

Check status and handle errors for newly-created result objects.

If throw_error is true, throw an error and clear the result if the query results in a fatal error or unreadable response. Otherwise a warning is shown.

Also print an info message about the result.

column_name(jl_result::Result, column_number::Integer) -> String

Return the name of the column at index column_number (1-based).

column_name(stmt::Statement, column_number::Integer) -> String

Return the name of the column at index column_number (1-based) that would be returned by executing the prepared statement.

column_names(jl_result::Result) -> Vector{String}

Return the names of all the columns in the query result.

column_names(stmt::Statement) -> Vector{String}

Return the names of all the columns in the query result that would be returned by executing the prepared statement.

column_number(jl_result::Result, column_name::Union{AbstractString, Symbol}) -> Int

Return the index (1-based) of the column named column_name.

column_number(jl_result::Result, column_idx::Integer) -> Int

Return the index of the column if it is valid, or error.

column_number(stmt::Statement, column_name::AbstractString) -> Int

Return the index (1-based) of the column named column_name that would be returned by executing the prepared statement.

column_oids(jl_result::Result) -> Vector{LibPQ.Oid}

Return the PostgreSQL oids for each column in the result.

column_types(jl_result::Result) -> Vector{Type}

Return the corresponding Julia types for each column in the result.

num_params(jl_result::Result) -> Int

Return the number of parameters in a prepared statement. If this result did not come from the description of a prepared statement, return 0.

error_message(jl_result::Result) -> String

Return the error message associated with the result, or an empty string if there was no error. Includes a trailing newline.

oid(typ::Union{Symbol, String, Integer}) -> LibPQ.Oid

Convert a PostgreSQL type from an AbstractString or Symbol representation to its oid representation. Integers are converted directly to LibPQ.Oids.

primitive type PQChar 8

A one-byte character type for correspondence with PostgreSQL's one-byte "char" type.

Fields:

const PQ_SYSTEM_TYPES::Dict{Symbol, Oid}

Internal mapping of PostgreSQL's default types from PostgreSQL internal name to Oid. The names may not correspond well to the common names, e.g., "char(n)" is :bpchar. This dictionary is generated with the deps/system_type_map.jl script and contains only PostgreSQL's system-defined types. It is expected (but might not be guaranteed) that these are the same across versions and installations.

struct PQTypeMap <: AbstractDict{UInt32,Type}

A mapping from PostgreSQL Oid to Julia type.

Fields:

• type_map

Base.getindex(tmap::PQTypeMap, typ) -> Type

Get the Julia type corresponding to the given PostgreSQL type (any type accepted by oid) according to tmap.

Base.setindex!(tmap::PQTypeMap, val::Type, typ)

Set the Julia type corresponding to the given PostgreSQL type (any type accepted by oid) in tmap.

const _DEFAULT_TYPE_MAP::PQTypeMap

The PQTypeMap containing the default type mappings for LibPQ.jl. This should not be mutated; LibPQ-level type mappings can be added to LIBPQ_TYPE_MAP.

const LIBPQ_TYPE_MAP::PQTypeMap

The PQTypeMap containing LibPQ-level type mappings for LibPQ.jl. Adding type mappings to this constant will override the default type mappings for all code using LibPQ.jl.

struct PQConversions <: AbstractDict{Tuple{UInt32,Type},Union{Function, Type}}

A mapping from Oid and Julia type pairs to the function for converting a PostgreSQL value with said Oid to said Julia type.

Fields:

• func_map

Base.getindex(cmap::PQConversions, oid_typ::Tuple{Any, Type}) -> Base.Callable

Get the function according to cmap for converting a PostgreSQL value of some PostgreSQL type (any type accepted by oid) to some Julia type.

Base.setindex!(cmap::PQConversions, val::Base.Callable, oid_typ::Tuple{Any, Type})

Set the function in cmap for converting a PostgreSQL value of some PostgreSQL type (any type accepted by oid) to some Julia type.

const _DEFAULT_CONVERSIONS::PQConversions

The PQConversions containing the default conversion functions for LibPQ.jl. This should not be mutated; LibPQ-level conversion functions can be added to LIBPQ_CONVERSIONS.

const LIBPQ_CONVERSIONS::PQConversions

The PQConversions containing LibPQ-level conversion functions for LibPQ.jl. Adding conversions to this constant will override the default conversions for all code using LibPQ.jl.

A fallback conversion mapping (like PQConversions which holds a single function for converting PostgreSQL data of a given Oid to a given Julia type, using the parse function.

struct PQValue{OID}

A wrapper for one value in a PostgreSQL result.

Fields:

• jl_result

  PostgreSQL result

• row

  Row index of the result (0-indexed)

• col

  Column index of the result (0-indexed)

data_pointer(pqv::PQValue) -> Ptr{UInt8}

Get a raw pointer to the data for one value in a PostgreSQL result. This data will be freed by libpq when the result is cleared, and should only be used temporarily.

num_bytes(pqv::PQValue) -> Cint

The length in bytes of the PQValue's corresponding data. LibPQ.jl currently always uses text format, so this is equivalent to C's strlen.

See also: data_pointer

unsafe_string(pqv::PQValue) -> String

Construct a String from a PQValue by copying the data.

string_view(pqv::PQValue) -> String

Wrap a PQValue's underlying data in a String. This function uses data_pointer and num_bytes and does not copy.

See also: bytes_view

bytes_view(pqv::PQValue) -> Vector{UInt8}

Wrap a PQValue's underlying data in a vector of bytes. This function uses data_pointer and num_bytes and does not copy.

This function differs from string_view as it keeps the byte at the end. PQValue parsing functions should use bytes_view when the data returned by PostgreSQL is not in UTF-8.

parse(::Type{T}, pqv::PQValue) -> T

Parse a value of type T from a PQValue. By default, this uses any existing parse method for parsing a value of type T from a String.

@pqv_str -> VersionNumber

Parse a PostgreSQL version.

julia> using LibPQ: @pqv_str

julia> pqv"10.1" == v"10.0.1"
true

julia> pqv"9.2.5" == v"9.2.5"
true

string_parameters(parameters::AbstractVector) -> Vector{Union{String, Missing}}

Convert parameters to strings which can be passed to libpq, propagating missing.

parameter_pointers(parameters::AbstractVector{<:Parameter}) -> Vector{Ptr{UInt8}}

Given a vector of parameters, returns a vector of pointers to either the string bytes in the original or C_NULL if the element is missing.

unsafe_string_or_null(ptr::Cstring) -> Union{String, Missing}

Convert a Cstring to a Union{String, Missing}, returning missing if the pointer is C_NULL.