How to make an update extension

Viewed 154

Let consider a extension formed with this function:

CREATE OR REPLACE FUNCTION public.foo()
  RETURNS void AS
$BODY$
BEGIN
RAISE NOTICE 'foo v.1.0';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;  

This is myextension--1.0.sql

And this is myextension.control

# extension prueba
comment = 'prueba para updatear extensiones'
default_version = '1.0'
module_pathname = '$libdir/unaccent'
relocatable = true

Doing

CREATE EXTENSION myextension

All is all right.

Now, I need to update this function and add another one.

It will be myextension--2.0.sql

CREATE OR REPLACE FUNCTION public.foo()
  RETURNS void AS
$BODY$
BEGIN
RAISE NOTICE 'foo v.2.0';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;   

CREATE OR REPLACE FUNCTION public.bar()
  RETURNS void AS
$BODY$
BEGIN
RAISE NOTICE 'bar v.1.0';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;   

Then, I don't know how I must to prepare it for get an updated extension. I have been watching any extensions installed with postgres, like unnaccent but I don't understand the mechanism of update. Looks as if unaccent--1.1.sql has all the functions (the new ones and the updated functions) and unaccent--1.0--1.1.sql has the instructions for update the existing functions located in unaccent--1.1.sql

Thank you.

UPDATE

Following the answers, I have created myextension--1.0--2.0.sql with those statements:

ALTER FUNCTION foo() PARALLEL SAFE;

Also, I've changed the default version in myextension.control

default_version = '2.0'

But if I do:

ALTER EXTENSION myextension UPDATE;

Postgres tell me that I have made a succesfull updated, and I have 2.0 version, but neither foo() is updated nor bar() is created.

1 Answers

unaccent--1.0--1.1.sql has the SQL statements to update version 1.0 installed by unaccent--1.0.sql.

CREATE EXTENSION automatically finds a sequence of SQL scripts to run to install the desired version. If there is no "base" SQL script that installs the desired version, it will start with a lower versio and then apply "delta" scripts until it has the desired version.

Related