Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

SQL function from DBIC

by Sixtease (Friar)
on Dec 07, 2008 at 15:10 UTC ( #728721=perlquestion: print w/replies, xml ) Need Help??
Sixtease has asked for the wisdom of the Perl Monks concerning the following question:

Hail friends!

I'm working my way through with DBIC in Catalyst and I found myself needing to simply call a postgres function from a template (or from the controller if must be).

I have an enum type in the database and would like to generate <option>s for them within a <select>.

I'd like to see the result of the postgres function enum_range.

I was searching for a simple mechanism that would let me enter arbitrary SQL (not just the condition part), so I could write SELECT enum_range(typename);. But I have failed to find one.

Do you have any advice?

use strict; use warnings; print "Just Another Perl Hacker\n";

Replies are listed 'Best First'.
Re: SQL function from DBIC
by CountZero (Bishop) on Dec 07, 2008 at 16:52 UTC
    I think you can do something like:
    my $results = $resultset->search( {}, { 'select' => 'enum_range(typename)', 'as' => 'enumerate', }, ); while (my $result = $results->next) { print $result->get_column('enumerate'); }
    Caution: Code not tested as I do not have postgres, but I did something similar in MySQL (applying a function to a combination of columns) and it worked then.


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

      Yeehaw! Works, man. And without defining a result_source. :^)) Thanks a lot. *bows*

      use strict; use warnings; print "Just Another Perl Hacker\n";
Re: SQL function from DBIC
by Corion (Pope) on Dec 07, 2008 at 15:23 UTC
Re: SQL function from DBIC
by f00li5h (Chaplain) on Dec 07, 2008 at 16:32 UTC

    This one time, on #dbix-class, mst told me somet things about virtual views with DBIC.

    package MySchema::Sneaky; use base qw/DBIx::Class/; __PACKAGE__->load_components(qw/Core/); __PACKAGE__->table("DUMMY"); # madness going on # scalar ref to the sql statement __PACKAGE__->result_source_instance->name( \<<EOSQL); ( select foo, bar from baz ) EOSQL __PACKAGE__->add_columns( qw/ foo bar / );

    Note that SQL::Translator will have trouble ->deploying this kind of table, and you need a hook to remove this table from the definition with something like:

    package MySchema; use strict; use base qw/DBIx::Class::Schema/; sub sqlt_deploy_hook { my ($self, $sqlt_schema) = @_; my @pesky_tables = grep /^SCALAR\(0x[0-9A-F]+\)/i, keys %{ $sqlt_s +chema->{tables} }; $sqlt_schema->drop_table( $_ ) for @pesky_tables; local $" = ', '; warn "Removed @pesky_tables from list of tables t +o deploy"; use Data::Dumper; #die Dumper $sqlt_schema; }

    @_=qw; ask f00li5h to appear and remain for a moment of pretend better than a lifetime;;s;;@_[map hex,split'',B204316D8C2A4516DE];;y/05/os/&print;

Re: SQL function from DBIC
by davidrw (Prior) on Dec 07, 2008 at 15:30 UTC
    See the "Arbitrary SQL through a custom ResultSource" section in DBIx::Class::Manual::Cookbook

    Also, I think (untested, but appears so from grepping distro) that you can do:
    my $dbh = $myobj->db_Main; use Data::Dumper; print Dumper $dbh->selectall_arrayref( 'select enum_range(typename); +', {Slice=>{}}, () );

      If you are going to dig in and do something with a database handle directly, the recommended way is:

      use Data::Dumper; $schema->storage->dbh_do( sub { my ( $storage, $dbh ) = @_; print Dumper $dbh->selectall_arrayref( 'select enum_range(typename);', { slice => {} } ); } );

      The dbh_do method (documented in DBIx::Class::Storage::DBI) gives you the benefit of things like automatic reconnection to the database if your connection has timed out or the server has been restarted.

      Also, db_Main is from Class::DBI, not DBIx::Class, although there is a CDBICompat component that will make the latter work mostly like the former (though in the long run you will be better off by just learning the DBIx::Class way, rather than using CDBICompat).
      We're not surrounded, we're in a target-rich environment!
Re: SQL function from DBIC
by Sixtease (Friar) on Dec 07, 2008 at 15:59 UTC

    Thanks guys.

    Just how do I get to the object that allows me to call selectall_arrayref? I didn't find anything like that in the DBIx::Class docs. My starting point is $c->model('DB'). :-)

    use strict; use warnings; print "Just Another Perl Hacker\n";

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://728721]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (2)
As of 2018-02-19 02:25 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (257 votes). Check out past polls.