{"id":16518,"date":"2021-07-02T13:44:16","date_gmt":"2021-07-02T11:44:16","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/"},"modified":"2021-07-02T13:44:16","modified_gmt":"2021-07-02T11:44:16","slug":"creating-simple-extensions-for-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/","title":{"rendered":"Creating simple extensions for PostgreSQL"},"content":{"rendered":"

When you are using PostgreSQL you should already know that PostgreSQL comes with a set of extensions by default<\/a>. It might be that you need to install an additional package if you installed PostgreSQL with a package manager to get those extensions. Usually it is called something with “contrib” in its name. There is also the PostgreSQL Extension Network<\/a> which list a lot of external extensions that might be useful for you. If you can’t find what you’re looking for, you can still write your own extension. How to do that is the topic of this post: We’ll create a very simple extension for PostgreSQL.<\/p>\n

<\/p>\n

Lets assume you always need a table containing the list of countries with a few properties like this:<\/p>\n

\npostgres=# create table countries ( id int primary key, name text, alpha2 text, alpha3 text );\nCREATE TABLE\npostgres=# copy countries \n           from program 'curl https:\/\/raw.githubusercontent.com\/stefangabos\/world_countries\/master\/data\/en\/countries.csv'\n           with (header true, delimiter ',', format csv);\nCOPY 193\n<\/pre>\n
This gives as 193 countries and each country has a few properties:<\/p>\n
\npostgres=# select * from countries limit 5;\n id |    name     | alpha2 | alpha3 \n----+-------------+--------+--------\n  4 | Afghanistan | af     | afg\n  8 | Albania     | al     | alb\n 12 | Algeria     | dz     | dza\n 20 | Andorra     | ad     | and\n 24 | Angola      | ao     | ago\n(5 rows)\n<\/pre>\n
Packaging this into an extension is quite easy. You need a so called control file that provides some basic information about your extension. In my case it looks like this and I’ve placed it where all the other extensions are as well:<\/p>\n
\npostgres@debian11:\/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\/ [pg15] pwd\n\/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\npostgres@debian11:\/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\/ [pg15] cat my_cool_extension.control\n# my cool extension control file\ncomment = 'my cool extension for providing a table with countries'\ndefault_version = '1.0'\nrelocatable = false\ntrusted = true\n<\/pre>\n
Now we need the table and the data and this goes into a simple SQL file:<\/p>\n
\npostgres@debian11:\/home\/postgres\/ [pg15] pg_dump --column-inserts --table=countries postgres > \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\/my_cool_extension--1.0.sql\npostgres@debian11:\/home\/postgres\/ [pg15] psql -c \"drop table countries\" postgres\n<\/pre>\n
If you look at the default extensions they all have the first two lines of the SQL files like e.g. this:<\/p>\n
\n-- complain if script is sourced in psql, rather than via CREATE EXTENSION\necho Use \"CREATE EXTENSION ltree_plpython2u\" to load this file. quit\n<\/pre>\n
This prevents that the file can directly be piped into psql and we should do the same. Only “create extension” should be used to create extensions:<\/p>\n
\npostgres@debian11:\/home\/postgres\/ [pgdev] sed -i '1s\/^\/-- complain if script is sourced in psql, rather than via CREATE EXTENSION\/' \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\/my_cool_extension--1.0.sql\npostgres@debian11:\/home\/postgres\/ [pgdev] sed -i '2s\/^\/\\echo Use \"CREATE EXTENSION my_cool_extension\" to load this file. \\quitn\/' \/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\/my_cool_extension--1.0.sql\n<\/pre>\n
That’s all you need to do. Having these two files in place the extension is recognized by PostgreSQL:<\/p>\n
\npostgres=# select * from pg_available_extensions where comment like 'my cool%';\n       name        | default_version | installed_version |                       comment                        \n-------------------+-----------------+-------------------+------------------------------------------------------\n my_cool_extension | 1.0             |                   | my cool extension for providing a table of countries\n(1 row)\n<\/pre>\n
Now we can install it as usual:<\/p>\n
\npostgres=# create extension my_cool_extension;\nCREATE EXTENSION\npostgres=# dx\n                                  List of installed extensions\n       Name        | Version |   Schema   |                     Description                      \n-------------------+---------+------------+------------------------------------------------------\n my_cool_extension | 1.0     | public     | my cool extension for providing a table of countries\n plpgsql           | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(2 rows)\npostgres=# select * from countries limit 5;\n id |    name     | alpha2 | alpha3 \n----+-------------+--------+--------\n  4 | Afghanistan | af     | afg\n  8 | Albania     | al     | alb\n 12 | Algeria     | dz     | dza\n 20 | Andorra     | ad     | and\n 24 | Angola      | ao     | ago\n(5 rows)\n<\/pre>\n
Over time you might need to update your extension so lets assume in our case that there is a new country. What you need to do is create a new SQL file that does the necessary changes and update the default version in the control file:<\/p>\n
\npostgres@debian11:\/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\/ [pgdev] pwd\n\/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\npostgres@debian11:\/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\/ [pgdev] cat my_cool_extension--1.0--1.1.sql\n-- complain if script is sourced in psql, rather than via ALTER EXTENSION\necho Use \"ALTER EXTENSION my_cool_extension UPDATE TO '1.1'\" to load this file. quit\n-- the list of new countries\ninsert into countries (id,name,alpha2,alpha3) values (-1,'my new country','aaa','aaa');\n\npostgres@debian11:\/u01\/app\/postgres\/product\/DEV\/db_1\/share\/extension\/ [pgdev] grep default my_cool_extension.control \ndefault_version = '1.1'\n<\/pre>\n
Afterwards update the extension using alter extension:<\/p>\n
\npostgres=# select * from pg_available_extensions where name = 'my_cool_extension';\n       name        | default_version | installed_version |                       comment                        \n-------------------+-----------------+-------------------+------------------------------------------------------\n my_cool_extension | 1.1             | 1.0               | my cool extension for providing a table of countries\n(1 row)\n\npostgres=# alter extension my_cool_extension update;\nALTER EXTENSION\npostgres=# select count(*) from countries where id = -1;\n count \n-------\n     1\n(1 row)\n<\/pre>\n
All fine, ready to go.<\/p>\n","protected":false},"excerpt":{"rendered":"
When you are using PostgreSQL you should already know that PostgreSQL comes with a set of extensions by default. It might be that you need to install an additional package if you installed PostgreSQL with a package manager to get those extensions. Usually it is called something with “contrib” in its name. There is also […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-16518","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nCreating simple extensions for PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating simple extensions for PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"When you are using PostgreSQL you should already know that PostgreSQL comes with a set of extensions by default. It might be that you need to install an additional package if you installed PostgreSQL with a package manager to get those extensions. Usually it is called something with “contrib” in its name. There is also […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-07-02T11:44:16+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Creating simple extensions for PostgreSQL\",\"datePublished\":\"2021-07-02T11:44:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/\"},\"wordCount\":333,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/\",\"name\":\"Creating simple extensions for PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-07-02T11:44:16+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/creating-simple-extensions-for-postgresql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating simple extensions for PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Creating simple extensions for PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Creating simple extensions for PostgreSQL","og_description":"When you are using PostgreSQL you should already know that PostgreSQL comes with a set of extensions by default. It might be that you need to install an additional package if you installed PostgreSQL with a package manager to get those extensions. Usually it is called something with “contrib” in its name. There is also […]","og_url":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2021-07-02T11:44:16+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Creating simple extensions for PostgreSQL","datePublished":"2021-07-02T11:44:16+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/"},"wordCount":333,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/","name":"Creating simple extensions for PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-07-02T11:44:16+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/creating-simple-extensions-for-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Creating simple extensions for PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16518"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16518\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16518"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}