DO $$
DECLARE temp1 integer;
DECLARE temp2 integer;
DECLARE temp3 integer;
BEGIN
    --database permission checks would go here
    // qquery and raise exception if failed

    --main creates/updates/deletes
    INSERT INTO company (title_en, title_zh) VALUES 
    ("Cool Startup", "酷启动") 
    RETURNING id INTO temp1;

    INSERT INTO job_advert (title_en, title_zh, company_id, bl_order) VALUES
    ("Director of programming", "编程总监", temp1, "FFFFAB")
    RETURNING id INTO temp2;

    INSERT INTO job_advert (title_en, title_zh, company_id, bl_order) VALUES
    ("Graphic Design", "平面设计", temp1, "FFFFAC")
    RETURNING id INTO temp3;

    INSERT INTO company_tl_ar (item_id, title) VALUES
    (temp1, "بدء تشغيل رائع");

    INSERT INTO job_advert_tl_ar (item_id, title) VALUES
    (temp2, "مدير البرمجة"),
    (temp3, "تصميم غرافيك");

    --post database validations 

    --at most 10 job adverts per a company
    IF (SELECT count(*) > 10 FROM job_advert WHERE company_id = temp1 LIMIT 11) THEN
        RAISE EXCEPTION 'FAILED CHECK 1';
    END IF
END
$$;