numerus/deploy/edit_product.sql

-- Deploy numerus:edit_product to pg
-- requires: schema_numerus
-- requires: product
-- requires: product_tax
-- requires: parse_price
-- requires: company
-- requires: currency
-- requires: tag_name

begin;

set search_path to numerus, public;

create or replace function edit_product(slug uuid, name text, description text, price text, taxes integer[], tags tag_name[]) returns boolean
as $$
declare
	pid integer;
	company integer;
begin
	update product
	set name = edit_product.name
	  , description = edit_product.description
	  , price = parse_price(edit_product.price, decimal_digits)
	  , tags = edit_product.tags
	from company
	join currency using (currency_code)
	where product.company_id = company.company_id
	  and product.slug = edit_product.slug
	returning product_id, product.company_id
	into pid, company;

	if pid is null then
		return false;
	end if;

	delete from product_tax where product_id = pid;

	insert into product_tax(product_id, tax_id)
	select pid, tax_id
	from unnest(taxes) as tax(tax_id);

	return true;
end;
$$ language plpgsql;

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

commit;