Advertisement
Guest User

Untitled

a guest
May 21st, 2019
81
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create type warehouse_type as (id int, name varchar, receiving bool, responsible_person_id int, responsible_person_username varchar);
  2.  
  3. CREATE OR REPLACE FUNCTION warehouse.get_warehouse(warehouse_id int)
  4. RETURNS SETOF warehouse_type AS $$
  5. DECLARE
  6.     result_warehouse_id int;
  7. BEGIN
  8.    SELECT id INTO result_warehouse_id FROM warehouse.warehouse WHERE id = warehouse_id;
  9.    IF result_warehouse_id IS NULL THEN
  10.         RAISE EXCEPTION USING message = 'No warehouse with id = ' || warehouse_id, errcode = '22004';
  11.    END IF;
  12.    RETURN QUERY SELECT w.id, w.name, w.receiving, u.user_id as responsible_person_id, u.user_username as responsible_person_username
  13.    FROM warehouse.warehouse w
  14.    join main.get_users_view u on u.user_id = responsible_person_id
  15.    WHERE id = result_warehouse_id;
  16. END
  17. $$ LANGUAGE plpgsql;
  18.  
  19. select * from  warehouse.get_warehouse(1)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement