[Dbix-class] how to add a method that performs a database lookup?

[Adam Witney]

On 17 Dec 2008, at 04:15, Toby Corkindale wrote:

> Adam Witney wrote:
> [snip]
>>>> The second place i might need this is that i have some data in a  
>>>> second database and so i would have to interact with this using  
>>>> dblink (I am using PostgreSQL). This seems easier to just write  
>>>> the query in a method?
>>>
>>> Doesn't Pg just represent the foreign DBI-Linked database as  
>>> another schema? So I think you could just write the extra classes  
>>> into dbic and it should query them, without realising their foreign?
>>> .. I haven't tried it myself though.
>> The problem is that with dblink non-superusers have to provide a  
>> username/password in the dblink query, can you pass these kind of  
>> parameters into the class/object?
>> eg
>> SELECT * from dblink('dbname= mydb username=user password=mypass',  
>> 'SELECT id FROM mytable') AS t1(id int);
>> I can wrap this in a function, but can i build a class on top of a  
>> table function with DBIx::Class?
>
> Hang on.. Don't you put the username/password in the foreign-schema  
> defn when you set it all up, eg:
> SELECT make_accessor_functions(
> 'dbi:Pg:dbname=mydb',
> 'user',
> 'mypass',
> etc..
> );
>
> And then access it via plain SQL:
> SELECT id,foo,bar FROM remote_db.remote_table;
>
> As I understand it, it works via PL/Perlu, so you need to be  
> superuser to create the functions and setup, but after that anyone  
> could run them?
>
> Or are we talking about two different Pg utilities? :)

I was referring to dblink which works like this:

SELECT * FROM dblink('dbname=test1', 'SELECT id, name FROM my_table')  
AS t1(id int, name text);

http://www.postgresql.org/docs/current/static/dblink.html

I have not looked at DBI-link before... i will have a look and see how  
it works

thanks

adam