Re: Extensions vs PGXS' MODULE_PATHNAME handling

I wrote:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
>> I think you'd be interested into this reworked SQL query. It should be
>> providing exactly the script file you need as an upgrade from unpackaged.

> This seems overly complicated. I have a version of it that I'll publish
> as soon as I've tested it on all the contrib modules ...

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.

#! /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

regards, tom lane