Re: Extensions vs PGXS' MODULE_PATHNAME handling

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)(dot)lo;
ALTER EXTENSION lo ADD function @extschema(at)(dot)lo_oid(@extschema(at)(dot)lo);
ALTER EXTENSION lo ADD function @extschema(at)(dot)lo_manage();
(3 rows)

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