[Dbix-class] select * from function('param')

[Ash Berlin]

mock wrote:
> On Fri, Jul 28, 2006 at 12:38:59AM -0400, Daniel Westermann-Clark wrote:
>   
>> On 2006-07-27 22:38:02 +0000, mock wrote:
>>     
>>> So I've got a function in my Pg database which takes a single
>>> parameter and returns a set of rows.
>>>
>>> "select * from function('param')" works just fine from psql and
>>> returns all my lovely rows just as I want them.
>>>       
>> If 'param' is limited to only one or two values, the path of least
>> resistance is to create a view in your database and then treat it like
>> any other source in DBIx::Class.
>>
>> Otherwise, you'll have to try 'from' attr hackery.  See:
>>
>> http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/ResultSet.pm#from
>>
>>     
>
> Yeah, looks like I'm going to have to do the from hackery.
>
> So I tried the stupidly obvious:
>
> my $rs = $schema->resultset('Getcourses')->search(undef,
> {
>     from => 'getcourses(\'STUDENT\')',
> })->first;
>
> print "Name ",$rs->name, "\n";
>
> Which gives me this error:
>
> DBD::Pg::st execute failed: ERROR:  missing FROM-clause entry for table "me"
> DBIx::Class::ResultSet::first(): Error executing 'SELECT me.courseid, me.name, me.seattotal, me.seatleft, me.slot, me.priority, me.availid, me.begindate, me.enddate, me.couponname, me.coupontype, me.coupontotal, me.couponleft, me.price, me.tax, me.currency FROM getcourses('STUDENT')' (): ERROR:  missing FROM-clause entry for table "me"
>
> On first inspection it looks like it is doing what I want 
> "SELECT * FROM getcourses('STUDENT')"
> but I don't understand the error message.  The documentation talks a lot about
> custom joins, but that doesn't look like what I'm trying to do.  Any hints?
>
> mock
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive: http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>   
It is expecting:

  SELECT ... FROM getcourses('STUDENT') me

but the "me" (table alias) is missing.

Try:

  from => 'getcourses(\'STUDENT\') me',

Not sure if there is a proper way of doing that or not tho

-ash