Forums PostgreSQL.fr

create or replace function GenUnifCatal() returns void as $$
	declare 
		-- Curseur pour parcourir la table META
		meta_cursor cursor for select table_name, trans_code from META;
		-- Variables pour stocker les données de chaque catalogue
		catalog_name VARCHAR(255);
		transformation_code VARCHAR(10);
		-- Variables pour stocker les attributs de catalogue
		name_attribute VARCHAR(50);
		price_attribute numeric(8, 2);
		-- Variables pour stocker les données à charger dans C_ALL
		product_name VARCHAR(50);
		product_price NUMERIC(8, 2);
	begin
		-- detruit C_ALL si existe
		drop table if exists C_ALL;
		-- crée la table C_ALL
		create table C_ALL (pid serial primary key,
							pname varchar,
							pprice numeric);
		-- Ouvre le curseur
    	open meta_cursor;
		-- Boucle pour parcourir la table META
        fetch meta_cursor into catalog_name, transformation_code;
    	
       	while found loop
		
	    	-- Recherche dynamique des attributs "name" et "price" dans le catalogue
        	select column_name into name_attribute from information_schema.columns where table_name = catalog_name and column_name LIKE '%name%';
        	select column_name into price_attribute from information_schema.columns where table_name = catalog_name and column_name LIKE '%price%';
       	 	
        	-- Dynamiquement charge les données de chaque catalogue
        	EXECUTE format("insert into C_ALL (pname, pprice)
                        values( case when %I like '%CAP%' then upper(%I) 
                                else %I
                            	end, 
                            	case when %I like '%CUR%' then (%I * 1.05)
                                else %I
								end);") using transformation_code, name_attribute, name_attribute, transformation_code, price_attribute, price_attribute);
			fetch meta_cursor into catalog_name, transformation_code;
    	end loop;

    	-- Ferme le curseur
    	close meta_cursor;
	end;
$$ language plpgsql;

CREATE TABLE C1(
  product_id NUMERIC(5) PRIMARY KEY,
  product_name VARCHAR(50),
  product_price NUMERIC(8,2)
);
  
INSERT INTO C1 VALUES(1, 'COMPUTER', 799.9);  
INSERT INTO C1 VALUES(2, 'MONITOR', 349.9); 

CREATE TABLE C2(
  id NUMERIC(5) PRIMARY KEY,
  price NUMERIC(8,2),
  name VARCHAR(50)
);  
  
INSERT INTO C2 VALUES(10, 299.9, 'Printer');  
INSERT INTO C2 VALUES(20, 149.9, 'Scanner');   
  
CREATE TABLE C3(
  pname VARCHAR(50),
  pprice NUMERIC(8,2)
);  
  
INSERT INTO C3 VALUES('mouse', 29.9);  
INSERT INTO C3 VALUES('webcam', 19.9);     
  
CREATE TABLE meta(
  table_name VARCHAR(255),
  trans_code VARCHAR(10)
);
  
INSERT INTO meta VALUES('C1', NULL);  
INSERT INTO meta VALUES('C2', 'CAP');  
INSERT INTO meta VALUES('C3', 'CAP+CUR'); 

COMMIT;