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';
