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
2
votes
1 answer

Postgis Extension install PostgreSQL

When I try to enable PostGis extension on my database I receive the following: postgis=# CREATE EXTENSION postgis; ERROR: could not load library "/usr/pgsql-9.3/lib/rtpostgis-2.1.so": libhdf5.so.6: cannot open shared object file: No such file or…
user3474547
  • 31
  • 3
1
vote
1 answer

Error while implementing Custom Operator in Postgresql

Creating a Simple Custom Operator in my Postgres Extension pg_sample_ext, but when implementing it in the database getting an error. The code for the update script and error are mentioned below. Update script pg_sample_ext--1.0.1--1.0.2.sql: --…
Ishaan Adarsh
  • 197
  • 1
  • 11
1
vote
1 answer

ERROR: While executing gem ... (OptionParser::InvalidOption) invalid option: --no-rdoc

While running the command pgxn install pgxn_utils Getting the error: INFO: best version: pgxn_utils 0.1.4 INFO: saving /var/folders/kn/b8b9x9yn683cjfkl1xd9jjwh0000gn/T/tmpnb50s2nv/pgxn_utils-0.1.4.zip INFO: unpacking:…
Ishaan Adarsh
  • 197
  • 1
  • 11
1
vote
1 answer

ALTER EXTENSION ERROR : extension "my_extension" has no update path from version "1.0.0" to version "1.0.1"

Trying to implement ALTER EXTENSION on custom extension my_extension. Made the following changes: Created a new file my_extension--1.0.1.sql I changed the code of the control file: default_version = '1.0.0' to default_version = '1.0.1' Changed…
Ishaan Adarsh
  • 197
  • 1
  • 11
1
vote
1 answer

Unknown META specification, cannot validate. [Spec v1.0.1]

PGXN upload issue: I am trying to uplaod this distribution pcakage into PGXN, this is the new version my_extension 1.0.1. THe previous extension: https://pgxn.org/dist/my_extension/1.0.0/ Getting this error: Error message The extension code is on my…
Ishaan Adarsh
  • 197
  • 1
  • 11
1
vote
1 answer

PostgreSQL C Extension function: Table as argument and as Result

I need to write an extension function in C for PostgreSQL that: Has as argument a table of 3 columns of type double Has as result a table of 3 columns of type double Note that the mode should ideally be in materialize mode, since the function will…
Rafael Scudelari de Macedo
  • 25
  • 6
1
vote
0 answers

installing aws_s3 to a postgres db hosted in an aws ec2 instance

I would like to fetche the contents of a postgres db into an s3 bucket To do this, I need to install the aws_s3 and aws_commons extensions in the database. However, when I follow the instructions here I get this error: some_postgress_db=# CREATE…
DonB
  • 21
  • 2
1
vote
1 answer

Correct way of installing postgresql extension with custom library

TL;DR One has to compile their custom library as shared library: gcc -c -fPIC warp_client.c -o warp_client.o gcc -shared warp_client.o libwarp-client.so Include the shared library and additional dependencies of that shared library in the…
Hyrikan
  • 59
  • 7
1
vote
0 answers

Installing pgrouting on macos

I am struggling getting the pgrouting extension to Postgresql to work. I am running PG 13 and attempting to install pgrouting 3.3.0. The hos OS is Mac Monterey 12.0.1 (M1). I have built pgrouting from source and all seems to go well. I had though to…
Ulrik Balslev
  • 53
  • 5
1
vote
1 answer

psql: display functions with \df that do *not* contain a certain pattern

Because I installed dblink extension into the (default) public schema, \df will display all the dblink related functions. I only want to view customized function. How can I see function that not contain text "dblink"? So far what I tried. \df !~…
jian
  • 4,119
  • 1
  • 17
  • 32
1
vote
0 answers

How can I use pg_proctab extension in AWS Aurora for Monitoring purpose?

I have recently came across this pg_proctab extension through which we should be able to get the details of OS stats. However I am unable to get the value out of the functions built by this extension, Here is the the readme from the GIT code…
Chetan Dev Khanna
  • 127
  • 7
1
vote
1 answer

Install postgres extension PGXN "fatal error: unistd.h: No such file or directory"

I need to sync some postgres tables with elasticsearch and come to conclusion that the best solution for my case is the one described here. Unfortunately I'm still failing to install required postgres extension amqp. I use docker so I start from…
Tomáš Vavřinka
  • 591
  • 8
  • 27
1
vote
1 answer

Connection failed with pg_cron extension

Need help with pg_cron extension: I'm using it for the first time: it is failing for me with 'connection failed' error. I have followed all https://github.com/citusdata/pg_cron mentioned instructions. Can anyone help me with it: In my case, it is…
Channa
  • 742
  • 17
  • 28
1
vote
2 answers

Can't install extension on Postgresql

I try to install semver on my Postgresql 12. I installed postgis successfully and used following command to install pg-semver (semver extension) on my Centos 7 server: yum install pg-semver Then i ran CREATE EXTENSION semver; I got following…
Micheal Toru
  • 422
  • 4
  • 28
1
vote
2 answers

How to detect if view was created by an extension?

In PostgreSQL on how can one know whether a specific view was created by an extension? What SQL query must be executed to find out? No manual solutions.
Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
1
2
3 4 5