M40B7TwF

1-- FUNCTION: public.prcinsertcurrencies(character varying, integer)
2
3-- DROP FUNCTION public.prcinsertcurrencies(character varying, integer);
4
5CREATE OR REPLACE FUNCTION public.prcinsertcurrencies(
6	pcurrencyids character varying,
7	pclientrequestid integer)
8    RETURNS void
9    LANGUAGE 'plpgsql'
10
11    COST 100
12    VOLATILE 
13AS $BODY$
14
15    
16BEGIN    
17	--create temptable
18   DROP TABLE IF EXISTS TempCurrencies;
19	CREATE TEMPORARY TABLE TempCurrencies 
20    (
21		CurrencyID int
22	);  
23	--insert all the values from the id-string
24	INSERT INTO TempCurrencies (currencyid) (select regexp_split_to_table(pcurrencyids, E'[\\s,]+')::int);
25		
26	--insert into final table all currencyids for the clientrequest 
27		INSERT INTO tblclientrequestcurrencies
28			(ClientRequestID,CurrencyID)
29		SELECT distinct
30			pClientRequestID, CurrencyID
31		FROM TempCurrencies
32		WHERE NOT EXISTS (
33			SELECT * 
34			FROM tblclientrequestcurrencies
35			WHERE TempCurrencies.CurrencyID = tblclientrequestcurrencies.CurrencyID 
36				AND pClientRequestID = tblclientrequestcurrencies.ClientRequestID
37			);
38		EXCEPTION 
39    	WHEN OTHERS THEN
40        RAISE INFO 'Error: %',SQLERRM;
41END;
42$BODY$;
43
44ALTER FUNCTION public.prcinsertcurrencies(character varying, integer)
45    OWNER TO postgres;