I've been pretty good at writing release notes in the version bump commit, so migrating to a changelog file should be easy enough to do.

I don't know what then naming convention is of these files, but something conventional.

In the app I work on we have quite a lot of functions and it is becoming very messy and difficult to maintain. It would be great if the gem allowed me to specify a subfolder that a function belongs to so that I can place functions into subfolders to categorise them. Ideally this could be done by adding an option to the create_function method to specify the subfolder name.

Hi! Thanks for all your work on this gem. We ran into an issue where the schema dumper would occasionally dump the functions in a different order, resulting in a diff that didn't really reflect any changes. Our workaround is something like this:

# This monkey patches the F(x) gem so that the functions are always dumped in
# alphabetical order by name, preventing schema diffs when nothing has actually changed.
# Original code: https://github.com/teoljungberg/fx/blob/master/lib/fx/schema_dumper/function.rb
module Fx
  module SchemaDumper
    module Function
      def functions(stream)
        dumpable_functions_in_database.sort_by(&:name).each do |function|
          stream.puts(function.to_schema)
        end
      end
    end
  end
end

It could also be done in the dumpable_functions_in_database method though. If other people have this issue or if you think it would be helpful, I'd be happy to open a PR along these lines.

See #122, once activerecord is warning free this should be done and dealt with.

We start using Fx on a database using PostGIS.

When performing rails db:schema:dump, fx is dumping a trigger from the topology extension:

ActiveRecord::Schema.define(version: 2022_11_29_082047) do
  enable_extension "postgis"
  enable_extension "postgis_topology"

(...)

  create_trigger :layer_integrity_checks, sql_definition: <<-SQL
      CREATE TRIGGER layer_integrity_checks BEFORE DELETE OR UPDATE ON topology.layer FOR EACH ROW EXECUTE FUNCTION topology.layertrigger()
  SQL
end

After then, any rails db:schema:load will fail.

$ rails db:schema:load
rails aborted!
ActiveRecord::StatementInvalid: PG::DuplicateObject: ERROR:  trigger "layer_integrity_checks" for relation "layer" already exists

The trigger is dumped by the statement defined in (..)/adapters/postgres/triggers.rb :

# SELECT * FROM pg_trigger WHERE pg_trigger.tgname NOT ILIKE '%constraint%';
  oid  | tgrelid | tgparentid |         tgname         | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
-------+---------+------------+------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+------------+------------
 46987 |   46972 |          0 | layer_integrity_checks |  46971 |     27 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
(1 row)

I tried but couldn't find a way to link triggers to namespaces to include only "public" triggers as is done for functions.

The gem currently doesn't have a wiki, meaning that users have to figure out how to use the gem from:

• the README;
• blog posts about the gem (if there are any);
• the code.

It would be amazing to have some wiki pages explaining the different things that can be achieved with the gem, and the different options that can be used in each situation.

Nothing drastic has changed, but for safety's sake I'd like to be able to easily add new Ruby versions to build matrix as the are shipped.

I think the right way to set up triggers with fx is like: (after running fx:function and then fx:trigger generators)

db:
  functions:
    - do_the_thing_on_insert_v01.sql  # `CREATE OR REPLACE FUNCTION ...`
  triggers:
    - do_the_thing_on_insert_v01.sql  # `CREATE TRIGGER do_the_thing_on_insert AFTER ...`

then after running rails g fx:function do_the_thing_on_insert we have:

db:
  functions:
    - do_the_thing_on_insert_v01.sql  # `CREATE OR REPLACE FUNCTION ...`
    - do_the_thing_on_insert_v02.sql  # `CREATE OR REPLACE FUNCTION ...`
  triggers:
    - do_the_thing_on_insert_v01.sql  # `CREATE TRIGGER do_the_thing_on_insert AFTER ...`

with the migration:

class UpdateFunctionDoTheThingOnInsertToVersion2 < ActiveRecord::Migration[6.1]
  def change
    update_function :do_the_thing_on_insert, version: 2, revert_to_version: 1
  end
end

that looks mb okay, but running it gives the error:

ActiveRecord::StatementInvalid: PG::DependentObjectsStillExist: ERROR:  cannot drop function do_the_thing_on_insert() because other objects depend on it
DETAIL:  trigger do_the_thing_on_insert on table things depends on function do_the_thing_on_insert()
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

I was hoping that the CREATE OR REPLACE FUNCTION thing would just work out, but apparently not. So the workaround I initially came up with was:

class UpdateFunctionDoTheThingOnInsertToVersion2 < ActiveRecord::Migration[6.1]
  def change
    drop_trigger :do_the_thing_on_insert, on: :things, revert_to_version: 1
    update_function :do_the_thing_on_insert, version: 2, revert_to_version: 1
    create_trigger :do_the_thing_on_insert, on: :things, version: 1
    raise "if we get here, the migration worked but don't commit the result, yet"
  end
end

however, this doesn't feel great, and only migrates correctly in the fwd direction. in the reverse I get:

-- drop_trigger(:do_the_thing_on_insert, {:on=>:things, :version=>2})
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

wrong number of arguments (given 2, expected 1; required keyword: on)

I can go the whole

reversible do |dir|
  dir.up do; end
  dir.down do; end
end

But IDK if that's the intended way to do this sort of thing with fx. I had a look at other issues but didn't find anything.

Is there a better way to do this sort of thing? (or am I doing something wrong and can avoid this by doing something different?)

When trying to run a simple generate command based on the README

rails generate fx:trigger uppercase_user_name

I am seeing the following output:

No value provided for required arguments 'table_name'

It's not clear based on the documentation what table_name is.

Any ideas?

Hi!

drop_trigger does not work with Ruby 3. Unfortunately, I couldn't make a quick fix for this.

== 20210214133450 CreateTriggerUpdateOrderSubtotal: reverting =================
-- drop_trigger(:update_order_subtotal, {:on=>:orders_items})
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

wrong number of arguments (given 2, expected 1; required keyword: on)


Caused by:
ArgumentError: wrong number of arguments (given 2, expected 1; required keyword: on)

Tasks: TOP => db:rollback
(See full trace by running task with --trace)

Thanks!

I don't know if it best in here or in

https://github.com/ctran/annotate_models/

Basically it will be nice if we can see triggers in model. So that we are aware of their existence.

Hi. Thanks for making fx! it helped me clean up the company's big rails app's migrations quite a bit.

Currently

Sadly, we still have to use db/structure.sql.
I'd prefer if we could go back to using db/schema.rb.

The one last thing that stands between here and there is a custom operator.

Currently, we have this code in the same migration with all of our create_function ... calls:

# TODO: figure out if/how we can express this so it ends up in schema.rb:
execute <<~SQL.squish
  CREATE OPERATOR ||+ (LEFTARG = TEXT,  RIGHTARG = TEXT, PROCEDURE = immutable_concat);
SQL

I'd rather have:

create_operator :pipe_pipe_plus

This line of code:

CREATE OPERATOR ||+ (LEFTARG = TEXT,  RIGHTARG = TEXT, PROCEDURE = immutable_concat);

is depended on by a few lines of code in migrations like this:

t.index "(((((name)::text ||+ (location_line_1)::text) ||+ (location_city)::text) ||+ (location_region)::text)) gin_trgm_ops", name: "index_jobs_1_gin", using: :gin

I tried, just in case, doing a create_operator :pipe_pipe_plus along with all of our create_functions.
This raises a NoMethodError for #create_operator, obviously.

I tried being sneaky and doing create_function :pipe_pipe_plus.
This didn't blow up, but then didn't end up in db/schema.rb.

I'm assuming that this is because db/schema.rb has no concept of create_operator like it has create_function, create_trigger, create_view, et al (?).

Feature request

Could you add a create_operator method that works just like create_function, which looks for a file named the same as the passed in symbol arg but in a db/operators folder instead of db/functions?

And then :hand wave: (I don't know what this would take), also get that to work in db/schema.rb please?

That would make me so happy.

Ruby's EOL is <= 2.6, we should drop the versions older than that, and ensure that 2.7 and above are supported.

We are experiencing a problem with the way the dump is generated into the schema.

Generated schema puts function definitions at the bottom

ActiveRecord::Schema.define(version: 2019_06_04_171454) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"
  enable_extension "unaccent"
  
  create_table "products", force: :cascade do |t|
    t.string "name"
    t.index "ts_tokenize((name)::text)", name: "index_product_name_tokenized", using: :gin
  end

  create_function :ts_tokenize, sql_definition: <<-SQL
      CREATE OR REPLACE FUNCTION public.ts_tokenize(text)
       RETURNS tsvector
       LANGUAGE sql
       IMMUTABLE
      AS $function$
      SELECT to_tsvector(public.unaccent('public.unaccent', $1))
      $function$
  SQL
end

This creates a failure when loading the schema at our test suite because it tries first to create the index depending in the function that was not defined yet.

If we manually change the order, and define the function just after enabling extensions we experience no problem at all, but every migration/dump resets the default order making this fail.

Probably a fix for this might be defining the functions at the beginning of the schema in order to have it available for the rest of the code.

Thank you.

Hey!

We use this gem in combination with https://github.com/rails-on-services/apartment. Like that gem recommends, we install extensions into a separate schema that is used throughout the application. We would also like to be able to create function in a different schema than the public one, eg:

CREATE OR REPLACE FUNCTION shared_extensions.my_function(...

The "creating" part works as expected, but because this line is hardcoded to look into the public schema, the functions aren't dumped to the schema.rb.

Would you be open to adding a way to configure the schema lookup?

Hey there!

I was wondering if this gem has any options as far as code formatting (tabs/spaces, number of spaces/tabs) as far as when code is inserted into schema.rb. I took the time to format the SQL how I preferred it to be - but whenever we run a migration, it is overwritten with the default/original formatting.

Feel free to just tell me "no", and I will happily live with it how it is.

Thank you!

rails 6.1.3
fx 0.6.2

When using github actions, I am getting this error:

ActiveRecord::StatementInvalid: PG::DuplicateObject: ERROR:  trigger "layer_integrity_checks" for relation "layer" already exists
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `exec'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `block (2 levels) in execute'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:46:in `block in execute'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activesupport-6.1.3/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/connection_adapters/postgresql/database_statements.rb:45:in `execute'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/fx-0.6.2/lib/fx/adapters/postgres.rb:152:in `execute'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/fx-0.6.2/lib/fx/adapters/postgres.rb:84:in `create_trigger'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/fx-0.6.2/lib/fx/statements/trigger.rb:49:in `create_trigger'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/migration.rb:929:in `block in method_missing'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/migration.rb:897:in `block in say_with_time'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/migration.rb:897:in `say_with_time'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/migration.rb:918:in `method_missing'
/home/runner/work/***/***/db/schema.rb:1136:in `block in <main>'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/schema.rb:50:in `instance_eval'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/schema.rb:50:in `define'
/home/runner/work/***/***/vendor/bundle/ruby/3.0.0/gems/activerecord-6.1.3/lib/active_record/schema.rb:46:in `define'
/home/runner/work/***/***/db/schema.rb:14:in `<main>'

Here is a copy of my schema https://gist.github.com/cj/bb62776913387c17ca1dc9c1c3ae394c

Locally it is working fine, the problem seems to just be when running it on github actions. Here is a copy of my github action https://gist.github.com/cj/ba6aca746cbc84afba1a1b510979889d

If this is unrelated to FX please let me know and I can close the issue.

Cheers!

It would be amazing if fx supported procedures!

I would love to be able to say

rails g fx:procedure my_fancy_procedure

Because I accidentally tried to do that with a procedure and use create_function / update_function and boy did that not work (because of course it isn't supported yet).

I love this gem!

background

I use hair_trigger in a project to add triggers which are integral to the DB -- it's used enough that I wrote some basic versioning around it, inspired by scenic (which seems v similar to the system fx uses).

Yesterday, I was looking for a way to add functions via migrations. Using execute works for rails db:migrate but doesn't work for tests because schema.rb doesn't record the functions. I couldn't find a way to force the execute SQL to be included, so went looking for a gem that could do this for me and found fx -- great!, I thought. However, fx defines create_trigger, which hair_trigger also defines. this has been problematic.

I tried a few things, like 'fx', require: false in the gemfile and then require 'fx' (and some variants like require 'fx/functions', and include Fx::Statements::Function` in the migration) but couldn't get anything working. Searching (many times in different ways) for methods to avoid naming conflicts between gems did not yield useful results.

my (eventual, and very quick) solution was to fork this repo and just comment all the requires and methods regarding triggers, which works well, but isn't really a good, long term, principled solution to the problem.

ideas on how to solve

The main idea on how to solve this that I have atm is for fx to provide either:

1. a way to import only one of trigger or function related statements/adapters (this does not look that simple, but mb there is an elegant way to do it), maybe via 'fx', require: 'function' in a gemfile; or
2. a way to import everything with alternate names, e.g., fx_create_function and fx_create_trigger, then maybe have 'fx', require: 'compatibility_names' in the gemfile, or something.

I think method 1 might be mb an easier PR, but there's a decisive reason to go with method 2, IMO: I want to start using fx for triggers instead of hair_trigger -- and that probably requires having both gems loaded simultaneously. I don't think it's a good idea to try and remove hair_trigger from past migrations (the webapp isn't live, yet, so this is maybe feasible); the more principled approach feels like going for compatibility over partial requires.

Writing a PR

If I write a PR for this, is this something that would be merged? (after review, etc)

If so, do you (or does anyone else) have any useful insight/input to this problem and other methods of solving it?

Finally, do you have any hints about how I should approach this? like, where would the best places be to add this sort of thing. I guess that I will need to learn a bit more about gemfiles and requires, but it doesn't seem overly complex.

I expect not much has changed from 5.0 to 5.1 and 5.2. But - we should add them to our test suite.

Currently if you run the generator with the following for example

rails generate fx:function test

The generator will create a migration file and an sql function file with paths like the following

db/functions/test_v01.sql
db/migrate/20211011202933_create_function_test.rb

But when you try to destroy the generated files with the following commad

rails destroy fx:function test

The generator tries to remove files with paths that don't exist like the following

db/functions/test_v02.sql
db/migrate/20211011203016_update_function_test_to_version_2.rb

GEM VERSION 0.6.2

I don't use Hound that much nowadays, might change F(x) to use CodeClimate instead.

While the gemspec claims the gem is licensed under the MIT licence, the recently added LICENSE file declares BSD-3. Which is it?

This was brought up in #73, and I think it holds some merit if we include a generated boilerplate with the generated function and/or trigger. Ideally this generated boilerplate should be unique per database adapter.

With Rails 6 just being shipped, is there anything we can do get fx to work with the new version?

Hi!
I see that the gem already support Ruby 3, but the release on rubygems doesn't support it (crash when reverting migrations). A ruby 3 compatible release would be helpful.
Is the gem in a releasable state?

  AFTER INSERT OR UPDATE OR DELETE
  ON table_name
  FOR EACH STATEMENT
  WHEN (OLD.value IS DISTINCT FROM NEW.value)
  EXECUTE PROCEDURE function();

Could someone make it work?

Thanks for this, as a user of scenic, I was happy to find a similar tool for functions :)

Unfortunately, this doesn't work for functions with parameters: although creating function isn't a problem, dropping function isn't possible so migrations can't be reverted.

I believe the problem comes from Fx::Adapters::Postgres#drop_function as the brakets (()) are added automatically.
I have functions with parameters and the function signature must be specified to drop it (because one can create different functions with the same name but different parameters).

A fix could be that when the function name is specified as a string, the brackets are not added? If backward-compatibility is a concern, the alternatively could be to pass a list of parameters as an option?

here is my_code

pry(main)> Fx.database.functions.map(&:name)
   (2.5ms)            SELECT
              pp.proname AS name,
              pg_get_functiondef(pp.oid) AS definition
          FROM pg_proc pp
          JOIN pg_namespace pn
              ON pn.oid = pp.pronamespace
          LEFT JOIN pg_depend pd
              ON pd.objid = pp.oid AND pd.deptype = 'e'
          WHERE pn.nspname = 'public'
            AND NOT pp.proisagg
            AND pd.objid IS NULL
          ORDER BY pp.oid;
 /*application:Pi*/
=> ["sfunc_for_multi_currency_agg", "ratio", "multi_currency_plus", "trans_to"]
pry(main)> Fx.database.drop_function('trans_to')
   (0.6ms)  DROP FUNCTION trans_to(); /*application:Pi*/
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  function trans_to() does not exist

#75

Could you add a section in the README on how to update a function that's used by triggers to not get errors while migrating, please?

Hello, thanks for this gem!

The create_function and create_trigger commands both accept a version argument, but when you roll back the migration you get this failure, because drop_function and drop_trigger don't know about that param:

ArgumentError: unknown keyword: version

(I'm not really sure why create_ commands would need a version other than 1, but I guess someone must be using it.)

I can see that you're already trying to make these commands reversible. Would you be interested in a PR that knows how to reverse creating functions & triggers when given a version? I guess it would still just DROP them.

As Rails 6 is shipped(#39) - we want to drop support for Rails 4.2 and ruby versions older than 2.2.2.

I looked at #create_trigger method code and noticed than option on not used in code. I tried run my migration without this option and migration successful done.

Is option on for #create_trigger useless? What do you think about using this option to interpolate sql definition?

For example:

CREATE TRIGGER update_state_changed_at
    BEFORE UPDATE ON %{on}
    FOR EACH ROW
    WHEN (OLD.state <> NEW.state)
EXECUTE PROCEDURE update_state_changed_at();

I think this is easy and I can make pull request if you don't mind

This throws the following error if you have aggregate functions (Postgis has a lot of these):

ActiveRecord::StatementInvalid: PG::WrongObjectType: ERROR:  "array_accum" is an aggregate function
:           SELECT
              pp.proname AS name,
              pg_get_functiondef(pp.oid) AS definition
          FROM pg_proc pp
          JOIN pg_namespace pn
              ON pn.oid = pp.pronamespace
          LEFT JOIN pg_depend pd
              ON pd.objid = pp.oid AND pd.deptype = 'e'
          WHERE pn.nspname = 'public' AND pd.objid IS NULL
          ORDER BY pp.oid;

Stacktrace if that helps:

/bundle/gems/activerecord-5.1.6.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:73:in `async_exec'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:73:in `block (2 levels) in execute'
/bundle/gems/activesupport-5.1.6.2/lib/active_support/dependencies/interlock.rb:46:in `block in permit_concurrent_loads'
/bundle/gems/activesupport-5.1.6.2/lib/active_support/concurrency/share_lock.rb:185:in `yield_shares'
/bundle/gems/activesupport-5.1.6.2/lib/active_support/dependencies/interlock.rb:45:in `permit_concurrent_loads'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:72:in `block in execute'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/connection_adapters/abstract_adapter.rb:613:in `block (2 levels) in log'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/connection_adapters/abstract_adapter.rb:612:in `block in log'
/bundle/gems/activesupport-5.1.6.2/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/connection_adapters/abstract_adapter.rb:604:in `log'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/connection_adapters/postgresql/database_statements.rb:71:in `execute'
/bundle/gems/fx-0.5.0/lib/fx/adapters/postgres/functions.rb:47:in `functions_from_postgres'
/bundle/gems/fx-0.5.0/lib/fx/adapters/postgres/functions.rb:39:in `all'
/bundle/gems/fx-0.5.0/lib/fx/adapters/postgres/functions.rb:28:in `all'
/bundle/gems/fx-0.5.0/lib/fx/adapters/postgres.rb:50:in `functions'
/bundle/gems/fx-0.5.0/lib/fx/schema_dumper/function.rb:25:in `dumpable_functions_in_database'
/bundle/gems/fx-0.5.0/lib/fx/schema_dumper/function.rb:13:in `functions'
/bundle/gems/fx-0.5.0/lib/fx/schema_dumper/function.rb:9:in `tables'
/bundle/gems/fx-0.5.0/lib/fx/schema_dumper/trigger.rb:8:in `tables'
/bundle/gems/scenic-1.5.1/lib/scenic/schema_dumper.rb:7:in `tables'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/schema_dumper.rb:37:in `dump'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/schema_dumper.rb:21:in `dump'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/railties/databases.rake:241:in `block (4 levels) in <top (required)>'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/railties/databases.rake:240:in `open'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/railties/databases.rake:240:in `block (3 levels) in <top (required)>'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/railties/databases.rake:66:in `block (2 levels) in <top (required)>'
/bundle/gems/activerecord-5.1.6.2/lib/active_record/railties/databases.rake:59:in `block (2 levels) in <top (required)>'
/bundle/gems/railties-5.1.6.2/lib/rails/commands/rake/rake_command.rb:21:in `block in perform'
/bundle/gems/railties-5.1.6.2/lib/rails/commands/rake/rake_command.rb:18:in `perform'
/bundle/gems/railties-5.1.6.2/lib/rails/command.rb:46:in `invoke'
/bundle/gems/railties-5.1.6.2/lib/rails/commands.rb:16:in `<top (required)>'
bin/rails:4:in `require'
bin/rails:4:in `<main>'

Basically pg_get_functiondef() fails for aggregate functions.

As we've begun adding functions to our system we've found a minor but annoying pain point when updating functions. With the current behavior a call to update_function will first drop and then recreate the function. For simple use-cases this behavior works fine, but as your functions grow and begin to depend on each other this no longer works. The problem becomes worse with the addition of views through the scenic gem.

As an example, imagine this scenario:

• A function age_years(dob date) returns int8 for calculating the number of years since dob.
• A function age_bracket(dob date) returns text returning a string of "child", "adolescent" or "adult" using age_years.
• A view (through scenic) that uses both age_years(dob date) and age_bracket(dob date).

In the current system we have two options:

1. The migration will drop all dependencies, and then rebuild. This is essentially a non-starter in terms of complexity.
2. Use CREATE OR REPLACE FUNCTION in our function definitions, and in the migrations manually execute the correct up and down versions.

My current thinking is that adding a replace_function for migrations, with a generator flag --replace, would be enough to completely support these scenarios. Perhaps even make the default replace_function and keep the "tear it all down" update_function approach for when actually needed.

An alternative, that I don't like as much, would be changing update_function to "realize" when a script has OR REPLACE and skip dropping those functions. Although instead of a new replace_function method we could add the flag replace: true to this method instead.

  create_function :naturalsort, sql_definition: <<-SQL
      CREATE OR REPLACE FUNCTION public.naturalsort(text)
       RETURNS bytea
       LANGUAGE sql
       IMMUTABLE STRICT
      AS $function$
          select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'),'\x00')
          from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r;
      $function$
  SQL

notice the \x00
I think it should be escaped in the generated schema.rb.

It works if we use structure.sql .

Travis is no longer free from open source, and I had totally missed that. We should replace it with something else, and #40 (comment) brought up that we could replace it with GitHub actions.

I'm OK with that, and would welcome a PR.

Fails on cloud providers not using a standard local postgres socket

When I generate a function with the generator such as in the example;

rails generate fx:function uppercase_users_name

it only generates the function but not the migration file. Reverting to 0.5 solved the issue

Hi! not sure if I'm missing something (maybe a config or similar) but when a project uses table_name_prefix (or table_name_suffix) on the application.rb, like:

module ProjectName
  class Application < Rails::Application
    # ...
    config.active_record.table_name_prefix = 'project-prefix-'
    # ...
  end
end

Seems that the generated functions or triggers should be aware of, in such the seems to need to generate the files like:

/functions/project-prefix-logidze_capture_exception_v01.sql
...
/triggers/project-prefix-logidze_on_some_table_v01.sql

And as metioned in the Issue #207 on the logidze gem this issue is related to how fx implemented the create_function method. It is though the method_missing defined in activerecord-6.1.4.4/lib/active_record/migration.rb:915 and so in there the method proper_table_name( ) is called, and in such when you have in the migration:

      #...
      dir.up do
        create_function :logidze_capture_exception, version: 1
      end
      #...

When the migration is run, the file that looks for is functions/project-prefix-logidze_capture_exception_v01.sql and the same happes with the triggers so if in the migration there is:

      #...
      dir.up do
        create_trigger :logidze_on_some_table, on: :some_table
      end
      #...

The file that looks is triggers/project-prefix-logidze_on_some_table_v01.sql and notice that the second argument is fine because the same proper_table_name( ) is called on them so the actual table name is referenced, in this case project-prefix-some_table

Not sure if this is something that you have in mind or do you want to address it. If so, what approach do you prefer?
Thanks for all the work you put into this gem!

The specs fail when run on a more recent version of Postgres than 10.x, which EOLs this year. There are currently four more recent major versions - 11 - 14.

The failure is due to a change in syntax for trigger creation. In Postgres 10.x, the text EXECUTE PROCEDURE was included, as can be seen here - https://www.postgresql.org/docs/10/plpgsql-trigger.html

In later Postgres versions, this text was changed to EXECUTE FUNCTION as can be seen:

• https://www.postgresql.org/docs/11/plpgsql-trigger.html
• https://www.postgresql.org/docs/12/plpgsql-trigger.html
• https://www.postgresql.org/docs/13/plpgsql-trigger.html
• https://www.postgresql.org/docs/14/plpgsql-trigger.html

This mismatch causes specs to fail.

Is there any objection to updating the Postgres version in .travis.yml to 14? Making this change doesn't appear to impact the behavior of the gem, only whether the specs pass.

I'm not sure if I'm doing something wrong, but when I have a trigger such as the following, under db/triggers:

CREATE TRIGGER trigger_name
    BEFORE INSERT OR UPDATE ON table_name
    FOR EACH ROW EXECUTE PROCEDURE
    function_name();

With a migration as follows:

class CreateTriggerTriggerName < ActiveRecord::Migration[5.2]
  def change
    create_trigger :trigger_name, on: :table_name
  end
end

Running the migration adds the trigger to the schema, but converts the PROCEDURE to a FUNCTION, as follows:

  create_trigger :trigger_name, sql_definition: <<-SQL
      CREATE TRIGGER trigger_name BEFORE INSERT OR UPDATE ON public.table_name FOR EACH ROW EXECUTE FUNCTION function_name()
  SQL

Is this expected behaviour? Is there any way to override/prevent it? Our app needs a PROCEDURE and not a FUNCTION to be run by the trigger.

4.0 and 4.1 have reached EOL, we can comfortably drop them.