crea tablas a partir de una tabla maestra. Se visualiza uso de queries dinamicos, cursores de referencia y ciclos sencillos
Código SQL:
Ver originalCREATE OR REPLACE FUNCTION p_poblaingas_subtables(VARCHAR) RETURNS VOID AS $$
DECLARE
rec refcursor;
SQL VARCHAR;
city ALIAS FOR $1;
year_mi INTEGER;
year_ma INTEGER;
i INTEGER;
BEGIN
SQL := 'SELECT MIN(periodo) AS min,MAX(periodo) AS max
FROM servcon_poblaingas_'||city;
OPEN rec FOR EXECUTE SQL;
FETCH rec INTO year_mi,year_ma;
i := year_mi;
LOOP
SQL := 'CREATE TABLE servcon_poblaingas_'||city||'_'||i||' AS
SELECT * FROM servcon_poblaingas_'||city||' WHERE periodo = '||i;
EXECUTE SQL;
i := i + 1;
EXIT WHEN i > year_ma;
END LOOP;
RETURN;
END;
$$LANGUAGE plpgsql;
SELECT p_poblaingas_subtables('05001');