Funciones postgres - fundar/Curul501 GitHub Wiki

Crear campo tipo array

alter table representatives_scrapper add column trayectoria text[][];

Función en postgres

--Generar registros a partir de un array multidimensional

CREATE OR REPLACE FUNCTION unnest_multidim(anyarray)
RETURNS SETOF anyarray AS
$BODY$
  SELECT array_agg($1[series2.i][series2.x]) FROM
    (SELECT generate_series(array_lower($1,2),array_upper($1,2)) as x, series1.i
     FROM 
     (SELECT generate_series(array_lower($1,1),array_upper($1,1)) as i) series1 
    ) series2
GROUP BY series2.i
$BODY$
LANGUAGE sql IMMUTABLE;

Consulta de ejemplo con la función

select id_representative, array_to_string(unnest_multidim(trayectoria), ',') as trayectoria from representatives_scrapper  where id_representative=2;

Consulta de ejemplo con búsqueda de trayectoria

select id_representative, array_to_string(unnest_multidim(trayectoria), ',') from representatives_scrapper  where 'TRA10' = ANY(trayectoria);

Actualizar campo de trayectoria tipo array en representantes

update representatives_scrapper set trayectoria=ARRAY[ARRAY['TRA1', 'TEXTTRA'], ARRAY['TRA2', 'TEXTRA2'], ARRAY['TRA3', 'TEXTRA3'], ARRAY['TRA10', 'TEXTRA4']] where id_representative=2;