[SQL] Generating Rows from a date and a duration

[Tim Schumacher]

Hi List,

I'm kinda stuck situation, I have a timestamp which resambles a
startdate and a duration in days and I want to bloat this, so I have a
row for every day beginning from the startdate. I have created an
example bellow, maybe I'm doing it on the wrong angle and you can come
up with some better ideas:

BEGIN TRANSACTION;

CREATE TABLE example
(
   id serial NOT NULL,
   startdate timestamp without time zone,
   duration int_unsigned NOT NULL,
   CONSTRAINT pq_example_id PRIMARY KEY (id)
) WITH (OIDS=FALSE)
;

insert into example(id,startdate,duration) values (1,'2010-09-03',4);
insert into example(id,startdate,duration) values (2,'2010-09-03',6);

CREATE OR REPLACE FUNCTION bloat_duration(IN id integer,
  IN startdate  timestamp
without time zone,
  IN duration integer,
  OUT id integer,
  OUT duration_date date)
RETURNS SETOF RECORD AS
$$
BEGIN
  RETURN QUERY SELECT
id,to_date(to_char(startdate,'-MM-DD'),'-MM-DD')+s.a AS
stockdate FROM generate_series(0,duration-1) AS s(a);
END;
$$
LANGUAGE 'plpgsql';

-- This works, but not what I want
SELECT * FROM bloat_duration(1,'2010-09-03',4);

-- This does not work

SELECT * FROM example AS ex
INNER JOIN bloat_duration(ex.id,ex.startdate,ex.duration) AS bd ON bd.id
= ex.id

ROLLBACK TRANSACTION;


greetings

Tim



signature.asc
Description: OpenPGP digital signature

[SQL] procedure help between databases

[Daniel Sobey]

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









-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql