Extension Function and Views

[aahmad]

Excellent gem and upgraded to Rails 5.2 so all seems to be working well. I was wondering if there is a way for the rake db:migrate not to export the functions? For example, we use extensions and the db/schema.rb file rightfully has this:

create_extension "postgis", :version => "2.4.3"

However, within the db/schema.rb file are the functions generated from that extension (definitions for __st_countagg_transfn, updategeometrysrid, and others). The extension will already install these functions and therefore when running bin/rake db:schema:load, errors are produced. Same with any views that came from extensions (these will try to recreate them, causing errors).

The query in place to change is https://github.com/HornsAndHooves/pg_saurus/blob/master/lib/pg_saurus/connection_adapters/postgresql_adapter/function_methods.rb#L14-L30

Changing it to:

      SELECT n.nspname AS "Schema",
        p.proname AS "Name",
        pg_catalog.pg_get_function_result(p.oid) AS "Returning",
       CASE
        WHEN p.proiswindow                                           THEN 'window'
        WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
        ELSE 'normal'
       END   AS "Type",
       p.oid AS "Oid"
      FROM pg_catalog.pg_proc p
           LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
           LEFT JOIN pg_depend d ON d.objid = p.oid AND d.deptype = 'e'
           LEFT JOIN pg_catalog.pg_language l ON p.prolang = l.oid
      WHERE pg_catalog.pg_function_is_visible(p.oid)
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND p.proisagg <> TRUE
            AND d.objid IS NULL
      ORDER BY 1, 2, 3, 4;

does the trick, however, wondering if there is an alternative.

Is there any way to shut off the function and view exports from extensions?

Thank you!

[albertosaurus]

Hmm... We'll look into it. Your proposal may very well turn out to be the Right Thing.

[aahmad]

Thank you. Yes, the extension will create those functions anyhow. Maybe if there’s some way for options to be passed, to run the alternative queries?