49 lines
1.1 KiB
PL/PgSQL
49 lines
1.1 KiB
PL/PgSQL
DROP FUNCTION IF EXISTS public.create_contract (
|
|
VARCHAR(20), -- p_contractor_id_number
|
|
VARCHAR(50), -- p_contractor_name
|
|
VARCHAR(100), -- p_contractor_surname
|
|
TIMESTAMP, -- p_contract_init_date
|
|
INTEGER -- p_rate_id
|
|
);
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION public.create_contract (
|
|
p_contractor_id_number VARCHAR(20),
|
|
p_contractor_name VARCHAR(50),
|
|
p_contractor_surname VARCHAR(100),
|
|
p_contract_init_date TIMESTAMP,
|
|
p_rate_id INTEGER
|
|
)
|
|
RETURNS INTEGER
|
|
AS $$
|
|
DECLARE
|
|
v_new_id INTEGER;
|
|
BEGIN
|
|
PERFORM 1
|
|
FROM public.rates
|
|
WHERE id = p_rate_id;
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION
|
|
'No reate found with id %', p_rate_id;
|
|
END IF;
|
|
|
|
INSERT INTO public.contracts (
|
|
contractoridnumber,
|
|
contractorname,
|
|
contractorsurname,
|
|
contractinitdate,
|
|
rateid
|
|
)
|
|
VALUES (
|
|
p_contractor_id_number,
|
|
p_contractor_name,
|
|
p_contractor_surname,
|
|
p_contract_init_date,
|
|
p_rate_id
|
|
)
|
|
RETURNING id INTO v_new_id;
|
|
|
|
RETURN v_new_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql VOLATILE;
|