Stack Overflow
Stack Overflow

Questions tagged [postgresql-extensions]

Custom user defined functions written as either stored procedures using SQL and PL/pgSQL or via C that extend the functionality of PostgreSQL

The PostgreSQL RDBMS (relational database management system) allows for users of a PostgreSQL installation to extend the base functionality of SQL via user written code. This code comes in the form of stored procedures, written in SQL or PL/pgSQL, and C source code.

A user may want to extend the functionality of SQL to perform complex operations or aggregations of a database that are not present in SQL or PostgreSQL, or to increase performance by avoiding shipping query results to another host in order to operate on the results using the logic of another application.

The official PostgreSQL documentation is a excellent starting point to begin extending SQL, specifically Chapter 38: Extending SQL. For writing SQL functions, refer to Chapter 38.5: Query Language (SQL) Functions of PostgreSQL's documentation. Information on using the PL/pgSQL procedural language can be found in Chapter 43: PL/pgSQL - SQL Procedural Language. And finally, reference Chapter 38.10: C-Language Functions to learn about writing custom user defined functions for PostgreSQL in C.

Some additional worthwhile resources include the following blog posts and PostgreSQL wiki article:

Several examples of extending SQL via PostgreSQL extensions can also be found in the contrib directory of PostgreSQL's source code.

75 questions
394
votes
6 answers

Using psql how do I list extensions installed in a database?

How do I list all extensions that are already installed in a database or schema from psql? See also Finding a list of available extensions that PostgreSQL ships with
ARV
  • 6,287
  • 11
  • 31
  • 41
15
votes
2 answers

User cannot use extension "uuid-ossp"

EDIT : there is no need for an extension anymore. You can now generate a uuid with the function gen_random_uuid() that is available by default since PostgreSQL 14. I am developing an application in which I decided to use UUIDs for the primary and…
Arnaud Denoyelle
  • 29,980
  • 16
  • 92
  • 148
12
votes
2 answers

Best way to install hstore on multiple schemas in a Postgres database?

I'm working on a project that needs to use hstore on multiple schemas. The 'public' schema, where the hstore extension was being installed isn't available everywhere, because my scope doesn't lookup at 'public'. On some tryouts, I've created the…
Gregório Kusowski
  • 188
  • 1
  • 8
9
votes
1 answer

Missing hstore extension

I am running PostgreSQL 9.5.3 Fedora Linux, and I tried to create the "hstore" extension: CREATE EXTENSION IF NOT EXISTS hstore; But I got this error: could not open extension control file "/usr/share/pgsql/extension/hstore.control": No such file…
user6731260
  • 105
  • 1
  • 1
  • 4
6
votes
1 answer

how to set user for installcheck in the Makefile of a postgres extension?

I am testing an example for creating a simple base36 PostgreSQL extensions. However, I run into problems when writing and using the unit test case (REGRESS =). If I use sudo make installcheck, there will be an error saying ... ==============…
thor
  • 21,418
  • 31
  • 87
  • 173
5
votes
0 answers

How to start pg_cron extension after shutdown

Had to terminate a process run by pg_cron. However, after doing that pg_cron extension stopped. Below is the log from the server: 2021-04-29 12:55:40.712 UTC [535529] LOG: pg_cron scheduler shutting down Now, no jobs are running. How to restart…
Nishant Soni
  • 61
  • 6
4
votes
1 answer

how to install plpythonu extension on postgresql 9.3 ubuntu

I had Postgresql 9.3 server on ubuntu installed, but create extension plpythonu fail with the error: ERROR: could not open extension control file "/usr/share/postgresql/9.1/extension/plpythonu.control": No such file or directory SQL state:…
Hello lad
  • 17,344
  • 46
  • 127
  • 200
4
votes
2 answers

How do I use an extension installed in a different schema?

I added an extension and my install shows the module installed, mydb=# \dx List of installed extensions Name | Version | Schema | Description …
Emerson R Orci
  • 85
  • 1
  • 6
3
votes
1 answer

Docker - How is it possible to make/make install an extension in a Docker container?

I am using the posgres:10.0 image to run PostgreSQL inside a container. My docker-compose.yml looks fairly simple: version: "2" services: db: image: "postgres:10.0" environment: - POSTGRES_USER=postgres - POSTGRES_DB=postgres …
user4460845
3
votes
2 answers

How to write a Postgres language handler using MSVC

Here is the code, straight out of the sample. 64 bit install, x64 build. #include "postgres.h" #include "executor/spi.h" #include "commands/trigger.h" #include "fmgr.h" #include "access/heapam.h" #include "utils/syscache.h" #include…
david.pfx
  • 10,520
  • 3
  • 30
  • 63
3
votes
1 answer

cstore_fdw extension: FATAL: could not access file "‘cstore_fdw’": No such file or directory

I've installed the cstore_fdw extension in PostgreSQL 9.3.5 on OS X, and it looks as though there was no error in the process (/usr/local/pgsql/bin/ is incorrect path, but files were copied where they should be, as pg_config is symlinked in the…
Krzysztof Jędrzejewski
  • 698
  • 4
  • 21
2
votes
1 answer

Install postgres extension with schema

I am trying to install pgcrypto in pg_catalog schema. But this does not work with postgres 13 or higher since the function gen_random_uuid is globally available. How can I still create my extension? I am trying: CREATE EXTENSION IF NOT EXISTS…
Fab C
  • 65
  • 1
  • 6
2
votes
1 answer

How to install external extensions in GCP's PostgreSQL

I see here that only allowed extensions can be installed on PostgreSQL in Google Cloud Platform. Is there any possibility to add an extension that is not listed in supported extensions? The extension I need is pg_semver.
Micheal Toru
  • 422
  • 4
  • 28
2
votes
1 answer

ActiveRecord: "uuid-ossp" extension added, but no uuid functions available

Using rails-5.0.7.1 (according to bundle show rails) I wrote a migration which adds the "uuid-ossp" extension, and the SQL gets executed, and the extension shows up when I type \dx in the psql console. However, the functions that this extension…
jefflunt
  • 33,527
  • 7
  • 88
  • 126
2
votes
1 answer

Additional module citext is installed, but type "citext" not found?

I'm trying to run an inline query on my database - which has the citext extension installed (using CREATE EXTENSION) - and yet the executed query keeps throwing this error when calling a function: type "citext" does not exist DO LANGUAGE…
Michael Brown
  • 1,585
  • 1
  • 22
  • 36
1
2 3 4 5