---------------------------------------------------------------------
-- A set of utilities dealing with various aspects of the repository
---------------------------------------------------------------------

-- Determine if an item in some sort of hierarchy has children
create function acs_util__has_children(varchar,varchar,varchar)
returns boolean as '
declare
  item_id_in		alias for $1;
  table_name_in		alias for $2;
  parent_column_in	alias for $3;
  v_row_count		integer;
begin
  execute ''select 1 where exists (select 1 from ''
	    || quote_ident(table_name_in)
	    || '' where ''
	    || quote_ident(parent_column_in)
	    || '' = ''
	    || quote_literal(item_id_in)
	    || '')'';
  get diagnostics v_row_count = ROW_COUNT;
  if v_row_count = 1 then
     return ''t'';
  else
     return ''f'';
  end if;
end;' language 'plpgsql';


-- Perform a SQL query which will return some values
-- or NULL. Return 't' if a value was returned, 'f' otherwise
create function acs_util__boolean_query(varchar,boolean)
returns boolean as '
declare
  query_in		alias for $1;
  not_flag_in		alias for $2;
  v_sql_query		varchar;
  v_row_count		integer;
begin
  v_sql_query := ''select 1 where '';

  if not_flag_in then
    v_sql_query := v_sql_query || ''not '';
  end if;

  execute v_sql_query || ''exists ('' || query_in || '')'';

  get diagnostics v_row_count = ROW_COUNT;

  if v_row_count = 1 then
    return ''t'';
  else
    return ''f'';
  end if;

end;' language 'plpgsql';


-- Convert a SQL string to a TCL string: surround the string with
-- {} if it contains spaces
create function acs_util__sql_to_tcl_string(varchar)
returns varchar as '
declare
  string_in	alias for $1;
begin
  if instr(string_in, '' '') <> 0 then
    return ''{'' || string_in || ''}'';
  else
    return string_in;
  end if;
end;' language 'plpgsql';