numerus/deploy/edit_expense.sql

-- Deploy numerus:edit_expense to pg
-- requires: schema_numerus
-- requires: expense
-- requires: currency
-- requires: parse_price
-- requires: tax
-- requires: tag_name
-- requires: expense_status
-- requires: expense_expense_status

begin;

set search_path to numerus, public;

create or replace function edit_expense(expense_slug uuid, status text, invoice_date date, contact_id integer, invoice_number text, amount text, taxes integer[], tags tag_name[]) returns uuid as
$$
declare
	eid integer;
begin
	update expense
	set invoice_date = edit_expense.invoice_date
	  , contact_id = edit_expense.contact_id
	  , invoice_number = edit_expense.invoice_number
	  , amount = parse_price(edit_expense.amount, decimal_digits)
	  , expense_status = status
	  , tags = edit_expense.tags
	from currency
	where slug = expense_slug
	and currency.currency_code = expense.currency_code
	returning expense_id
	into eid;

	if eid is null then
		return null;
	end if;

	delete from expense_tax where expense_id = eid;

	insert into expense_tax (expense_id, tax_id, tax_rate)
	select eid, tax_id, tax.rate
	from tax
	join unnest(taxes) as etax(tax_id) using (tax_id);

	return expense_slug;
end;
$$
language plpgsql;

revoke execute on function edit_expense(uuid, text, date, integer, text, text, integer[], tag_name[]) from public;
grant execute on function edit_expense(uuid, text, date, integer, text, text, integer[], tag_name[]) to invoicer;
grant execute on function edit_expense(uuid, text, date, integer, text, text, integer[], tag_name[]) to admin;

commit;