Postgres

psql> CREATE TABLE test_ids(rowid serial, myname character(50));

psql> INSERT INTO test_ids(name) VALUES('Alexandru Gris`);

psql> SELECT currval(`test_ids_rowid_seq`);

psql> SELECT nextval(`test_ids_rowid_seq`)

psql>ALTER TABLE test_ids ADD COLUMN borndate timestamp CHECK (borndate > '1/1/1940')

psql> insert into test_ids(myname, borndate) values ('New Born', 'yesterday');

psql> CREATE TABLE awesome_ids (description varchar(50), turned_on bit) INHERITS (test_ids);

psql> INSERT INTO awesome_ids(myname, description) VALUES ('Inherited', 'This is inherited');

psql> SELECT * FROM awesome_ids;
psql> SELECT * FROM row_ids;

psql>SELECT * FROM ONLY test_ids;

psql>ALTER TEST test_ids ADD COLUMN my_text TEXT;
psql>CREATE INDEX ftidx_myname ON test_ids USING GIN (to_tsvector('english'::regconfig, my_text::text));

psql>select to_tsvector('english', 'this is the best thing I have seen in my life');

psql>select to_tsvector('english', 'this is the best thing I have seen in my life, oh my life again my life') @@ to_tsquery('english', 'life');

psql>select ts_rank(v, q) from to_tsvector('english', 'this is the best thing I have seen in my life, life, life, super life') v, to_tsquery('english', 'life & best') q;

insert into test_ids(my_text) values ('Hello, Hello');
insert into test_ids(my_text) values ('Hello World');
insert into test_ids(my_text) values ('Hello Alexandru');
insert into test_ids(my_text) values ('Hello Alexandru Gris');
insert into test_ids(my_text) values ('Hello Alexandru Hello Gris Hello World');

select t.my_text,  ts_rank(v, q) as rank 
    from test_ids t, to_tsvector('english', t.my_text) v, to_tsquery('english', 'hello') q 
    where v @@ q 
    order by rank desc;

--- add a column with type tsvector
alter table test_ids add column words_vector tsvector;

--- index it
create index idxtxt on test_ids using gin(words_vector);

--- create a trigger to update this column with tsvectors
create trigger update_words_vector before insert or update on test_ids
  for each row execute procedure tsvector_update_trigger('words_vector', 'pg_catalog.english', 'my_text');

--- force self update the table
update test_ids set my_text = my_text;

--- run the query on this particular column
select t.my_text, ts_rank(t.words_vector, q)
    as rank from test_ids t, to_tsquery('english', 'hello') q
    where t.words_vector @@ q
    order by rank desc;

create table employees(
    id serial primary key, 
    name varchar(50) unique, 
    salary numeric(6,2));

create table salary_audit(
        emp_id integer references employees(id), 
        old_salary decimal(6, 2), 
        new_salary decimal(6, 2), 
        updated timestamp);

create index idx_emp_salary_audit on salary_audit(emp_id);
create index idx_emp on employees(LOWER(name));

-- enforce some constraints, for proving how before triggers work
create or replace function emp_constraints() returns trigger language plpgsql as $$
begin

    if new.name is null or new.salary is null then

      raise exception '% employee cannot have name or salary null', new.id;

    end if;

    if new.salary < 0 then
      raise exception '% employee cannot have negative salary', new.name;
    end if;

    return new;
  end;
$$;

drop trigger if exists emp_constraints on employees;
create trigger emp_constraints before insert or update on employees
	for each row execute procedure emp_constraints();

-- trigger to insert in the audit table all the changes to this table
create or replace function salary_audit() returns trigger language plpgsql as $$
declare
  old_salary integer default null;
begin

  --- if insert, old does not exist
  if TG_OP = 'UPDATE' then
    old_salary := old.salary;
  end if;

  insert into salary_audit(emp_id, old_salary, new_salary, updated) 
    values (new.id, old_salary, new.salary, now());

    return null; -- results are discarded since this is an after trigger
end;
$$;

-- execute audit trigger only when salary changes, not when name changes and only if different
drop trigger if exists emp_salary_audit on employees;
create trigger emp_salary_audit after update of salary on employees
  for each row when (old.* is distinct from new.*) execute procedure salary_audit();

-- old does not exist, thus we need a separate trigger for insert
drop trigger if exists emp_salary_audit_insert on employees;
create trigger emp_salary_audit_insert after insert on employees
  for each row execute procedure salary_audit();

insert into employees(name, salary) values ('AG6', 100), ('OM7', 100);
update employees set salary = salary * 1.5 where LOWER(name) = LOWER('OM7');

-- see the increases for all employees in percentage change
select e.name, (-a.old_salary+a.new_salary)/a.old_salary as pct_change, a.updated 
    from employees e inner join salary_audit a 
    on e.id = a.emp_id 
    where a.old_salary is not null and a.old_salary > 0;

insert into employee_current_position (emp_name, position_name) values ('AG10', 'Director');
insert into employee_current_position (emp_name, position_name) values ('AG11', 'Developer');

update employee_current_position set position_name='Developer' where emp_name='AG10';

select * from employee_current_position;

create table positions (id serial primary key, name varchar);

create table employee_position(
  id serial primary key,
  emp_id integer references employees(id),
  pos_id integer references positions(id),
  s timestamp, e timestamp);

--- some seed values
insert into positions(name) values ('architect'), ('developer'), ('manager');

--- our view
create view employee_current_position as
  select e.id emp_id, e.name emp_name, p.name position_name, ep.s start_date from
    employees e
    inner join employee_position ep on e.id = ep.emp_id
    inner join positions p on ep.pos_id = p.id where ep.e is null;

create or replace function update_into_emp_position() returns trigger language plpgsql as $$
declare
  pos integer default null; -- id of the position
  emp integer default null; -- id of the employee
begin

  -- strict keyword to return exactly one; if it doen't exist => raise exception and exit
  select id into strict emp from employees where name = new.emp_name;

  -- try to see if we already have a position inserted in the database that matches our wish
  select id into pos from positions where name = new.position_name;

  -- if not, we create it
  if pos is null then
    insert into positions(name) values (new.position_name);
    select currval('positions_id_seq') into pos; -- this will give us the id from the insert above
  elsif (select count(ep.emp_id) from employee_position ep 
        where ep.emp_id = emp and ep.pos_id = pos and ep.e is null) > 0 then
    return null; -- same position, no update. silent fail. another option would be to raise exception
  end if;

  -- set the end time to the previous position (if any) to current time
  update employee_position ep set e = now() where ep.emp_id = emp and ep.e is null;

  -- start a new position
  insert into employee_position(emp_id, pos_id, s) values (emp, pos, now());
  return new ; -- this does not matter
end;
$$;

-- create the trigger instead of insert or update
create trigger update_emp_pos instead of insert or update on employee_current_position
  for each row execute procedure update_into_emp_position();

create table auto_tags(
  id serial primary key, -- the tag index, we will use this for tagging teams
  index tsvector -- create a gin index on this
);

create index fts on auto_tags using gin(index);

--- The function below creates new tags or maps synonyms to existing tags (or creates a tag in the process)
create or replace function update_tags(t1 text, t2 text = null) returns integer language plpgsql as $$
declare

  t1_q tsquery;
  t2_q tsquery;
  t1_v tsvector;
  t2_v tsvector;

  ret integer := 0;

begin

  if t1 is null then
    raise exception 'First term must not be null';
  end if;

  if t2 is null then
    t2 := t1;
  end if;

  t1_q := plainto_tsquery(t1);
  t2_q := plainto_tsquery(t2);
  t1_v := to_tsvector(t1);
  t2_v := to_tsvector(t2);

  -- one of the two tags already exist in the database
  if exists(select 1 from auto_tags where index @@ (t1_q || t2_q) limit 1) then

    -- search / update for the left tag, no new tag is created
    with t as (update auto_tags set index = strip(index || t2_v)
      where id in (select id from auto_tags where index @@ (select t1_q && !!t2_q))
      returning 1) select count(*) from t into ret;

    -- search / update for the right tag
    if ret = 0 then
      with t as (update auto_tags set index = strip(index || t1_v)
        where id in (select id from auto_tags where index @@ (select t2_q && !!t1_q))
        returning 1) select count(*) from t into ret;

      end if;

  else
    -- create the tags as they do not exist
    insert into auto_tags(index) values (strip(t1_v || t2_v));
    return 1;
  end if;

  return ret;

end;
$$;

--- The function below returns the index for a specifc tag. 
--- If the tag is not found, it inserts it and returns its new index
create or replace function get_or_insert_tag(tag text) returns integer language plpgsql as $$
declare
  ret integer default null;
begin

  select id from auto_tags where index @@ plainto_tsquery(tag) limit 1 into ret;

  if ret is null then
    select update_tags(tag) into ret;
    if ret <> 1 then
      raise exception 'Something bad happened, procedure not correct. Should have at least 1 insert here.';
    end if;

    select currval('auto_tags_id_seq') into ret;
  end if;

  return ret;
end;
$$;

select update_tags('Dynamo', 'Dinamo');
select update_tags('Kiev', 'Kyiv');
select update_tags('Bucharest', 'Bucuresti');
select update_tags('Poly', 'Politehnica');
select update_tags('New York');

create table teams (
  id       serial primary key, -- teamid
  name     varchar(50), -- name of the team
  location varchar(50), -- the location of the team
  index_name    integer references auto_tags(id), -- link to the auto_tags for name
  index_location integer references auto_tags(id) -- link for auto_tags for location
);

-- since the index is basically an arbitrary token and range searches do not make sense for it
-- we use has indexes
create index idx_team_name on teams using hash(index_name);
create index idx_team_location on teams using hash(index_location);

-- trigger to update the tag (index in the auto_tags table)
create or replace function insert_or_update_teams() returns trigger language plpgsql as $$
begin

  select get_or_insert_tag(new.name::text) into new.index_name;
  select get_or_insert_tag(new.location::text) into new.index_location;

  return new;

end;
$$;

create trigger trg_insert_or_update_teams before insert or update on teams
  for each row execute procedure insert_or_update_teams();

insert into teams(name, location) values
                                          ('Poly', 'Iasi'),
                                          ('Poly', 'Timisoara'),
                                          ('Dinamo', 'Bucuresti'),
                                          ('Dinamo', 'Kiev'),
                                          ('Rangers', 'New York'),
                                          ('Steaua', 'Bucuresti'),
                                          ('Rapid', 'Bucuresti'),
                                          ('Arsenal', 'Kyiv'),
                                          ('Locomotiv', 'Kyiv'),
                                          ('Arsenal', 'London');

select * from teams;
select * from auto_tags; -- we see that new tags are created for the team names

ate or replace function get_team_by_prefix(prefix text) returns setof teams language plpgsql as $$
declare
  arr_query_terms text array;
  l integer;
begin

  select string_to_array(prefix, ' ') into arr_query_terms;
  l := array_length(arr_query_terms, 1);

  create temp table if not exists indices (idx integer) on commit drop;
  truncate indices;

  for i in  1 .. l-1 loop
    insert into indices select id from auto_tags where index @@ plainto_tsquery(arr_query_terms[i]);
  end loop;

  if char_length(arr_query_terms[l]) > 0 then
     insert into indices select id from auto_tags where index @@ to_tsquery(arr_query_terms[l] || ':*') ;
  end if;

  return query
    select r.* from teams r inner join
            (select x.id, count(x.id) as rank from
                  (select t.id from teams t inner join indices i on t.index_name = i.idx
                   union all
                  select t.id from teams t inner join indices i on
            t.index_location = i.idx) x
    group by x.id) q on r.id = q.id
    order by q.rank
    desc limit 10;

end;
$$;

select get_team_by_prefix('Kie'); -- will return everything that is Kiev and Kyiv
select get_team_by_prefix('Kyi'); -- will return everything that is Kiev and Kyiv
select get_team_by_prefix('Dynamo Kyi'); -- will return Dinamo Kiev, followed by a the dinamo bucharest and other teams from Kiev
select get_team_by_prefix('New York Ran'); -- will return Rangers New York