Program usage ============= The program entry point is the script called :program:`pgxn`. Usage: .. parsed-literal:: :class: pgxn pgxn [--help] [--version] *COMMAND* [--mirror *URL*] [--verbose] [--yes] ... The script offers several commands, whose list can be obtained using ``pgxn --help``. The options available for each subcommand can be obtained using :samp:`pgxn {COMMAND} --help`. The main commands you may be interested in are `install`_ (to download, build and install an extension distribution into the system) and `load`_ (to load an installed extension into a database). Commands to perform reverse operations are `uninstall`_ and `unload`_. Use `download`_ to get a package from a mirror without installing it. There are also informative commands: `search <#pgxn-search>`_ is used to search the network, `info`_ to get information about a distribution. The `mirror`_ command can be used to get a list of mirrors. A few options are available to all the commands: :samp:`--mirror {URL}` Select a mirror to interact with. If not specified the default is ``https://api.pgxn.org/``. ``--verbose`` Print more information during the process. ``--yes`` Assume affirmative answer to all questions. Useful for unattended scripts. Package specification --------------------- Many commands such as install_ require a *package specification* to operate. In its simple form the specification is just the name of a distribution: ``pgxn install foo`` means "install the most recent stable release of the ``foo`` distribution". If a distribution with given name is not found, many commands will look for an *extension* with the given name, and will work on it. The specification allows specifying an operator and a version number, so that ``pgxn install 'foo<2.0'`` will install the most recent stable release of the distribution before the release 2.0. The version numbers are ordered according to the `Semantic Versioning specification `__. Supported operators are ``=``, ``==`` (alias for ``=``), ``<``, ``<=``, ``>``, ``>=``. Note that you probably need to quote the string as in the example to avoid invoking shell command redirection. Whenever a command takes a specification in input, it also accepts options ``--stable``, ``--testing`` and ``--unstable`` to specify the minimum release status accepted. The default is "stable". A few commands also allow specifying a local archive or local directory containing a distribution: in this case the specification should contain at least a path separator to disambiguate it from a distribution name (for instance ``pgxn install ./foo.zip``) or it should be specified as an URL with ``file://`` schema. A few commands also allow specifying a remote package with a URL. Currently the schemas ``http://`` and ``https://`` are supported. Currently the client supports ``.zip`` and ``.tar`` archives (eventually with *gzip* and *bz2* compression). .. _install: ``pgxn install`` ---------------- Download, build, and install a distribution on the local system. Usage: .. parsed-literal:: :class: pgxn-install pgxn install [--help] [--stable | --testing | --unstable] [--pg_config *PROG*] [--make *PROG*] [--sudo [*PROG*] | --nosudo] *SPEC* The program takes a `package specification`_ identifying the distribution to work with. The download phase is skipped if the distribution specification refers to a local directory or package. The package may be specified with an URL. Note that the built extension is not loaded in any database: use the command `load`_ for this purpose. The command will run the ``configure`` script if available in the package, then will perform ``make all`` and ``make install``. It is assumed that the ``Makefile`` provided by the distribution uses PGXS_ to build the extension, but this is not enforced: you may provide any Makefile as long as the expected commands are implemented. .. _PGXS: https://www.postgresql.org/docs/current/extend-pgxs.html If there are many PostgreSQL installations on the system, the extension will be built and installed against the instance whose :program:`pg_config` is first found on the :envvar:`PATH`. A different instance can be specified using the option :samp:`--pg_config {PATH}`. The PGXS_ build system relies on a presence of `GNU Make`__: in many systems it is installed as :program:`gmake` or :program:`make` executable. The program will use the first of them on the path. You can specify an alternative program using ``--make`` option. .. __: https://www.gnu.org/software/make/ If the extension is being installed into a system PostgreSQL installation, the install phase will likely require root privileges to be performed. In this case either run the command under :program:`sudo` or specify the ``--sudo`` option: in the latter case :program:`sudo` will only be invoked during the "install" phase. An optional program :samp:`{PROG}` to elevate the user privileges can be specified as ``--sudo`` option; if none is specified, :program:`sudo` will be used. .. note:: If ``--sudo`` is the last option and no :samp:`{PROG}` is specified, a ``--`` separator may be required to disambiguate the :samp:`{SPEC}`:: pgxn install --sudo -- foobar .. _check: ``pgxn check`` -------------- Run a distribution's unit test. Usage: .. parsed-literal:: :class: pgxn-check pgxn check [--help] [--stable | --testing | --unstable] [--pg_config *PROG*] [--make *PROG*] [-d *DBNAME*] [-h *HOST*] [-p *PORT*] [-U *NAME*] *SPEC* The command takes a `package specification`_ identifying the distribution to work with, which can also be a local file or directory or an URL. The distribution is unpacked if required and the ``installcheck`` make target is run. .. note:: The command doesn't run ``make all`` before ``installcheck``: if any file required for testing is to be built, it should be listed as ``installcheck`` prerequisite in the ``Makefile``, for instance: .. code-block:: make myext.sql: myext.sql.in some_command installcheck: myext.sql The script exits with non-zero value in case of test failed. In this case, if files ``regression.diff`` and ``regression.out`` are produced (as :program:`pg_regress` does), these files are copied to the local directory where the script is run. The database connection options are similar to the ones in load_, with the difference that the variable :envvar:`PGDATABASE` doesn't influence the database name. See the install_ command for details about the command arguments. .. warning:: At the time of writing, :program:`pg_regress` on Debian and derivatives is affected by `bug #554166`__ which makes *HOST* selection impossible. .. __: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=554166 .. _uninstall: ``pgxn uninstall`` ------------------ Remove a distribution from the system. Usage: .. parsed-literal:: :class: pgxn-uninstall pgxn uninstall [--help] [--stable | --testing | --unstable] [--pg_config *PROG*] [--make *PROG*] [--sudo [*PROG*] | --nosudo] *SPEC* The command does the opposite of the install_ command, removing a distribution's files from the system. It doesn't issue any command to the databases where the distribution's extensions may have been loaded: you should first drop the extension (the unload_ command can do this). The distribution should match what installed via the `install`_ command. See the install_ command for details about the command arguments. .. _load: ``pgxn load`` ------------- Load the extensions included in a distribution into a database. The distribution must be already installed in the system, e.g. via the `install`_ command. Usage: .. parsed-literal:: :class: pgxn-load pgxn load [--help] [--stable | --testing | --unstable] [-d *DBNAME*] [-h *HOST*] [-p *PORT*] [-U *NAME*] [--pg_config *PATH*] [--schema *SCHEMA*] *SPEC* [*EXT* [*EXT* ...]] The distribution is specified according to the `package specification`_ and can refer to a local directory or file or to an URL. No consistency check is performed between the packages specified in the ``install`` and ``load`` command: the specifications should refer to compatible packages. The specified distribution is only used to read the metadata: only installed files are actually used to issue database commands. The database to install into can be specified using options ``-d``/``--dbname``, ``-h``/``--host``, ``-p``/``--port``, ``-U``/``--username``. The default values for these parameters are the regular system ones and can be also set using environment variables :envvar:`PGDATABASE`, :envvar:`PGHOST`, :envvar:`PGPORT`, :envvar:`PGUSER`. The command supports also a ``--pg_config`` option that can be used to specify an alternative :program:`pg_config` to use to look for installation scripts: you may need to specify the parameter if there are many PostgreSQL installations on the system, and should be consistent to the one specified in the ``install`` command. If the specified database version is at least PostgreSQL 9.1, and if the extension specifies a ``.control`` file, it will be loaded using the `CREATE EXTENSION`_ command, otherwise it will be loaded as a loose set of objects. For more information see the `extensions documentation`__. .. _CREATE EXTENSION: https://www.postgresql.org/docs/current/sql-createextension.html .. __: https://www.postgresql.org/docs/current/extend-extensions.html The command is based on the `'provides' section`_ of the distribution's ``META.json``: if a SQL file is specified, that file will be used to load the extension. Note that loading is only attempted if the file extension is ``.sql``: if it's not, we assume that the extension is not really a PostgreSQL extension (it may be for instance a script). If no ``file`` is specified, a file named :samp:`{extension}.sql` will be looked for in a few directories under the PostgreSQL ``shared`` directory and it will be loaded after an user confirmation. If the distribution provides more than one extension, the extensions are loaded in the order in which they are specified in the ``provides`` section of the ``META.json`` file. It is also possible to load only a few of the extensions provided, specifying them after *SPEC*: the extensions will be loaded in the order specified. If a *SCHEMA* is specified, the extensions are loaded in the provided schema. Note that if ``CREATE EXTENSION`` is used, the schema is directly supported; otherwise the ``.sql`` script loaded will be patched to create the objects in the provided schema (a confirmation will be asked before attempting loading). .. _'provides' section: https://pgxn.org/spec/#provides .. _unload: ``pgxn unload`` --------------- Unload a distribution's extensions from a database. Usage: .. parsed-literal:: :class: pgxn-unload pgxn unload [--help] [--stable | --testing | --unstable] [-d *DBNAME*] [-h *HOST*] [-p *PORT*] [-U *NAME*] [--pg_config *PATH*] [--schema *SCHEMA*] *SPEC* [*EXT* [*EXT* ...]] The command does the opposite of the load_ command: it drops a distribution extensions from the specified database, either issuing `DROP EXTENSION`_ commands or running uninstall scripts eventually provided. For every extension specified in the `'provides' section`_ of the distribution ``META.json``, the command will look for a file called :samp:`uninstall_{file.sql}` where :samp:`{file.sql}` is the ``file`` specified. If no file is specified, :samp:`{extension}.sql` is assumed. If a file with extension different from ``.sql`` is specified, it is assumed that the extension is not a PostgreSQL extension so unload is not performed. If a *SCHEMA* is specified, the uninstall script will be patched to drop the objects in the selected schema. However, if the extension was loaded via ``CREATE EXTENSION``, the server will be able to figure out the correct schema itself, so the option will be ignored. If the distribution specifies more than one extension, they are unloaded in reverse order respect to the order in which they are specified in the ``META.json`` file. It is also possible to unload only a few of the extensions provided, specifying them after *SPEC*: the extensions will be unloaded in the order specified. .. _DROP EXTENSION: https://www.postgresql.org/docs/current/sql-dropextension.html See the load_ command for details about the command arguments. .. _download: ``pgxn download`` ----------------- Download a distribution from the network. Usage: .. parsed-literal:: :class: pgxn-download pgxn download [--help] [--stable | --testing | --unstable] [--target *PATH*] *SPEC* The distribution is specified according to the `package specification`_ and can be represented by an URL. The file is saved in the current directory with name usually :samp:`{distribution}-{version}.zip`. If a file with the same name exists, a suffix ``-1``, ``-2`` etc. is added to the name, before the extension. A different directory or name can be specified using the ``--target`` option. .. _pgxn-search: ``pgxn search`` --------------- Search in the extensions available on PGXN. Usage: .. parsed-literal:: :class: pgxn-search pgxn search [--help] [--dist | --ext | --docs] *TERM* [*TERM* ...] The command prints on ``stdout`` a list of packages and version matching :samp:`{TERM}`. By default the search is performed in the documentation: alternatively the distributions (using the ``--dist`` option) or the extensions (using the ``--ext`` option) can be searched. Example: .. code-block:: console $ pgxn search --dist integer tinyint 0.1.1 Traditionally, PostgreSQL core has a policy not to have 1 byte *integer* in it. With this module, you can define 1 byte *integer* column on your tables, which will help query performances and... check_updates 1.0.0 ... test2 defined as: CREATE TABLE test2(a *INTEGER*, b *INTEGER*, c *INTEGER*, d *INTEGER*); To make a trigger allowing updates only when c becomes equal to 5: CREATE TRIGGER c_should_be_5 BEFORE UPDATE ON... ssn 1.0.0 INSERT INTO test VALUES('124659876'); The output is always represented using the format with dashes, i.e: 123-45-6789 124-65-9876 Internals: The type is stored as a 4 bytes *integer*. The search will return all the matches containing any of *TERM*. In order to search for items containing more than one word, join the word into a single token. For instance to search for items containing the terms "double precision" or the terms "floating point" use: .. code-block:: console $ pgxn search "double precision" "floating point" semver 0.2.2 ... to semver semver(12.0::real) 12.0.0semver(*double precision*) Cast *double precision* to semver semver(9.2::*double precision*) 9.2.0semver(integer) Cast integer to semver semver(42::integer)... saio 0.0.1 Defaults to true. saio_seed A *floating point* seed for the random numbers generator. saio_equilibrium_factor Scaling factor for the query size, determining the number of loops before equilibrium is... pgTAP 0.25.0 ... ) casts_are( casts[] ) SELECT casts_are( ARRAY[ 'integer AS *double precision*', 'integer AS reltime', 'integer AS numeric', -- ... .. _info: ``pgxn info`` ------------- Print information about a distribution obtained from PGXN. Usage: .. parsed-literal:: :class: pgxn-info pgxn info [--help] [--stable | --testing | --unstable] [--details | --meta | --readme | --versions] *SPEC* The distribution is specified according to the `package specification`_. It cannot be a local dir or file nor an URL. The command output is a list of values obtained by the distribution's ``META.json`` file, for example: .. code-block:: console $ pgxn info pair name: pair abstract: A key/value pair data type description: This library contains a single PostgreSQL extension, a key/value pair data type called “pair”, along with a convenience function for constructing key/value pairs. maintainer: David E. Wheeler license: postgresql release_status: stable version: 0.1.2 date: 2011-04-20T23:47:22Z sha1: 9988d7adb056b11f8576db44cca30f88a08bd652 provides: pair: 0.1.2 Alternatively the raw ``META.json`` (using the ``--meta`` option) or the distribution README (using the ``--readme`` option) can be obtained. Using the ``--versions`` option, the command prints a list of available versions for the specified distribution, together with their release status. Only distributions respecting :samp:`{SPEC}` and the eventually specified release status options are printed, for example: .. code-block:: console $ pgxn info --versions 'pair<0.1.2' pair 0.1.1 stable pair 0.1.0 stable .. _mirror: ``pgxn mirror`` --------------- Return information about the available mirrors. Usage: .. parsed-literal:: :class: pgxn-mirror pgxn mirror [--help] [--detailed] [*URI*] If no :samp:`URI` is specified, print a list of known mirror URIs. Otherwise print details about the specified mirror. It is also possible to print details for all the known mirrors using the ``--detailed`` option. .. _help: ``pgxn help`` ------------- Display help and other program information. Usage: .. parsed-literal:: :class: pgxn-help pgxn help [--help] [--all | --libexec | *CMD*] Without options show the same information obtained by ``pgxn --help``, which includes a list of builtin commands. With the ``--all`` option print the complete list of commands installed in the system. The option ``--libexec`` prints the full path of the directory containing the external commands scripts: see :ref:`extending` for more information. :samp:`pgxn help {CMD}` is an alias for :samp:`pgxn {CMD} --help`.