create or replace function us_selection__new (integer,integer,varchar,text,text,text,integer,integer,timestamptz,varchar)
returns integer as '
declare
	new__selection_id	alias for $1; -- default null
	new__package_id		alias for $2; -- default null
	new__title		alias for $3;
	new__description	alias for $4; -- default null
	new__full_sql		alias for $5; -- default null
	new__bind_vars		alias for $6; -- default null
	new__context_id		alias for $7; -- default null
	new__creation_user	alias for $8; -- default null
	new__creation_date	alias for $9; -- default current_timestamp
	new__creation_ip	alias for $10; -- default null
	v_selection_id		us_selections.selection_id%TYPE;
begin

                v_selection_id := acs_object__new (
                        new__selection_id,
                        ''us_selection'',
                        new__creation_date,
                        new__creation_user,
                        new__creation_ip,
                        new__context_id,
			''t''
                );

		insert into acs_named_objects (object_id, object_name, package_id) 
        	 values (v_selection_id, new__title, new__package_id);

		insert into us_selections
		       (selection_id, package_id, title, description, full_sql, bind_vars)
		values
		       (v_selection_id, new__package_id, new__title, new__description, new__full_sql, new__bind_vars);
		return v_selection_id;

end;' language 'plpgsql';

create or replace function us_selection__delete (integer)
returns integer as '
declare
	delete__selection_id	alias for $1;
begin
	delete from acs_objects where context_id = delete__selection_id;
	delete from us_selections where selection_id = delete__selection_id;
	acs_object__delete(delete__selection_id);

	return 0;	
end;' language 'plpgsql';

create or replace function us_selection__name (integer)
returns varchar as '
declare
	 name__selection_id	alias for $1;
	 v_title		us_selections.title%TYPE;
begin

		select into v_title title
		from us_selections
		where selection_id = name__selection_id;

		return v_title;
end;' language 'plpgsql';

create or replace function us_selection__edit (integer,varchar,text,text,text,integer,varchar)
returns integer as '
declare
	edit__selection_id		alias for $1;
	edit__title			alias for $2;
	edit__description		alias for $3;
	edit__full_sql			alias for $4;
	edit__bind_vars			alias for $5;
	edit__modifying_user		alias for $6;
	edit__modifying_ip		alias for $7;
begin
		update us_selections
		set title = edit__title,
		    description = edit__description,
		    full_sql = edit__full_sql,
		    bind_vars = edit__bind_vars
		where selection_id = edit__selection_id;

		update acs_named_objects
		set object_name = edit__title
		where object_id = edit__selection_id;

		update acs_objects
		set modifying_user = edit__modifying_user,
		    modifying_ip = edit__modifying_ip
		where object_id = edit__selection_id;

		return 0;
end;' language 'plpgsql';
