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

[Toby Corkindale]

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? :)

Toby