From: | Daniel Sobey <dns(at)dns(dot)id(dot)au> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | procedure help between databases |
Date: | 2010-09-03 10:49:46 |
Message-ID: | 1283510986.14678.35.camel@ubuntu-laptop.BlueCrystal.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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';
From | Date | Subject | |
---|---|---|---|
Next Message | Viktor Bojović | 2010-09-05 23:38:24 | naming arguments in aggregate function |
Previous Message | Tim Schumacher | 2010-09-03 09:40:18 | Generating Rows from a date and a duration |