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.

In reply to DBIx::Class deploy by yewtc

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.