Re: Extensions vs PGXS' MODULE_PATHNAME handling

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Just for the archives' sake: the '@extschema@' business did turn out to
> be important, at least for tsearch2 where it's necessary to distinguish
> the objects it's dealing with from similarly-named objects in
> pg_catalog.  So this is what I used to generate the "unpackaged"
> scripts.  Some of them needed manual adjustment later to cover cases
> where 9.1 had diverged from 9.0, but the script could hardly be expected
> to know about that.

Good to know that even contrib needs that!

> #! /bin/sh
>
> MOD="$1"
>
> psql -d testdb -c "create extension $MOD"
>
> (
> echo "/* contrib/$MOD/$MOD--unpackaged--1.0.sql */"
> echo
>
> psql -A -t -d testdb -c "
>   SELECT 'ALTER EXTENSION ' || E.extname || ' ADD '
>       || replace(pg_describe_object(classid, objid, 0),
>                  N.nspname, '@extschema@')
>       || ';'
>     FROM pg_depend D
>          JOIN pg_extension E ON D.refobjid = E.oid
>                             AND D.refclassid = E.tableoid
>          JOIN pg_namespace N ON E.extnamespace = N.oid
>   WHERE deptype = 'e' AND E.extname = '$MOD'
>   ORDER BY D.objid
> " | sed -e 's/ADD cast from \(.*\) to \(.*\);/ADD cast (\1 as \2);/' \
>     -e 's/ for access method / using /'
> ) > contrib/$MOD/$MOD--unpackaged--1.0.sql

Ah well sed makes it simpler to read, but it won't be usable in windows.
I now realize also that the second version of this query did some
useless array type filtering.  Adding a replace() step in the query
would not be that ugly I guess, if we wanted to make it so.

Do we want to add such a query in the docs to help pgfoundry authors to
write their own 'from unpackaged' scripts?

CREATE OR REPLACE FUNCTION extension_unpackaged_upgrade_script(text) RETURNS SETOF text LANGUAGE SQL
AS $$
WITH objs AS ( SELECT 'ALTER EXTENSION ' || E.extname || ' ADD '     || replace(pg_describe_object(classid, objid, 0),
             N.nspname, '@extschema@')     || ';' AS d   FROM pg_depend D        JOIN pg_extension E ON D.refobjid =
E.oid                          AND D.refclassid = E.tableoid        JOIN pg_namespace N ON E.extnamespace = N.oid WHERE
deptype= 'e' AND E.extname = $1 ORDER BY D.objid
 
)
SELECT regexp_replace(replace(d, ' for access method ', ' using '),                     'ADD cast from (.*) to (.*);',
                  E'ADD cast (\\1 as \\2);') FROM objs
 
$$;


dim=# select * from extension_unpackaged_upgrade_script('lo');                extension_unpackaged_upgrade_script
        
 
---------------------------------------------------------------------ALTER EXTENSION lo ADD type @extschema@.lo;ALTER
EXTENSIONlo ADD function @extschema@.lo_oid(@extschema@.lo);ALTER EXTENSION lo ADD function @extschema@.lo_manage();
 
(3 rows)

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support