 BEGIN
 
     IF exists( SELECT 1 FROM loan_items i INNER JOIN loan_status s on i.loan_ref = s.loan_ref
-        WHERE s.is_last= true AND s.status != 'closed' AND i.part_ref = OLD.id ) THEN
+        WHERE s.is_last= true AND s.status != 'closed' AND i.specimen_ref = OLD.id ) THEN
       RAISE EXCEPTION 'The Part is currently used in an ongoing loan';
     END IF;
     RETURN OLD;

LANGUAGE sql immutable;


create or replace function upsert (tableName in varchar, keyValues in hstore) returns text language plpgsql as
$$
declare
  insert_stmt varchar := 'insert into ' || quote_ident(tableName) || ' (';
  update_stmt varchar := 'update ' || quote_ident(tableName) || ' SET (';
  where_stmt varchar := ' WHERE ';
  iloop integer := 0;
  recUnqFields RECORD;
  newhst RECORD;
  lowerKeyValues hstore;
begin
  for newhst in (select * from each(keyValues)) loop
    if iloop = 0 then
      lowerKeyValues := hstore(lower(newhst.key), newhst.value);
    else
      lowerKeyValues := lowerKeyValues || hstore(lower(newhst.key), newhst.value);
    end if;
    iloop := iloop +1;
  end loop;
  iloop := 0;
  insert_stmt := insert_stmt || array_to_string(akeys(lowerKeyValues), ',') || ') VALUES (' || chr(39) || array_to_string(avals(lowerKeyValues), (chr(39) || ',' || chr(39))::text) || chr(39) ||')';
  begin
    execute insert_stmt;
  exception
    when unique_violation then
      begin
        for recUnqFields IN (select x.vals as field, column_default as defaultVal 
                             from (select regexp_split_to_table(trim(substr(indexdef,strpos(indexdef, '(')+1),')'),', ') as vals 
                                   from pg_indexes 
                                   where tablename = tableName 
                                     and strpos(indexdef, 'UNIQUE') > 0 
                                     and indexname = (select conname 
                                                      from pg_class inner join pg_constraint on pg_class.oid = pg_constraint.conrelid and relname = tableName and contype = 'p'
                                                     )
                                  ) as x 
                             inner join 
                             information_schema.columns on x.vals = column_name and table_name = tableName) loop
          if iloop > 0 then
            where_stmt := where_stmt || ' AND ';
          end if;
          iloop := iloop + 1;
          if lowerKeyValues ? recUnqFields.field then
            where_stmt := where_stmt || quote_ident(recUnqFields.field) || ' = ' || quote_literal(lowerKeyValues -> recUnqFields.field);
          else
            where_stmt := where_stmt || quote_ident(recUnqFields.field) || ' = ' || quote_literal(coalesce(recUnqFields.defaultVal,''));
          end if;
        end loop;
        update_stmt := update_stmt || array_to_string(akeys(lowerKeyValues), ',') || ') = (' || chr(39) || array_to_string(avals(lowerKeyValues), (chr(39) || ',' || chr(39))::text) || chr(39) ||')' || where_stmt;
        execute update_stmt;
        return 'updated';
      exception
        when others then
          return 'SQL error is: '::text || SQLERRM;
      end;
      return 'SQL error is: '::text || SQLERRM;
  end;
  return 'inserted';
end;
$$;


CREATE OR REPLACE function fct_remove_last_flag() RETURNS TRIGGER
language plpgsql
AS

                                ) as fields_available
         ) subq
    WHERE user_ref = NEW.id
      AND pref_key = 'search_cols_specimen';
    FOR saved_search_row IN SELECT id, visible_fields_in_result FROM my_saved_searches WHERE user_ref = NEW.id LOOP
      UPDATE my_saved_searches
      SET visible_fields_in_result = subq.fields_available
