Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create type warehouse_type as (id int, name varchar, receiving bool, responsible_person_id int, responsible_person_username varchar);
- CREATE OR REPLACE FUNCTION warehouse.get_warehouse(warehouse_id int)
- RETURNS SETOF warehouse_type AS $$
- DECLARE
- result_warehouse_id int;
- BEGIN
- SELECT id INTO result_warehouse_id FROM warehouse.warehouse WHERE id = warehouse_id;
- IF result_warehouse_id IS NULL THEN
- RAISE EXCEPTION USING message = 'No warehouse with id = ' || warehouse_id, errcode = '22004';
- END IF;
- RETURN QUERY SELECT w.id, w.name, w.receiving, u.user_id as responsible_person_id, u.user_username as responsible_person_username
- FROM warehouse.warehouse w
- join main.get_users_view u on u.user_id = responsible_person_id
- WHERE id = result_warehouse_id;
- END
- $$ LANGUAGE plpgsql;
- select * from warehouse.get_warehouse(1)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement