I'm trying to use DBIx::Class deploy. It's in a pg setting, but I have a problem, actually a real problem and a couple of meta problems.

THE 'REAL' PROBLEM

I have a procedure that is used in the definition of the table. A simplified if somewhat stupid version is ...

 create function my_now() returns timestamptz as $$ select now() $$ language sql;

and in the table

 create my_table ( code serial, tstamp default my_now(), some_data varchar);

I have added an sqlt_deploy_hook into Schema.pm that does (essentially)  $sqlt_schema->add_procedure(...). but it doesn't have any effect. After a bit of reverse engineering what appears to happen is the the procedure is added internally to a "_procedures" but, this is never referenced by SQL::Translator::Producer::PostgreSQL->produce, this does tables, views and triggers, in that order, not procedures. As my column definition contains the home made procedure the create table fails.

META PROBLEM

After a bit of research I found an issue on the github page for SQL::Translator::Producer::PostgreSQL (#82) that seems to directly relate to this issue, in that they add have added the procedures *but* they do it after the tables etc, so it still doesn't work.

Ok I could move adding the procedures to the beginning and that will probably work, *but* I'm now looking at a monkey patched version of a the non-master branch of a CPAN module. This is not a good thing. The fact that the work for this issue is now over 2 years old makes me think the maintainer isn't interested.

I can see that there are issues. With pg, you can defined a procedure that references tables and columns that don't exist. I don't think you can with mysql. So to do this properly requires some work. At the very least a pre and post deploy hook. I think I saw something in documentation that says you do a post deploy thing, but I can no longer find it, which brings me to...

META PROBLEM 2

I found and find the documentation quite hard to follow. For example it took me some time to figure out what the arguments to add_procedure (in SQL::Translator::Schema should be.

WORK AROUND

My work around - which works for deploy, but probably not for versioning is in Schema.pm sqlt_deploy_hook to pull out the dbh and explicitly add the functions "by hand". Luckily in the context the functions are fairly stable so in practice I might be able to get away with it.

FOOTNOTE

I'm doing this because I really want versioning to work (maybe at some point moving to Sqitch) for a specific change. I'm just about to start this so the story may evolve. Though the procedures are not changing.