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

No permissions for postgres extensions in CosmosDB for PostgreSQL

I am using CosmosDB for PostgreSQL and I need permissions to pgcrypto pg extension with the default "citus" user but don't. I can't give myself access because I can't log in as the default superuser "postgres". If I don't have permissions for…
gabe
  • 1,873
  • 2
  • 20
  • 36
0
votes
1 answer

Unable to create PostGIS extension

I am working on a CentOS 7 server with PostGIS 3.2 installed. When I try to create the PostGIS extension using CREATE EXTENSION postgis;, I get the following error: ERROR: could not load library "/usr/pgsql-15/lib/postgis-3.so": libgeos_c.so.1:…
Geek2.0
  • 3
  • 2
0
votes
1 answer

On running pgxn install -> Getting ERROR: network error: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed

Installed PGXN using pip install pgxnclient Tried installing the postgres extension pgxn_utils using PGXN client https://pgxn.org/dist/pgxn_utils/0.1.4/ On running the command pgxn install pgxn_utils getting the Error: ERROR: network error: [SSL:…
Ishaan Adarsh
  • 197
  • 1
  • 11
0
votes
1 answer

ERROR: function add(integer, integer) does not exist No function matches the given name and argument types. You might need to add explicit type casts

I have tried various ways to solve this issue: i tried it with the code SELECT add(1, 2); but still getting the same result. I have looked at other stack overflow asnwers but they are not working either. -- Test the add function SELECT add(1::int,…
Ishaan Adarsh
  • 197
  • 1
  • 11
0
votes
1 answer

Regression testing Issue [make installcheck]

For the regression tests: The code for the makefile is: EXTENSION = my_extension DATA = my_extension--1.0.sql REGRESS = my_extension--regress.sql PG_CONFIG ?= pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) Regression testing SQL…
Ishaan Adarsh
  • 197
  • 1
  • 11
0
votes
0 answers

Postgres ShmemHash Extension

I try to develop a little Postgresql shared hash extension, just to store constant values to an key. This is onetime loaded and can be accessed by any connected client. Not really a rocket-science. #include "postgres.h" #include "fmgr.h" #include…
Nik
  • 53
  • 1
  • 9
0
votes
0 answers

How can to use ltree extension without using public schema in Azure hyperscale citus database?

My application database is deployed on Azure citus database which requires ltree extension for one of its schema. Currently the queries from my java application do not work since ltree extension is enabled/works for public schema only. While testing…
Fahad
  • 1
0
votes
0 answers

crosstab function is not accessible to non-postgres users

I've installed tablefuncs via create extension tablefuncs and can use crosstab from the postgres account. However, its not visible/usable to non-postgres users. From postgres account: \dx List of installed…
Werner Reiche
  • 31
  • 4
0
votes
2 answers

Postgresql doesn't working with Citus and pg_stat_statements at the same time

So, I built the PostgreSQL with citus extension in docker. I use the official documentation in citus, then I run this command in the terminal. docker run -d --network citus-network --name citus_coordinator -p 5500:5432 -e…
Gismat Gusein
  • 31
  • 6
0
votes
1 answer

Implement Oracle external table like functionality in Azure managed postgresql

Currently we are using Oracle 19c external table functionality on-prem whereby CSV files are loaded to a specific location on DB server and they get automatically loaded into an oracle external table. The file location is mentioned as part of the…
Jacob
  • 426
  • 3
  • 19
0
votes
2 answers

PostgreSQL C function to get values

I am trying to write a PostgreSQL function in C. My goal is finding minimum value of a list. So, my function will be executed like these: SELECT min_to_max(val) FROM (VALUES(1),(2),(3)) x(val); SELECT min_to_max(val) FROM my_table; Here is my C…
Umut TEKİN
  • 856
  • 1
  • 9
  • 19
0
votes
1 answer

Postgres aws_s3 extention not accepting timestamp input

I have a csv with a timestamp column like this 2021-05-27 11:57:23 but the table_import_from_s3 function (from the aws_s3 postgres extension) keeps giving me this error: ERROR: invalid input syntax for type timestamp: "start_time" Has anyone…
Nelu
  • 16,644
  • 10
  • 80
  • 88
0
votes
0 answers

Install PostgreSQL extension for non-superuser

My app's database has a dedicated user with full access like so; CREATE DATABASE "mydb" WITH OWNER = cloudsqlsuperuser ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF8' LC_CTYPE = 'en_US.UTF8' …
Mr. Developerdude
  • 9,118
  • 10
  • 57
  • 95
0
votes
2 answers

PostgreSQL C Extensions without -Wdeclaration-after-statement

Currently, when I compile my extension I get, warning: ISO C90 forbids mixed declarations and code [-Wdeclaration-after-statement] 57 | uint32 n = fctx->n; PostgreSQL currently uses -Wdeclaration-after-statement during compilation. They set…
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
0
votes
2 answers

How do you install the PostgreSQL tablefunc extension?

I am not able to run the following command in my PostgreSQL server: CREATE EXTENSION tablefunc; When I run this command, I get the following error message: ERROR: could not open extension control file…
Blake Bell
  • 23
  • 6
1 2 3
4
5