Raw SQL references
Let's say I want to migrate a sql function in pgroll, does the function, and all the schema objects referenced inside it, need to hard reference the new migration schema? Will this then require migrating the function and its schema objects to public on the next migration?
2 Replies
Oh, it seems like search path should handle this 🙂
Hmmm, looking at the raw sql restrictions, it's not possible for a function to be migrated in step with a supported operation like rename column, so a function that references a renamed column will break. Is it viable to just put all the migration's operations into a single raw sql operation, or will this cause locking and kind of defeat the purpose of pgroll?
Maybe the thing to do is to add the function after start with hard references to the new schema, and then remove the hard references after complete?
Indeed changes to functions can only be done with raw sql (https://github.com/xataio/pgroll/blob/main/docs/README.md#raw-sql/). You are right that after the column rename completes the function won't be able to reference it by its old name anymore. The column rename and function change would have to be made at roughly the same time.