Source

Socrates / contrib / pg.sql

-- 试验 PostgreSQL 的特化实现

-- 原语定义
-- SUBJECT = 1
-- PREDICATE = 2
-- IS = 3
-- OBJTYPE = 4
-- TYPE = 5
-- STRING = 6
-- NAME = 7
-- URI = 8
-- STORAGE = 9

-- write segemnt 的原子实现
create or replace function write_segment(sbj_id integer, pred_id integer, object anyelement) returns integer as $$
declare
  sbj integer; 
  stg text;
  has_it integer;
  objType integer;
begin
  if sbj_id is null then
     sbj := nextval('subject_seq_id_seq');
  else
     sbj := sbj_id;
  end if;

  select obj into objType from segment_subject where predicate_id=4 and subject_id = pred_id;
  select obj into stg from segment_string where predicate_id=9 and subject_id = objType;
  execute 'select count(*) from '|| stg || ' where predicate_id= '|| quote_nullable(pred_id) ||' and subject_id='|| quote_nullable(sbj) into has_it ;
  if has_it > 0 then
     execute 'delete from '|| stg ||' where predicate_id = ' || quote_nullable(pred_id) || 'and subject_id=' || quote_nullable(sbj);
  end if;
  execute 'insert into ' || stg || '(subject_id, predicate_id, obj) values('|| quote_nullable(sbj)||', '|| quote_nullable(pred_id)||', '|| quote_nullable(object)||');';
  return sbj;
end;
$$ language plpgsql;

-- write segment 的易读版本
create or replace function write_segment(sbj_id integer, predicate text, object anyelement) returns integer as $$
declare
  pred_id integer;
begin
  select subject_id into pred_id from segment_string where obj = predicate and predicate = 7;
  -- TODO: 如果得到 NULL,应该 raise 错误。
  return write_segment(sbj_id, pred_id, object);
end;
$$ language plpgsql;

-- get storage name by predicate id
create or replace function get_storage_by_predicate_id(pred_id integer) returns TEXT as $$
       select obj from segment_string where subject_id = (select obj from segment_subject where subject_id = $1 and predicate_id = 4) and predicate_id = 9;
$$ language sql;

-- get storage name by predicate name
create or replace function get_storage_by_predicate(pred text) returns TEXT as $$
      select get_storage_by_predicate_id((select subject_id from segment_string where obj=$1 and predicate_id=7));
$$ language sql;

-- get predicate's ids
create or replace function get_subject(sbj_id integer) returns setof integer as $$
declare
  typeid integer;
  stgName TEXT;
begin
  -- 取得所有注册的 type
  for typeid in select subject_id from segment_subject where predicate_id = 3 and obj = 5 loop
      -- 取得该 type 的 storage
      select obj into stgName from segment_string where subject_id = typeid and predicate_id = 9;
      return query execute 'select predicate_id from '|| stgName || ' where subject_id=' || quote_nullable(sbj_id);
  end loop;
end;
$$ language plpgsql;

-- get object by subject id and predicate id
create or replace function get_object(sbj_id integer, pred_id integer) returns RECORD as $$
declare
  stg TEXT;
  re RECORD;
begin
  stg := get_storage_by_predicate_id(pred_id);
  execute 'select obj from '|| stg || ' where subject_id=' || sbj_id || ' and predicate_id = ' || pred_id into re;
  return re;
end;
$$ language plpgsql;

-- get object by subject id and predicate name
create or replace function get_object(sbj_id integer, predicate text) returns RECORD as $$
declare
  stg TEXT;
  pred_id integer;
  re RECORD;
begin
  stg := get_storage_by_predicate(predicate);
  select subject_id into pred_id from segment_string as str where predicate_id = 7 and obj = predicate and exists(select * from segment_subject where subject_id=str.subject_id and predicate_id=3 and obj=2);
  execute 'select obj from '|| stg || ' where subject_id=' || sbj_id || 'and predicate_id = ' || pred_id into re;
  return re;
end;
$$ language plpgsql;