Cacher is the code snippet organizer for pro developers

CREATE OR REPLACE FUNCTION next_uuid() RETURNS UUID AS
$$
SELECT md5(random()::text || clock_timestamp()::text)::uuid
$$ LANGUAGE SQL IMMUTABLE;



 INSERT INTO agencies_agencyuser(token, agency_id, user_id, country, phone)
 values(next_uuid(), 35, 11, 'BD', '4545') 

CREATE OR REPLACE FUNCTION next_uuid() RETURNS UUID AS
$$
SELECT md5(random()::text || clock_timestamp()::text)::uuid
$$ LANGUAGE SQL IMMUTABLE;




/*
Coming from the input file:

  - TRADE_NAME == FISCAL_NAME
  - CIF
  - ADDRESS
  - CITY
  - POSTAL_CODE
  - COUNTRY
  - PROVINCE
  - CUSTOMER_CODE + COMMISSION
  - LANGUAGE
*/

INSERT INTO AGENCIES_AGENCY(
status, trade_name, fiscal_name, address, city, postal_code, country, commission, language,
province, tax_number, internal_comments, created_at
) VALUES (
1, /* Approved status */ 
'TEST INSERT', /* trade_name */
'TEST INSERT', /* fiscal_name */
'CALLE TEST INSERT', /* address */
'CIUDAD TEST INSERT', /* city */
'POSTAL CODE TEST INSERT', /* postal_code */
'ES', /* country */
0, /* commission --> debe venir del fichero */
'es', /* language */
'PROVINCIA TEST INSERT', /* province */
'00T', /* tax_number --> CIF EN EL FICHERO */
'', /* internal_comments */
TIMESTAMP '2019-04-15 16:00:00' /* created_at */
);


INSERT INTO AGENCIES_AGENCYUSER(token, agency_id, user_id, country, phone) VALUES (
 next_uuid(), 10, 40395, 'ES', '971'
);

CREATE OR REPLACE FUNCTION create_agency(p_AGENCY_NAME TEXT, p_ADDRESS TEXT, p_CITY TEXT, p_POSTAL_CODE TEXT, p_COUNTRY TEXT, p_COMMISSION INT, p_LANGUAGE TEXT, p_PROVINCE TEXT, p_TAX_NUMBER TEXT)
RETURNS INT AS $$
DECLARE 
 BEGIN
 
	BEGIN
		/* Function that inserts one agency every time */
		 
		INSERT INTO AGENCIES_AGENCY(
		status, trade_name, fiscal_name, address, city, postal_code, country, commission, language,
		province, tax_number, internal_comments, created_at
		) VALUES (
		1, /* Approved status */ 
		p_AGENCY_NAME, /* trade_name */
		p_AGENCY_NAME, /* fiscal_name */
		p_ADDRESS, /* address */
		p_CITY, /* city */
		p_POSTAL_CODE, /* postal_code */
		p_COUNTRY, /* country */
		p_COMMISSION, /* commission --> debe venir del fichero */
		p_LANGUAGE, /* language */
		p_PROVINCE, /* province */
		p_TAX_NUMBER, /* tax_number --> CIF EN EL FICHERO */
		'', /* internal_comments */
		TIMESTAMP '2019-04-15 16:00:00' /* created_at */
		);
 
	EXCEPTION WHEN OTHERS THEN
	END;
 
RETURN 1;
END;
$$ LANGUAGE plpgsql;
 
 
/* Beginning the script that inserts all the agencies in the file */
DO
$do$
 
 
 
BEGIN 
 

PERFORM create_agency('TEST AGENCY 1', 'CALLE NEW CALLE', 'PALMA', '07675', 'ES', 10, 'es', 'BALEARES', 'CIFX1');
PERFORM create_agency('TEST AGENCY 1', 'CALLE NEW CALLE', 'PALMA', '07675', 'ES', 10, 'es', 'BALEARES', 'CIFX2');
PERFORM create_agency('TEST AGENCY 1', 'CALLE NEW CALLE', 'PALMA', '07675', 'ES', 10, 'es', 'BALEARES', 'CIFX1');
PERFORM create_agency('TEST AGENCY 1', 'CALLE NEW CALLE', 'PALMA', '07675', 'ES', 10, 'es', 'BALEARES', 'CIFX3');
 
END
$do$;

CREATE OR REPLACE FUNCTION create_agency(p_AGENCY_NAME TEXT, p_ADDRESS TEXT, p_CITY TEXT, p_POSTAL_CODE TEXT, p_COUNTRY TEXT, p_LANGUAGE TEXT, p_PROVINCE TEXT, p_TAX_NUMBER TEXT)
RETURNS INT AS $$
DECLARE 
 BEGIN
 
	BEGIN
		/* Function that inserts one agency every time */
		 
		INSERT INTO AGENCIES_AGENCY(
		status, trade_name, fiscal_name, address, city, postal_code, country, commission, language,
		province, tax_number, internal_comments, created_at
		) VALUES (
		1, /* Approved status */ 
		p_AGENCY_NAME, /* trade_name */
		p_AGENCY_NAME, /* fiscal_name */
		p_ADDRESS, /* address */
		p_CITY, /* city */
		p_POSTAL_CODE, /* postal_code */
		p_COUNTRY, /* country */
		12, /* commission */
		p_LANGUAGE, /* language */
		p_PROVINCE, /* province */
		p_TAX_NUMBER, /* tax_number --> CIF EN EL FICHERO */
		'', /* internal_comments */
		TIMESTAMP '2019-04-16 16:00:00' /* created_at */
		);
 
	EXCEPTION WHEN OTHERS THEN
	END;
 
RETURN 1;
END;
$$ LANGUAGE plpgsql;
 
 
/* Beginning the script that inserts all the agencies in the file */
DO
$do$
 
 
 
BEGIN 
 
PERFORM create_agency('A','ADDRESS','34','CIF','ES','10','es','46005','ES');

 
END
$do$;