summaryrefslogtreecommitdiff
path: root/sql/modules/Company.sql
blob: a102fa1c8142be182f3b3975736738140f65bcdf (plain)
  1. -- BEGIN;
  2. CREATE TYPE company_search_result AS (
  3. entity_id int,
  4. entity_control_code text,
  5. company_id int,
  6. entity_credit_id int,
  7. meta_number text,
  8. credit_description text,
  9. entity_class int,
  10. legal_name text,
  11. sic_code text,
  12. business_type text,
  13. curr text
  14. );
  15. CREATE OR REPLACE FUNCTION company__search
  16. (in_account_class int, in_contact text, in_contact_info text[],
  17. in_meta_number text, in_address text, in_city text, in_state text,
  18. in_mail_code text, in_country text, in_date_from date, in_date_to date,
  19. in_business_id int, in_legal_name text, in_control_code text)
  20. RETURNS SETOF company_search_result AS $$
  21. DECLARE
  22. out_row company_search_result;
  23. loop_count int;
  24. t_contact_info text[];
  25. BEGIN
  26. t_contact_info = in_contact_info;
  27. FOR out_row IN
  28. SELECT e.id, e.control_code, c.id, ec.id, ec.meta_number,
  29. ec.description, ec.entity_class,
  30. c.legal_name, c.sic_code, b.description , ec.curr::text
  31. FROM entity e
  32. JOIN company c ON (e.id = c.entity_id)
  33. JOIN entity_credit_account ec ON (ec.entity_id = e.id)
  34. LEFT JOIN business b ON (ec.business_id = b.id)
  35. WHERE ec.entity_class = in_account_class
  36. AND (e.control_code = in_control_code
  37. or in_control_code IS NULL)
  38. AND (c.id IN (select company_id FROM company_to_contact
  39. WHERE contact LIKE ALL(t_contact_info))
  40. OR '' LIKE ALL(t_contact_info))
  41. AND (ec.meta_number = in_meta_number
  42. OR in_meta_number IS NULL)
  43. AND (c.legal_name like '%' || in_legal_name || '%'
  44. OR in_legal_name IS NULL)
  45. AND ((in_address IS NULL AND in_city IS NULL
  46. AND in_state IS NULL
  47. AND in_country IS NULL)
  48. OR (c.id IN
  49. (select company_id FROM company_to_location
  50. WHERE location_id IN
  51. (SELECT id FROM location
  52. WHERE line_one
  53. ilike '%' ||
  54. coalesce(in_address, '')
  55. || '%'
  56. AND city ILIKE
  57. '%' ||
  58. coalesce(in_city, '')
  59. || '%'
  60. AND state ILIKE
  61. '%' ||
  62. coalesce(in_state, '')
  63. || '%'
  64. AND mail_code ILIKE
  65. '%' ||
  66. coalesce(in_mail_code,
  67. '')
  68. || '%'
  69. AND country_id IN
  70. (SELECT id FROM country
  71. WHERE name LIKE '%' ||
  72. in_country ||'%'
  73. OR short_name
  74. ilike
  75. in_country)))))
  76. AND (ec.business_id =
  77. coalesce(in_business_id, ec.business_id)
  78. OR (ec.business_id IS NULL
  79. AND in_business_id IS NULL))
  80. AND (ec.startdate <= coalesce(in_date_to,
  81. ec.startdate)
  82. OR (ec.startdate IS NULL))
  83. AND (ec.enddate >= coalesce(in_date_from, ec.enddate)
  84. OR (ec.enddate IS NULL))
  85. LOOP
  86. RETURN NEXT out_row;
  87. END LOOP;
  88. END;
  89. $$ language plpgsql;
  90. CREATE OR REPLACE FUNCTION entity__save_notes(in_entity_id int, in_note text)
  91. RETURNS INT AS
  92. $$
  93. DECLARE out_id int;
  94. BEGIN
  95. -- TODO, change this to create vector too
  96. INSERT INTO entity_note (ref_key, note_class, entity_id, note, vector)
  97. VALUES (in_entity_id, 1, in_entity_id, in_note, '');
  98. SELECT currval('note_id_seq') INTO out_id;
  99. RETURN out_id;
  100. END;
  101. $$ LANGUAGE PLPGSQL;
  102. CREATE OR REPLACE FUNCTION eca__save_notes(in_credit_id int, in_note text)
  103. RETURNS INT AS
  104. $$
  105. DECLARE out_id int;
  106. BEGIN
  107. -- TODO, change this to create vector too
  108. INSERT INTO eca_note (ref_key, note_class, note, vector)
  109. VALUES (in_credit_id, 3, in_note, '');
  110. SELECT currval('note_id_seq') INTO out_id;
  111. RETURN out_id;
  112. END;
  113. $$ LANGUAGE PLPGSQL;
  114. CREATE OR REPLACE FUNCTION entity_credit_get_id_by_meta_number
  115. (in_meta_number text, in_account_class int)
  116. returns int AS
  117. $$
  118. DECLARE out_credit_id int;
  119. BEGIN
  120. SELECT id INTO out_credit_id
  121. FROM entity_credit_account
  122. WHERE meta_number = in_meta_number
  123. AND entity_class = in_account_class;
  124. RETURN out_credit_id;
  125. END;
  126. $$ LANGUAGE plpgsql;
  127. CREATE OR REPLACE FUNCTION entity_list_contact_class()
  128. RETURNS SETOF contact_class AS
  129. $$
  130. DECLARE out_row RECORD;
  131. BEGIN
  132. FOR out_row IN
  133. SELECT * FROM contact_class ORDER BY id
  134. LOOP
  135. RETURN NEXT out_row;
  136. END LOOP;
  137. END;
  138. $$ language plpgsql;
  139. CREATE TYPE entity_credit_search_return AS (
  140. legal_name text,
  141. id int,
  142. entity_id int,
  143. entity_control_code text,
  144. entity_class int,
  145. discount numeric,
  146. taxincluded bool,
  147. creditlimit numeric,
  148. terms int2,
  149. meta_number text,
  150. credit_description text,
  151. business_id int,
  152. language_code text,
  153. pricegroup_id int,
  154. curr char(3),
  155. startdate date,
  156. enddate date,
  157. ar_ap_account_id int,
  158. cash_account_id int,
  159. tax_id text,
  160. threshold numeric
  161. );
  162. CREATE TYPE entity_credit_retrieve AS (
  163. id int,
  164. entity_id int,
  165. entity_class int,
  166. discount numeric,
  167. taxincluded bool,
  168. creditlimit numeric,
  169. terms int2,
  170. meta_number text,
  171. description text,
  172. business_id int,
  173. language_code text,
  174. pricegroup_id int,
  175. curr text,
  176. startdate date,
  177. enddate date,
  178. ar_ap_account_id int,
  179. cash_account_id int,
  180. threshold numeric,
  181. control_code text,
  182. credit_id int
  183. );
  184. COMMENT ON TYPE entity_credit_search_return IS
  185. $$ This may change in 1.4 and should not be relied upon too much $$;
  186. CREATE OR REPLACE FUNCTION entity_credit_get_id
  187. (in_entity_id int, in_entity_class int, in_meta_number text)
  188. RETURNS int AS $$
  189. DECLARE out_var int;
  190. BEGIN
  191. SELECT id INTO out_var FROM entity_credit_account
  192. WHERE entity_id = in_entity_id
  193. AND in_entity_class = entity_class
  194. AND in_meta_number = meta_number;
  195. RETURN out_var;
  196. END;
  197. $$ language plpgsql;
  198. CREATE OR REPLACE FUNCTION entity__list_credit
  199. (in_entity_id int, in_entity_class int)
  200. RETURNS SETOF entity_credit_retrieve AS
  201. $$
  202. DECLARE out_row entity_credit_retrieve;
  203. BEGIN
  204. FOR out_row IN
  205. SELECT c.id, e.id, ec.entity_class, ec.discount,
  206. ec.taxincluded, ec.creditlimit, ec.terms,
  207. ec.meta_number, ec.description, ec.business_id,
  208. ec.language_code,
  209. ec.pricegroup_id, ec.curr, ec.startdate,
  210. ec.enddate, ec.ar_ap_account_id, ec.cash_account_id,
  211. ec.threshold, e.control_code, ec.id
  212. FROM company c
  213. JOIN entity e ON (c.entity_id = e.id)
  214. JOIN entity_credit_account ec ON (c.entity_id = ec.entity_id)
  215. WHERE e.id = in_entity_id
  216. AND ec.entity_class =
  217. CASE WHEN in_entity_class = 3 THEN 2
  218. WHEN in_entity_class IS NULL
  219. THEN ec.entity_class
  220. ELSE in_entity_class END
  221. LOOP
  222. RETURN NEXT out_row;
  223. END LOOP;
  224. END;
  225. $$ LANGUAGE PLPGSQL;
  226. CREATE OR REPLACE FUNCTION company_retrieve (in_entity_id int) RETURNS company AS
  227. $$
  228. DECLARE t_company company;
  229. BEGIN
  230. SELECT * INTO t_company FROM company WHERE entity_id = in_entity_id;
  231. RETURN t_company;
  232. END;
  233. $$ language plpgsql;
  234. CREATE TYPE company_billing_info AS (
  235. legal_name text,
  236. meta_number text,
  237. tax_id text,
  238. street1 text,
  239. street2 text,
  240. street3 text,
  241. city text,
  242. state text,
  243. mail_code text,
  244. country text
  245. );
  246. CREATE OR REPLACE FUNCTION company_get_billing_info (in_id int)
  247. returns company_billing_info as
  248. $$
  249. DECLARE out_var company_billing_info;
  250. t_id INT;
  251. BEGIN
  252. select c.legal_name, eca.meta_number, c.tax_id, a.line_one, a.line_two, a.line_three,
  253. a.city, a.state, a.mail_code, cc.name
  254. into out_var
  255. FROM company c
  256. JOIN entity_credit_account eca ON (eca.entity_id = c.entity_id)
  257. JOIN eca_to_location cl ON (eca.id = cl.credit_id)
  258. JOIN location a ON (a.id = cl.location_id)
  259. JOIN country cc ON (cc.id = a.country_id)
  260. WHERE eca.id = in_id AND location_class = 1;
  261. RETURN out_var;
  262. END;
  263. $$ language plpgsql;
  264. CREATE OR REPLACE FUNCTION company_save (
  265. in_id int, in_control_code text, in_entity_class int,
  266. in_name text, in_tax_id TEXT,
  267. in_entity_id int, in_sic_code text
  268. ) RETURNS INT AS $$
  269. DECLARE t_entity_id INT;
  270. t_company_id INT;
  271. t_control_code TEXT;
  272. BEGIN
  273. t_company_id := in_id;
  274. IF in_control_code IS NULL THEN
  275. t_control_code := setting_increment('company_control');
  276. ELSE
  277. t_control_code := in_control_code;
  278. END IF;
  279. IF in_entity_id IS NULL THEN
  280. IF in_id IS NULL THEN
  281. RAISE NOTICE 'in_id is null';
  282. SELECT id INTO t_company_id FROM company
  283. WHERE entity_id = (SELECT id FROM entity WHERE
  284. control_code = t_control_code);
  285. END IF;
  286. IF t_company_id IS NOT NULL THEN
  287. SELECT entity_id INTO t_entity_id FROM company
  288. WHERE id = t_company_id;
  289. END IF;
  290. ELSE
  291. t_entity_id := in_entity_id;
  292. END IF;
  293. IF t_entity_id IS NULL THEN
  294. INSERT INTO entity (name, entity_class, control_code)
  295. VALUES (in_name, in_entity_class, t_control_code);
  296. t_entity_id := currval('entity_id_seq');
  297. END IF;
  298. UPDATE company
  299. SET legal_name = in_name,
  300. tax_id = in_tax_id,
  301. sic_code = in_sic_code
  302. WHERE id = t_company_id;
  303. IF NOT FOUND THEN
  304. INSERT INTO company(entity_id, legal_name, tax_id, sic_code)
  305. VALUES (t_entity_id, in_name, in_tax_id, in_sic_code);
  306. END IF;
  307. RETURN t_entity_id;
  308. END;
  309. $$ LANGUAGE PLPGSQL;
  310. CREATE OR REPLACE FUNCTION entity_credit_save (
  311. in_credit_id int, in_entity_class int,
  312. in_entity_id int, in_description text,
  313. in_discount numeric, in_taxincluded bool, in_creditlimit numeric,
  314. in_discount_terms int,
  315. in_terms int, in_meta_number varchar(32), in_business_id int,
  316. in_language varchar(6), in_pricegroup_id int,
  317. in_curr char, in_startdate date, in_enddate date,
  318. in_threshold NUMERIC,
  319. in_ar_ap_account_id int,
  320. in_cash_account_id int
  321. ) returns INT as $$
  322. DECLARE
  323. t_entity_class int;
  324. l_id int;
  325. t_meta_number text;
  326. t_mn_default_key text;
  327. BEGIN
  328. -- TODO: Move to mapping table.
  329. IF in_entity_class = 1 THEN
  330. t_mn_default_key := 'vendornumber';
  331. ELSIF in_entity_class = 2 THEN
  332. t_mn_default_key := 'customernumber';
  333. END IF;
  334. IF in_meta_number IS NULL THEN
  335. t_meta_number := setting_increment(t_mn_default_key);
  336. ELSE
  337. t_meta_number := in_meta_number;
  338. END IF;
  339. update entity_credit_account SET
  340. discount = in_discount,
  341. taxincluded = in_taxincluded,
  342. creditlimit = in_creditlimit,
  343. description = in_description,
  344. terms = in_terms,
  345. ar_ap_account_id = in_ar_ap_account_id,
  346. cash_account_id = in_cash_account_id,
  347. meta_number = t_meta_number,
  348. business_id = in_business_id,
  349. language_code = in_language,
  350. pricegroup_id = in_pricegroup_id,
  351. curr = in_curr,
  352. startdate = in_startdate,
  353. enddate = in_enddate,
  354. threshold = in_threshold,
  355. discount_terms = in_discount_terms
  356. where id = in_credit_id;
  357. IF FOUND THEN
  358. RETURN in_credit_id;
  359. ELSE
  360. INSERT INTO entity_credit_account (
  361. entity_id,
  362. entity_class,
  363. discount,
  364. description,
  365. taxincluded,
  366. creditlimit,
  367. terms,
  368. meta_number,
  369. business_id,
  370. language_code,
  371. pricegroup_id,
  372. curr,
  373. startdate,
  374. enddate,
  375. discount_terms,
  376. threshold,
  377. ar_ap_account_id,
  378. cash_account_id
  379. )
  380. VALUES (
  381. in_entity_id,
  382. in_entity_class,
  383. in_discount,
  384. in_description,
  385. in_taxincluded,
  386. in_creditlimit,
  387. in_terms,
  388. t_meta_number,
  389. in_business_id,
  390. in_language,
  391. in_pricegroup_id,
  392. in_curr,
  393. in_startdate,
  394. in_enddate,
  395. in_discount_terms,
  396. in_threshold,
  397. in_ar_ap_account_id,
  398. in_cash_account_id
  399. );
  400. RETURN currval('entity_credit_account_id_seq');
  401. END IF;
  402. END;
  403. $$ language 'plpgsql';
  404. CREATE OR REPLACE FUNCTION company__list_locations(in_entity_id int)
  405. RETURNS SETOF location_result AS
  406. $$
  407. DECLARE out_row RECORD;
  408. BEGIN
  409. FOR out_row IN
  410. SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
  411. l.state, l.mail_code, c.name, lc.class
  412. FROM location l
  413. JOIN company_to_location ctl ON (ctl.location_id = l.id)
  414. JOIN location_class lc ON (ctl.location_class = lc.id)
  415. JOIN country c ON (c.id = l.country_id)
  416. WHERE ctl.company_id = (select id from company where entity_id = in_entity_id)
  417. ORDER BY lc.id, l.id, c.name
  418. LOOP
  419. RETURN NEXT out_row;
  420. END LOOP;
  421. END;
  422. $$ LANGUAGE PLPGSQL;
  423. CREATE TYPE contact_list AS (
  424. class text,
  425. class_id int,
  426. contact text
  427. );
  428. CREATE OR REPLACE FUNCTION company__list_contacts(in_entity_id int)
  429. RETURNS SETOF contact_list AS $$
  430. DECLARE out_row contact_list;
  431. BEGIN
  432. FOR out_row IN
  433. SELECT cl.class, cl.id, c.description, c.contact
  434. FROM company_to_contact c
  435. JOIN contact_class cl ON (c.contact_class_id = cl.id)
  436. WHERE company_id =
  437. (select id FROM company
  438. WHERE entity_id = in_entity_id)
  439. LOOP
  440. return next out_row;
  441. END LOOP;
  442. END;
  443. $$ language plpgsql;
  444. CREATE OR REPLACE FUNCTION company__list_bank_account(in_entity_id int)
  445. RETURNS SETOF entity_bank_account AS
  446. $$
  447. DECLARE out_row entity_bank_account%ROWTYPE;
  448. BEGIN
  449. FOR out_row IN
  450. SELECT * from entity_bank_account where entity_id = in_entity_id
  451. LOOP
  452. RETURN NEXT out_row;
  453. END LOOP;
  454. END;
  455. $$ LANGUAGE PLPGSQL;
  456. CREATE OR REPLACE FUNCTION entity__save_bank_account
  457. (in_entity_id int, in_credit_id int, in_bic text, in_iban text)
  458. RETURNS int AS
  459. $$
  460. DECLARE out_id int;
  461. BEGIN
  462. INSERT INTO entity_bank_account(entity_id, bic, iban)
  463. VALUES(in_entity_id, in_bic, in_iban);
  464. SELECT CURRVAL('entity_bank_account_id_seq') INTO out_id ;
  465. IF in_credit_id IS NOT NULL THEN
  466. UPDATE entity_credit_account SET bank_account = out_id
  467. WHERE id = in_credit_id;
  468. END IF;
  469. RETURN out_id;
  470. END;
  471. $$ LANGUAGE PLPGSQL;
  472. CREATE OR REPLACE FUNCTION entity__save_bank_account
  473. (in_entity_id int, in_bic text, in_iban text)
  474. RETURNS int AS
  475. $$
  476. DECLARE out_id int;
  477. BEGIN
  478. INSERT INTO entity_bank_account(entity_id, bic, iban)
  479. VALUES(in_entity_id, in_bic, in_iban);
  480. SELECT CURRVAL('entity_bank_account_id_seq') INTO out_id ;
  481. RETURN out_id;
  482. END;
  483. $$ LANGUAGE PLPGSQL;
  484. CREATE OR REPLACE FUNCTION company__save_contact
  485. (in_entity_id int, in_contact_class int, in_description text, in_contact text)
  486. RETURNS INT AS
  487. $$
  488. DECLARE out_id int;
  489. BEGIN
  490. INSERT INTO company_to_contact(company_id, contact_class_id,
  491. description, contact)
  492. SELECT id, in_contact_class, in_description, in_contact FROM company
  493. WHERE entity_id = in_entity_id;
  494. RETURN 1;
  495. END;
  496. $$ LANGUAGE PLPGSQL;
  497. CREATE TYPE entity_note_list AS (
  498. id int,
  499. note_class int,
  500. note text
  501. );
  502. CREATE OR REPLACE FUNCTION company__list_notes(in_entity_id int)
  503. RETURNS SETOF entity_note AS
  504. $$
  505. DECLARE out_row record;
  506. BEGIN
  507. FOR out_row IN
  508. SELECT *
  509. FROM entity_note
  510. WHERE ref_key = in_entity_id
  511. ORDER BY created
  512. LOOP
  513. RETURN NEXT out_row;
  514. END LOOP;
  515. END;
  516. $$ LANGUAGE PLPGSQL;
  517. CREATE OR REPLACE FUNCTION eca__list_notes(in_credit_id int)
  518. RETURNS SETOF note AS
  519. $$
  520. DECLARE out_row record;
  521. t_entity_id int;
  522. BEGIN
  523. SELECT entity_id INTO t_entity_id
  524. FROM entity_credit_account
  525. WHERE id = in_credit_id;
  526. FOR out_row IN
  527. SELECT *
  528. FROM note
  529. WHERE (note_class = 3 and ref_key = in_credit_id) or
  530. (note_class = 1 and ref_key = t_entity_id)
  531. ORDER BY created
  532. LOOP
  533. RETURN NEXT out_row;
  534. END LOOP;
  535. END;
  536. $$ LANGUAGE PLPGSQL SECURITY DEFINER;
  537. REVOKE EXECUTE ON FUNCTION eca__list_notes(INT) FROM public;
  538. CREATE OR REPLACE FUNCTION company__next_id() returns bigint as $$
  539. select nextval('company_id_seq');
  540. $$ language 'sql';
  541. CREATE OR REPLACE FUNCTION company__location_save (
  542. in_entity_id int, in_location_id int,
  543. in_location_class int, in_line_one text, in_line_two text,
  544. in_city TEXT, in_state TEXT, in_mail_code text, in_country_code int,
  545. in_created date
  546. ) returns int AS $$
  547. BEGIN
  548. return _entity_location_save(
  549. in_entity_id, in_location_id,
  550. in_location_class, in_line_one, in_line_two,
  551. '', in_city , in_state, in_mail_code, in_country_code);
  552. END;
  553. $$ language 'plpgsql';
  554. create or replace function _entity_location_save(
  555. in_entity_id int, in_location_id int,
  556. in_location_class int, in_line_one text, in_line_two text,
  557. in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
  558. in_country_code int
  559. ) returns int AS $$
  560. DECLARE
  561. l_row location;
  562. l_id INT;
  563. t_company_id int;
  564. BEGIN
  565. SELECT id INTO t_company_id
  566. FROM company WHERE entity_id = in_entity_id;
  567. DELETE FROM company_to_location
  568. WHERE company_id = t_company_id
  569. AND location_class = in_location_class
  570. AND location_id = in_location_id;
  571. SELECT location_save(in_line_one, in_line_two, in_line_three, in_city,
  572. in_state, in_mail_code, in_country_code)
  573. INTO l_id;
  574. INSERT INTO company_to_location
  575. (company_id, location_class, location_id)
  576. VALUES (t_company_id, in_location_class, l_id);
  577. RETURN l_id;
  578. END;
  579. $$ language 'plpgsql';
  580. create or replace function eca__location_save(
  581. in_credit_id int, in_location_id int,
  582. in_location_class int, in_line_one text, in_line_two text,
  583. in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
  584. in_country_code int
  585. ) returns int AS $$
  586. DECLARE
  587. l_row location;
  588. l_id INT;
  589. BEGIN
  590. DELETE FROM eca_to_location
  591. WHERE credit_id = in_credit_id
  592. AND location_class = in_location_class
  593. AND location_id = in_location_id;
  594. SELECT location_save(in_line_one, in_line_two, in_line_three, in_city,
  595. in_state, in_mail_code, in_country_code)
  596. INTO l_id;
  597. INSERT INTO eca_to_location
  598. (credit_id, location_class, location_id)
  599. VALUES (in_credit_id, in_location_class, l_id);
  600. RETURN l_id;
  601. END;
  602. $$ language 'plpgsql';
  603. CREATE OR REPLACE FUNCTION company_get_billing_info (in_id int)
  604. returns company_billing_info as
  605. $$
  606. DECLARE out_var company_billing_info;
  607. t_id INT;
  608. BEGIN
  609. select c.legal_name, c.tax_id, a.line_one, a.line_two, a.line_three,
  610. a.city, a.state, a.mail_code, cc.name
  611. into out_var
  612. FROM company c
  613. JOIN entity_credit_account eca ON (eca.entity_id = c.entity_id)
  614. JOIN eca_to_location cl ON (eca.id = cl.credit_id)
  615. JOIN location a ON (a.id = cl.location_id)
  616. JOIN country cc ON (cc.id = a.country_id)
  617. WHERE eca.id = in_id AND location_class = 1;
  618. RETURN out_var;
  619. END;
  620. $$ language plpgsql;
  621. CREATE OR REPLACE FUNCTION eca__list_locations(in_credit_id int)
  622. RETURNS SETOF location_result AS
  623. $$
  624. DECLARE out_row RECORD;
  625. BEGIN
  626. FOR out_row IN
  627. SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
  628. l.state, l.mail_code, c.name, lc.class
  629. FROM location l
  630. JOIN eca_to_location ctl ON (ctl.location_id = l.id)
  631. JOIN location_class lc ON (ctl.location_class = lc.id)
  632. JOIN country c ON (c.id = l.country_id)
  633. WHERE ctl.credit_id = in_credit_id
  634. ORDER BY lc.id, l.id, c.name
  635. LOOP
  636. RETURN NEXT out_row;
  637. END LOOP;
  638. END;
  639. $$ LANGUAGE PLPGSQL;
  640. CREATE OR REPLACE FUNCTION eca__list_contacts(in_credit_id int)
  641. RETURNS SETOF contact_list AS $$
  642. DECLARE out_row contact_list;
  643. BEGIN
  644. FOR out_row IN
  645. SELECT cl.class, cl.id, c.description, c.contact
  646. FROM eca_to_contact c
  647. JOIN contact_class cl ON (c.contact_class_id = cl.id)
  648. WHERE credit_id = in_credit_id
  649. LOOP
  650. return next out_row;
  651. END LOOP;
  652. END;
  653. $$ language plpgsql;
  654. CREATE OR REPLACE FUNCTION eca__save_contact
  655. (in_credit_id int, in_contact_class int, in_description text, in_contact text,
  656. in_old_contact text, in_old_contact_class int)
  657. RETURNS INT AS
  658. $$
  659. DECLARE out_id int;
  660. BEGIN
  661. DELETE FROM eca_to_contact
  662. WHERE credit_id = in_credit_id
  663. AND contact = in_old_contact
  664. AND contact_class_id = in_old_contact_class;
  665. INSERT INTO eca_to_contact(credit_id, contact_class_id,
  666. description, contact)
  667. VALUES (in_credit_id, in_contact_class, in_description, in_contact);
  668. RETURN 1;
  669. END;
  670. $$ LANGUAGE PLPGSQL;
  671. -- COMMIT;