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
0 answers

Postgres C extension parallel aggregate, how to get worker id and row number?

I am writing a Postgres C extension for a Parallel Aggregate function. Normally, in C or C++ I get my thread id and indexing of the work to be done, and then I can make sure the work can be done with no dependency and that the final result is always…
ReverseFlowControl
  • 766
  • 6
  • 30
0
votes
0 answers

Postgres custom parallel aggregation extension in C, shared state?

I am creating a Postgres C extension that is a parallelized aggregate function. Is there any way I can share the state, or any any state/memory-block I choose, among all the workers? I dont want each worker process to have its own copy, I want a…
ReverseFlowControl
  • 766
  • 6
  • 30
0
votes
2 answers

libpqxx C Aggregate Extension returns wrong data?

I am learning how to create C aggregate extensions and using libpqxx with C++ on the client side to process the data. My toy aggregate extension has one argument of type bytea, and the state is also of type bytea. The following is the simplest…
ReverseFlowControl
  • 766
  • 6
  • 30
0
votes
1 answer

How to make an extension not relocatable?

I have an extension: https://github.com/CraigTyle/Mathexp My task is to make the extension not relocatable: it should be possible to install the extension in any schema, but it should be impossible to change that schema. I was told that this is how…
Mark_Flint
  • 34
  • 5
0
votes
1 answer

How to install pgxn with correct PostgreSQL version?

On sudo -H pgxn install semver, ERROR about wrong PostgreSQL version (it is v10 not v12), INFO: best version: semver 0.20.3 INFO: saving /tmp/tmpba6hta5a/semver-0.20.3.zip INFO: unpacking: /tmp/tmpba6hta5a/semver-0.20.3.zip INFO: building…
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
0
votes
2 answers

How to make a Postgres background workers sleep and wake up on a signal?

PostgreSQL makes use of background workers to allow processes working in a concurrent fashion and they have an API for backend/extension developers to control them. So far, I have managed to successfully work with this feature in a demo extension,…
Zeruno
  • 1,391
  • 2
  • 20
  • 39
0
votes
1 answer

ERROR: incompatible library while creating extension in Postgresql

I use Centos 7 and Postgresql 12. I installed already PostGIS and created successfully its extension. I'm now tring to install semver extension with create extension semver and get following error: ERROR: incompatible library…
Micheal Toru
  • 422
  • 4
  • 28
0
votes
1 answer

PostgreSQL parallel queries using SPI possible?

I am using PostgreSQL's Server Programming Interface (SPI) to build my postgres extension and execute my query. Please see this detailed example, or the following simple code sample: int ret = SPI_exec("SELECT * FROM ....", 0); We know that…
Zeruno
  • 1,391
  • 2
  • 20
  • 39
0
votes
0 answers

Installing PGroonga on Windows 10 for PostgreSQL 11

PostgreSQL extension Novice here. So I am trying to install PGroonga on Windows 10 for PostgreSQL 11(already installed) The instructions it telling me to extract the downloaded PGroonga package. And I need to specify PostgreSQL folder as extract…
user8183395
  • 115
  • 1
  • 14
0
votes
1 answer

"CREATE SCHEMA foo ..." query from C extension in PostgreSQL (using SPI_execute_with_args)

I'm trying to execute a SQL query from a C extension built for PostgreSQL, using the Server Programming Interface (SPI). The query should create a new schema with quite a large number of tables. (Basically it should setup a workspace for users to…
BitGen01100000
  • 57
  • 7
0
votes
1 answer

error while creating Postgres extension www_fdw

I want to use www_fdw extension of PostgreSQL in order to read data from web services. I used this command to create extension: CREATE EXTENSION IF NOT EXISTS www_fdw CASCADE but it gives me following error: could not open extension control file…
SillyPerson
  • 589
  • 2
  • 7
  • 30
0
votes
0 answers

Why does the process which creates the postgis extension hang?

While executing the command "create extension postgis;" I see that the psql process hangs indefinitely and the only information that strace gives me is that its stuck in this indefinite futex call futex(0x2ac7aac6928c, FUTEX_WAIT, 1, NULL I can't…
AnkitSablok
  • 3,021
  • 7
  • 35
  • 52
0
votes
1 answer

postgresql function to call webservice

I want to call a webservice from Postgresql function. I have seen this link where it is shown how to make a http request using pgpsql code http://boundlessgeo.com/2012/04/http-for-postgresql/ Also shown On Github…
user4906240
  • 605
  • 1
  • 6
  • 20
0
votes
1 answer

How to write data in a PostgreSQL C extension?

I am writing an extension function in C for PostgreSQL. I can find lots of examples online but nothing that explicitly shows how to actually write data to a table in an extension function? Where do I need to look to find the right…
jramm
  • 6,415
  • 4
  • 34
  • 73
-1
votes
1 answer

Compiling PostgreSQL extension, getting "error: ‘work_mem’ undeclared (first use in this function)"

When I compile the extension I've got, I'm getting error: ‘work_mem’ undeclared (first use in this function) 17 | Tuplestorestate *tupstore = tuplestore_begin_heap(true, false, work_mem); What header includes work_mem?
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
1 2 3 4
5