[7db7w] PostgreSQL

gistfile1.md

1日目

2日目

select * from crosstab(
  'select extract(week from starts) as week,
          extract(dow from starts) as dow,
          count(*)
  from events
  where starts >= '2012-02-01' and '2012-03-01' > starts
  group by week, dow
  order by week, dow',
  'select * from generate_series(0,6)'
) as (
  week int,
  Sun int,
  Mon int,
  Tue int,
  Wed int,
  Thu int,
  Fri int,
  Sat int
) order by week;

3日目

3日目のまとめ

3日目の宿題

調べてみよう

1. http://www.postgresql.jp/document/9.0/html/contrib.html
2. http://www.regular-expressions.info/posix.html

やってみよう

create or replace function find_movies_by_actor(actor text)
returns setof movies as $$
declare
  res movies%rowtype;
begin
  if actor is not null then
    for res IN select m.*
      from movies m natural join movies_actors natural join actors
      where metaphone(name, 8) % metaphone(actor, 8)
      order by levenshtein(lower(name), lower(actor))
      loop
      return next res;
    end loop;
  else
    for res in select * from movies loop return next res;
    end loop;
  end if;
end;
$$ language plpgsql;

create or replace function recommend(movie text, actor text)
returns setof movies as $$
declare
  res movies%rowtype;
begin
  if movie is not null then
    for res in select *
      from find_movies_by_actor(actor)
      order by cube_distance(
        genre,
        ( select m.genre
          from movies m
          where m.title % movie
          order by levenshtein(lower(m.title), lower(movie))
          limit 1
        )
      )
    limit 5
    loop
    return next res;
    end loop;
  else
    for res in select *
      from find_movies_by_actor(actor)
      limit 5
      loop
      return next res;
    end loop;
  end if;
end;
$$ language plpgsql;

問題の再定義：