Subject: procedure help between databases
From: Daniel Sobey <dns@dns.id.au>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Date: Fri, 03 Sep 2010 20:19:46 +0930
Message-ID: <1283510986.14678.35.camel@ubuntu-laptop.BlueCrystal.local>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Hello list,

I need some help in writing a trigger / procedure.

I have two databases, one for last.fm and one for musicbrainz.

What I would like to do is when i submit a song into last.fm, connect to
the musicbrainz and get an id for the song. Both are using postgres and
i can use a db link to query from one to the other.

The query I need to run on the musicbrainz database is:

select track.name, artist.name,album.name,album.gid,track.gid,artist.gid
from track,artist,albumjoin,album where artist.id = track.artist and
albumjoin.track = track.id and albumjoin.album=album.id and
artist.name='The Beatles' and track.name='Yellow Submarine' and
album.name='Revolver';

I can then perform the following query that uses the database link:

select * from dblink ('dbname=musicbrainz queries_db','select
track.name, artist.name,album.name,album.gid,track.gid,artist.gid from
track,artist,albumjoin,album where artist.id = track.artist and
albumjoin.track = track.id and albumjoin.album=album.id and
artist.name=''The Beatles'' and track.name=''Yellow Submarine'' and
album.name=''Revolver'';') as t1(track character varying(255),artist
character varying(255),album character varying(255), trackid
character(36),artistid character(36),albumid character(36));

Now i want to turn this query into a trigger so when i insert into a
table in the libre.fm database it performs the above query and inserts
it into some tables in the libre.fm database.

What i have so far is as below but i am not sure the best way to call a
function and then insert the results in a table. If anyone could point
me to some examples i would appreciate it.

create or replace function mb_lookup(varchar,varchar,varchar) returns
integer as $$
declare
  track alias for $1;
  artist alias for $2;
  album alias for $3;
  abc record;
begin
  for abc in select * from dblink ('dbname=musicbrainz_db','select
track.name, artist.name,album.name,album.gid,track.gid,artist.gid from
track,artist,albumjoin,album where artist.id = track.artist and
albumjoin.track = track.id and albumjoin.album=album.id and
artist.name=artist and track.name=track and album.name=album;') as
t1(track character varying(255),artist character varying(255),album
character varying(255), trackid character(36),artistid
character(36),albumid character(36)) LOOP

  END LOOP;
  return 1;
end;
$$ language 'plpgsql';