From: Daniel Sobey <dns@dns.id.au>
To: pgsql-sql@postgresql.org
Subject: procedure help between databases
Date: Fri, 03 Sep 2010 20:19:46 +0930
Message-ID: <1283510986.14678.35.camel@ubuntu-laptop.BlueCrystal.local> (raw)
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';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dns@dns.id.au
Subject: Re: procedure help between databases
In-Reply-To: <1283510986.14678.35.camel@ubuntu-laptop.BlueCrystal.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox