Custom SQL in DBIx::Class (or how to use PgSQL fulltext search from Perl)

kappa has asked for the wisdom of the Perl Monks concerning the following question:

Good day to all!

(this is either a DBIx::Class, SQL::Abstarct or PgSQL fulltext search + Perl question)

Can someone please help to select a function with several parameters using DBIx::Class?

This is the code we came up with. The problem is, $search_text doesn't get quoted when sent to DB server.

'+select'  => [
  \[q{ts_headline('ru', html, plainto_tsquery('ru', ?), 'StartSel = <b
+>, StopSel = </b>')},
    [dummy => $search_text],
  ]],
'+as'   => ['html_hilited'],
[download]

--kap

Comment on Custom SQL in DBIx::Class (or how to use PgSQL fulltext search from Perl) Select or Download Code

Replies are listed 'Best First'.
Re: Custom SQL in DBIx::Class (or how to use PgSQL fulltext search from Perl) by Mutant (Priest) on May 07, 2009 at 11:29 UTC
Not sure on the SQL::Abstract approach (you could try the DBIC mailing list, or the channel on irc.perl.org), but I've found one way around this when SQL::Abstract won't cut the mustard is to create a ResultSet class, get the DBI object, and manually execute a query. The main downside is you don't get back objects (altho I think it might be possible to construct them). SQL abstraction is nice, but not always the best approach.	[reply]

Replies are listed 'Best First'.

Re: Custom SQL in DBIx::Class (or how to use PgSQL fulltext search from Perl)
by Mutant (Priest) on May 07, 2009 at 11:29 UTC

Not sure on the SQL::Abstract approach (you could try the DBIC mailing list, or the channel on irc.perl.org), but I've found one way around this when SQL::Abstract won't cut the mustard is to create a ResultSet class, get the DBI object, and manually execute a query.

The main downside is you don't get back objects (altho I think it might be possible to construct them). SQL abstraction is nice, but not always the best approach.

[reply]