Tom Lane <t...@sss.pgh.pa.us> writes: >> Tom Lane <t...@sss.pgh.pa.us> writes: >>> I think it's better to keep it working as a textual substitution.
Thinking about this some more, it has the advantage that the effects of the control file settings are kept within the script file processing and pg_extension catalog. The only backend impact is the dependency tracking. > OK, so with that, attached is an example of the complete conversion diff > for a contrib module (hstore in particular). Although "git status" I see you're not using the @extschema@ placeholder in the upgrade script. It is intentional? It's been common wisdom and practice to edit the SQL file of any contrib or third party module to have it installed in your preferred schema… > reports hstore--1.0.sql as being a rename of hstore.sql.in, "git diff" > doesn't seem to be exceedingly bright about presenting it that way :-(. > But actually the change in that script other than renaming is just > removing the "set search_path" command and adjusting the header comment. And we don't have to rely on hstore.sql.in file anymore as the change is done by the backend side of things. That's a very good point for the windows build system I think. > Barring objections, I'll press on with fixing the rest of them. 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. I took the time to finish this query (filter out array types, some replacement in operator classes and families descriptions) because I think it would be nice to offer it in the docs. It could even be proposed as a function :) I hope you'll find it useful, but it could well be you finished the search&replace of all contribs already (ah, emacs keyboard macros). CREATE EXTENSION hstore; CREATE SCHEMA empty_place; SET search_path TO empty_place; WITH objs AS ( SELECT classid, 'ALTER EXTENSION ' || E.extname || ' ADD ' || replace(pg_describe_object(classid, objid, 0), N.nspname, '@extschema@') || ';' as sql 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 CASE WHEN classid = 'pg_catalog.pg_type'::regclass THEN (SELECT typarray FROM pg_type WHERE oid=objid) != 0 ELSE true END AND deptype = 'e' AND E.extname = 'hstore' ) SELECT CASE WHEN classid IN ('pg_catalog.pg_opclass'::regclass, 'pg_catalog.pg_opfamily'::regclass) THEN replace(sql, 'for access method', 'using') ELSE sql END FROM objs; Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers