Tom Lane <> writes:
>> Tom Lane <> 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

> 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, "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 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 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
          AND deptype = 'e' AND E.extname = 'hstore'
    CASE WHEN classid IN ('pg_catalog.pg_opclass'::regclass,
         THEN replace(sql, 'for access method', 'using')
         ELSE sql
    FROM objs;

Dimitri Fontaine     PostgreSQL : Expertise, Formation et Support

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to