O comando CREATE EXTENSION depende de um arquivo de controle para cada extensão, que deve ter o mesmo nome da extensão e o sufixo .control, e deve ser colocado no diretório da instalação $SHAREDIR/extension. Também deve haver pelo menos um arquivo de script SQL, que segue o padrão de nomenclatura extensão--versão.sql (por exemplo, foo--1.0.sql para a versão 1.0 da extensão foo). Por padrão, os arquivos de script também são colocados no diretório $SHAREDIR/extension; mas o arquivo de controle pode especificar um diretório diferente para o(s) arquivo(s) de script.

Podem ser configurados locais adicionais para arquivos de controle de extensão usando o parâmetro extension_control_path.

O formato de arquivo para um arquivo de controle de extensão é o mesmo do arquivo postgresql.conf, ou seja, uma lista de atribuições nome_do_parâmetro = valor, uma por linha. São permitidas linhas em branco e comentários começados por #. Certifique-se de delimitar qualquer valor que não seja uma única palavra ou número.

O arquivo de controle pode definir os seguintes parâmetros:

Além do arquivo de controle primário extensão.control, a extensão pode ter arquivos de controle secundários com nomes no estilo extensão--versão.control. Se fornecidos, devem estar localizados no diretório do arquivo de script. Os arquivos de controle secundários seguem o mesmo formato do arquivo de controle primário. Quaisquer parâmetros definidos no arquivo de controle secundário substituem os do arquivo de controle primário, ao instalar ou atualizar para essa versão da extensão. Entretanto, os parâmetros directory e default_version não podem ser definidos em arquivos de controle secundários.

Os arquivos de script SQL de uma extensão podem conter qualquer comando SQL, exceto comandos para controle de transação (BEGIN, COMMIT, etc.) e comandos que não podem ser executados dentro de um bloco de transação (como VACUUM). Isso se dá porque os arquivos de script são executados implicitamente em um bloco de transação.

Os arquivos de script SQL de uma extensão também podem conter linhas começando com \echo, que são ignoradas (tratadas como comentários) pelo mecanismo de extensão. Essa provisão é comumente usada para lançar um erro se o arquivo de script for alimentado para o psql, em vez de ser carregado via CREATE EXTENSION (veja o script de exemplo em Exemplo de extensão). Sem isso, os usuários podem acidentalmente carregar o conteúdo da extensão como objetos “soltos”, em vez de em uma extensão, um estado de coisas que é um tanto entediante para se recuperar dele.

Se o script de extensão contiver a cadeia de caracteres @extowner@, essa cadeia de caracteres será substituída pelo nome (adequadamente delimitado) do usuário que está chamando CREATE EXTENSION ou ALTER EXTENSION. Normalmente, esse recurso é usado por extensões marcadas como confiáveis, para atribuir a propriedade dos objetos selecionados ao usuário que efetua a chamada, em vez do superusuário de inicialização. (No entanto, deve-se ter cuidado ao fazer isso. Por exemplo, atribuir como dono de uma função na linguagem C um não superusuário criaria um caminho de escalonamento de privilégio para esse usuário.)

Embora os arquivos de script possam conter quaisquer caracteres permitidos pela codificação especificada, os arquivos de controle devem conter apenas caracteres ASCII simples, porque não há como o PostgreSQL saber em qual codificação um arquivo de controle está. Na prática, isso só é um problema se for desejado usar caracteres não ASCII no comentário da extensão. A prática recomendada nesse caso é não usar o parâmetro comment do arquivo de controle, e sim usar COMMENT ON EXTENSION em um arquivo de script para definir o comentário.

Os usuários geralmente desejam carregar os objetos contidos em uma extensão em um esquema diferente do que o autor da extensão tinha em mente. Existem três níveis de realocabilidade com suporte:

Em todos esses casos, o arquivo de script será executado com search_path inicialmente definido apontando para o esquema de destino; ou seja, CREATE EXTENSION faz o equivalente a:

SET LOCAL search_path TO @extschema@, pg_temp;

Isso permite os objetos criados pelo arquivo de script irem para o esquema de destino. O arquivo de script pode mudar search_path se quiser, mas isso é geralmente indesejável. O search_path é restaurado para sua configuração anterior após a conclusão de CREATE EXTENSION.

O esquema de destino é determinado pelo parâmetro schema no arquivo de controle, se for fornecido, caso contrário pela opção SCHEMA do comando CREATE EXTENSION, se estiver presente, e caso contrário o esquema de criação de objeto padrão corrente (o primeiro no search_path do usuário que efetua a chamada). Quando é usado o parâmetro schema do arquivo de controle, o esquema de destino será criado se ainda não existir, mas nos outros dois casos o esquema já deve existir.

Se alguma extensão que seja pré-requisito estiver listada no parâmetro requires no arquivo de controle, seus esquemas de destino serão adicionados à configuração inicial de search_path, seguindo o novo esquema de destino da extensão. Isso permite que seus objetos fiquem visíveis para o arquivo de script da nova extensão.

Por segurança, pg_temp é anexado automaticamente ao final de search_path em todos os casos.

Embora uma extensão não realocável possa conter objetos espalhados por vários esquemas, é geralmente desejável colocar todos os objetos destinados ao uso externo em um único esquema, considerado o esquema de destino da extensão. Esse arranjo funciona convenientemente com a configuração padrão de search_path durante a criação de extensões dependentes.

Se uma extensão fizer referência a objetos pertencentes a outra extensão, recomenda-se qualificar essas referências de acordo com o esquema. Para fazer isso, deve ser escrito @extschema:nome@ no arquivo de script da extensão, onde nome é o nome da outra extensão (que deve ser listado na lista requires desta extensão). Essa sequência de caracteres será substituída pelo nome (entre aspas, se necessário) do esquema de destino dessa extensão. Embora essa notação evite a necessidade de fazer suposições fixadas (hard-wired) sobre os nomes de esquema no arquivo de script da extensão, seu uso pode incorporar o nome do esquema da outra extensão nos objetos instalados desta extensão. (Normalmente isso acontece quando @extschema:nome@ é usado dentro de uma cadeia de caracteres literal, como o corpo de uma função ou uma configuração de search_path. Em outros casos, a referência ao objeto é reduzida a um OID durante a análise sintática e não requer pesquisas subsequentes.) Se o nome do esquema da outra extensão estiver incorporado dessa forma, deve-se impedir que a outra extensão seja realocada após a instalação desta extensão, adicionando o nome da outra extensão à lista no_relocate desta extensão.

Algumas extensões incluem tabelas de configuração, contendo dados que podem ser adicionados ou alterados pelo usuário após a instalação da extensão. Normalmente, se uma tabela faz parte de uma extensão, nem a definição da tabela, nem seu conteúdo, serão copiados pelo pg_dump. Mas esse comportamento não é desejado para uma tabela de configuração; quaisquer alterações de dados feitas pelo usuário precisam ser incluídas nas descargas, ou a extensão se comportará de maneira diferente após uma exportação e restauração (dump/restore).

Para resolver esse problema, o arquivo de script de uma extensão pode marcar uma tabela, ou uma sequência que ela criou, como uma relação de configuração, o que fará com que a aplicação pg_dump inclua o conteúdo da tabela ou da sequência (não a sua definição) nas cópias de segurança. Para fazer isso, deve ser chamada a função pg_extension_config_dump(regclass, text) após a tabela ou a sequência ser criada. Por exemplo:

CREATE TABLE my_config (key text, value text);
CREATE SEQUENCE my_config_seq;

SELECT pg_catalog.pg_extension_config_dump('my_config', '');
SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');

Qualquer número de tabelas ou sequências pode ser marcado dessa maneira. As sequências associadas às colunas serial ou bigserial também podem ser marcadas.

Quando o segundo argumento da função pg_extension_config_dump for uma cadeia de caracteres vazia, todo o conteúdo da tabela é copiado por pg_dump. Isso geralmente só é correto se a tabela estiver inicialmente vazia quando criada pelo script de extensão. Se houver uma mistura de dados iniciais e dados fornecidos pelo usuário na tabela, o segundo argumento da função pg_extension_config_dump fornece uma condição WHERE que seleciona os dados a serem descarregados. Por exemplo, pode ser feito

CREATE TABLE my_config (key text, value text, standard_entry boolean);

SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');

para certificar-se de que standard_entry seja verdade apenas nas linhas criadas pelo script da extensão.

Para as sequências, o segundo argumento da função pg_extension_config_dump não tem efeito.

Situações mais complicadas, como linhas fornecidas inicialmente que podem ser modificadas pelos usuários, podem ser tratadas criando gatilhos na tabela de configuração para garantir que as linhas modificadas sejam marcadas corretamente.

Pode ser alterada a condição do filtro associada a uma tabela de configuração chamando a função pg_extension_config_dump novamente. (Isso normalmente seria útil em um script de atualização de extensão.) A única maneira de marcar uma tabela como não sendo mais uma tabela de configuração é dissociá-la da extensão com o comando ALTER EXTENSION ... DROP TABLE.

Note que os relacionamentos de chaves estrangeiras entre essas tabelas ditarão a ordem na qual as tabelas serão descarregadas pelo pg_dump. Especificamente, pg_dump tentará descarregar a tabela referenciada antes da tabela que faz a referência. Como os relacionamentos de chave estrangeira são configurados no momento do comando CREATE EXTENSION (antes dos dados serem carregados nas tabelas) não há suporte para dependências circulares. Quando existirem dependências circulares, os dados ainda serão descarregados, mas a descarga não poderá ser recuperada diretamente, sendo necessária intervenção do usuário.

As sequências associadas às colunas serial ou bigserial precisam ser marcadas diretamente para exportar o seu estado. Marcar sua relação mãe não é suficiente para esse propósito.

Uma vantagem do mecanismo de extensão é o fornecimento de maneiras convenientes de gerenciar as atualizações para os comandos SQL que definem os objetos de uma extensão. Isso é feito associando um nome ou número de versão a cada versão lançada do script de instalação da extensão. Além disso, se for desejado que os usuários possam atualizar seus bancos de dados dinamicamente de uma versão para outra, devem ser fornecidos scripts de atualização que façam as alterações necessárias para ir de uma versão para outra. Os scripts de atualização têm nomes seguindo o padrão extensão--versão_antiga--versão_de_destino.sql (por exemplo, foo--1.0--1.1.sql contém os comandos para modificar a versão 1.0 da extensão foo para a versão 1.1).

Uma vez que um script de atualização adequado está disponível, o comando ALTER EXTENSION UPDATE atualizará a extensão instalada para a nova versão especificada. O script de atualização é executado no mesmo ambiente que o comando CREATE EXTENSION fornece para scripts de instalação: em particular, search_path é configurado da mesma forma, e quaisquer novos objetos criados pelo script são adicionados automaticamente à extensão. Além disso, se o script optar por excluir objetos membro da extensão, eles serão automaticamente dissociados da extensão.

Se a extensão tiver arquivos de controle secundários, os parâmetros de controle usados para o script de atualização serão aqueles associados à versão de destino (nova) do script.

O comando ALTER EXTENSION pode executar sequências de arquivos de script de atualização para obter a atualização solicitada. Por exemplo, se estirem disponíveis apenas foo--1.0--1.1.sql e foo--1.1--2.0.sql, o comando ALTER EXTENSION irá aplicá-los em sequência, se a atualização para a versão 2.0 for solicitada quando a 1.0 estiver instalada.

O PostgreSQL não assume nada sobre as propriedades dos nomes das versões: por exemplo, não sabe se 1.1 segue 1.0. Ele apenas corresponde aos nomes das versões disponíveis, seguindo o caminho que requer a aplicação do menor número de scripts de atualização. (Um nome de versão pode, na verdade, ser qualquer cadeia de caracteres que não contenha os caracteres --, ou o caractere - à esquerda ou à direita.)

Às vezes é útil fornecer scripts de “reversão” (downgrade), por exemplo, foo--1.1--1.0.sql para permitir a reversão das alterações associadas à versão 1.1. Se isso for feito, deve-se tomar cuidado com a possibilidade de um script de reversão ser aplicado inesperadamente, porque gera um caminho mais curto. O caso de risco é quando há um script de atualização “rápido” que avança várias versões, bem como um script de reversão para o ponto inicial do caminho rápido. Podem ser necessárias menos etapas para aplicar a reversão e, em seguida, aplicar o caminho rápido, do que avançar uma versão por vez. Se o script de reversão excluir quaisquer objetos insubstituíveis, isso produzirá resultados indesejáveis.

Para verificar caminhos de atualização inesperados, use o comando:

SELECT * FROM pg_extension_update_paths('nome_da_extensão');

Esse comando mostra cada par de nomes de versão conhecidos distintos para a extensão especificada, junto com a sequência de caminho de atualização que seria usada para ir da versão de origem para a versão de destino, ou NULL, caso não haja caminho de atualização disponível. O caminho é mostrado em forma de texto com separadores --. Pode ser usado regexp_split_to_array(path,'--') se for preferido um formato de matriz.

Uma extensão que já existe há algum tempo provavelmente existirá em várias versões, para as quais o autor precisará escrever scripts de atualização. Por exemplo, se foi lançada a extensão foo nas versões 1.0, 1.1, e 1.2, devem existir os scripts de atualização foo--1.0--1.1.sql e foo--1.1--1.2.sql. Antes do PostgreSQL 10, também era necessário criar os novos arquivos de script foo--1.1.sql e foo--1.2.sql que construíam diretamente as versões mais recentes da extensão, ou então as versões mais recentes não poderiam ser instaladas diretamente, apenas instalando a 1.0 e depois atualizando. Isso era entediante e duplicado, mas agora é desnecessário, porque o comando CREATE EXTENSION pode seguir as cadeias de atualização automaticamente. Por exemplo, se apenas os arquivos de script foo--1.0.sql, foo--1.0--1.1.sql, e foo--1.1--1.2.sql estiverem disponíveis, uma solicitação para instalar a versão 1.2 será honrada executando esses três scripts em sequência. O processamento é o mesmo como se tivesse sido instalado primeiro a 1.0, e depois atualizado para 1.2. (Como em ALTER EXTENSION UPDATE, se estiverem disponíveis vários caminhos, o mais curto será o preferido.) Organizar os arquivos de script de uma extensão nesse estilo pode reduzir a quantidade de esforço de manutenção necessária para produzir pequenas atualizações.

Se forem usados arquivos de controle secundários (específicos da versão) com uma extensão mantida nesse estilo, lembre-se de que cada versão precisa de um arquivo de controle, mesmo que não tenha um script de instalação autônomo, porque esse arquivo de controle determinará como a atualização implícita para essa versão é executada. Por exemplo, se foo--1.0.control especificar requires = 'bar', mas os outros arquivos de controle de foo não, a dependência da extensão em bar será descartada ao atualizar de 1.0 para outra versão.

As extensões amplamente distribuídas devem presumir pouco sobre o banco de dados onde residem. Portanto, é apropriado escrever funções fornecidas por uma extensão em um estilo seguro, que não possa ser comprometido por ataques baseados em caminhos de procura.

Uma extensão com a propriedade superuser definida como verdade, também deve considerar os riscos de segurança para as ações executadas em seus scripts de instalação e atualização. Não é muito difícil para um usuário mal-intencionado criar objetos Cavalo de Troia que vão comprometer a execução posterior de um script de extensão escrito de forma descuidada, permitindo que o usuário adquira privilégios de superusuário.

Se uma extensão estiver marcada como trusted, então seu esquema de instalação pode ser selecionado pelo usuário que está instalando, que pode intencionalmente usar um esquema inseguro na esperança de obter privilégios de superusuário. Portanto, uma extensão confiável é extremamente exposta do ponto de vista da segurança, e todos os seus comandos de script devem ser cuidadosamente examinados para garantir que nenhum comprometimento seja possível.

São fornecidos conselhos sobre como escrever funções seguras em Considerações sobre segurança para funções de extensão abaixo, e conselhos sobre como escrever scripts de instalação seguros são fornecidos em Considerações sobre segurança para scripts de extensão.

A seguir está um exemplo completo de uma extensão escrita somente em SQL, contendo um tipo de dados composto por dois elementos, que pode armazenar valor de qualquer tipo de dados em seus encaixes (slots), denominados “k” e “v”. Os valores não textuais são convertidos automaticamente para texto para armazenamento.

O arquivo de script pair--1.0.sql tem a seguinte aparência:

-- reclamar se o script for executado no psql, em vez de via CREATE EXTENSION
\echo Use "CREATE EXTENSION pair" para carregar esse arquivo. \quit

CREATE TYPE pair AS ( k text, v text );

CREATE FUNCTION pair(text, text)
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';

CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);

-- "SET search_path" é fácil de dar certo, mas nomes qualificados funcionam melhor.
CREATE FUNCTION lower(pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'
SET search_path = pg_temp;

CREATE FUNCTION pair_concat(pair, pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,
               $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';

O arquivo de controle pair.control se parece com:

# extensao pair
comment = 'Tipo de dados de par chave/valor'
default_version = '1.0'
# não pode ser realocável devido ao uso de @extschema@
relocatable = false

Embora dificilmente seja necessário um arquivo construtor para instalar esses dois arquivos no diretório correto, pode ser usado um arquivo de construção Makefile contendo:

EXTENSION = pair
DATA = pair--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Esse arquivo de construção depende de PGXS, que é descrito em Infraestrutura para construção de extensão. O comando make install irá instalar os arquivos de controle e script no diretório correto, conforme informado por pg_config.

Depois que os arquivos estiverem instalados, deve ser usado o comando CREATE EXTENSION para carregar os objetos em qualquer banco de dados específico.