fort-git
5/25/2018 - 6:37 PM
Funciones PostgreSQL
Sintaxis de creación de funciones
CREATE TEMP TABLE tmp_padron_apoyos AS
SELECT
capmun.nombre AS "Municipio de captura",
p.primer_apellido AS "Primer apellido",
p.segundo_apellido AS "Segundo apellido",
p.nombre AS "Nombre(s)",
s.nombre AS "Sexo",
p.fecha_nacimiento AS "Fecha de nacimiento",
p.curp AS "CURP",
m.id AS "Cve. municipio",
m.nombre AS "Municipio",
l.id AS "Cve. localidad",
l.nombre AS "Localidad",
ctseg.clave AS "Cve. CT SEG",
CASE COALESCE(p.calle, '') WHEN '' THEN 'S/R' ELSE p.calle END AS "Calle",
CASE COALESCE(p.colonia, '') WHEN '' THEN 'S/R' ELSE p.colonia END AS "Colonia",
CASE COALESCE(p.numero_ext, '') WHEN '' THEN 'S/R' ELSE p.numero_ext END AS "Num. exterior",
ct."DAFNNA-PREVERP-Chimalli Escolar",ct."DAFNNA-PREVERP-Conferencias",ct."DAFNNA-PREVERP-Foros (adicciones)",ct."DAFNNA-PREVERP-Foros(adicciones)-Estatal",ct."DAFNNA-PREVERP-Foros (suicidio)-Estatal",ct."DAFNNA-PREVERP-Platicas",ct."DAFNNA-PREVERP-Platicas(adicciones)-Estatal",ct."DAFNNA-PREVERP-Talleres"
FROM crosstab(
'SELECT
cp.id,
a.nombre_corto,
sum(cp.cantidad_apoyo) AS cantidad_apoyo
FROM tbl_captura_padrones AS cp
INNER JOIN tbl_capturas AS c ON c.id = cp.capturas_id
INNER JOIN vw_apoyos_simplificado AS a ON a.id = c.apoyos_id
INNER JOIN tbl_programas_dif AS pdif ON pdif.id = a.programas_dif_id
INNER JOIN tbl_direcciones_dif AS ddif ON ddif.id = pdif.direcciones_dif_id
WHERE cp.capturas_id IN(287343,287350,287351,287355,287369,287375,287386,287465,287504,287529,287553,287602,287653,287654,287659,287668,287681,287688,287718,287735,287742,287743,287756,287762,287796,287799,287802,287804,287808,287815,287826,287834,287844,287845,287862,287865,287919,287920,287938,287950,287952,287995,288016,288023,288048,288054,288119,288120,288199,288210,288211,288216,288265,288271,288274,288311,288352,288373,288375,288402,288404,288420,288441,288463,288464,288480,288526,288551,288559,288594,288595,288599,288614,288619,288621,288623,288626,288634,288645,288649,288742,288756,288788,288824,288827,288848,288857,288872,288875,288887,288905,288906,288909,288931,288937,288949,288960,289003,289053,289055,289059,289061,289073,289080,289083,289123,289141,289165,289166,289262,289265,289269,289274,289343,289346,289392,289409,289459,289461,289529,289534,289564,289565,289589,289623,289633,289645,289661,289687,289706,289707,289742,289763,289804,289826,289827,289833,289838,289840,289845,289851,289857,289879,289930,289932,289956,290008,290009,290020,290047,290064,290068,290132,290179,290207,290258,290264,290288,290293,290334,290375,290427,290696)
GROUP BY cp.id, a.nombre_corto,c.municipios_id
ORDER BY cp.id, a.nombre_corto',
'SELECT DISTINCT
a.nombre_corto
FROM tbl_capturas AS c
INNER JOIN vw_apoyos_simplificado AS a ON a.id = c.apoyos_id
INNER JOIN tbl_programas_dif AS pdif ON pdif.id = a.programas_dif_id
INNER JOIN tbl_direcciones_dif AS ddif ON ddif.id = pdif.direcciones_dif_id
WHERE c.id IN(287343,287350,287351,287355,287369,287375,287386,287465,287504,287529,287553,287602,287653,287654,287659,287668,287681,287688,287718,287735,287742,287743,287756,287762,287796,287799,287802,287804,287808,287815,287826,287834,287844,287845,287862,287865,287919,287920,287938,287950,287952,287995,288016,288023,288048,288054,288119,288120,288199,288210,288211,288216,288265,288271,288274,288311,288352,288373,288375,288402,288404,288420,288441,288463,288464,288480,288526,288551,288559,288594,288595,288599,288614,288619,288621,288623,288626,288634,288645,288649,288742,288756,288788,288824,288827,288848,288857,288872,288875,288887,288905,288906,288909,288931,288937,288949,288960,289003,289053,289055,289059,289061,289073,289080,289083,289123,289141,289165,289166,289262,289265,289269,289274,289343,289346,289392,289409,289459,289461,289529,289534,289564,289565,289589,289623,289633,289645,289661,289687,289706,289707,289742,289763,289804,289826,289827,289833,289838,289840,289845,289851,289857,289879,289930,289932,289956,290008,290009,290020,290047,290064,290068,290132,290179,290207,290258,290264,290288,290293,290334,290375,290427,290696) ORDER BY a.nombre_corto'
)AS ct(
id integer,
"DAFNNA-PREVERP-Chimalli Escolar" varchar ,"DAFNNA-PREVERP-Conferencias" varchar ,"DAFNNA-PREVERP-Foros (adicciones)" varchar ,"DAFNNA-PREVERP-Foros(adicciones)-Estatal" varchar ,"DAFNNA-PREVERP-Foros (suicidio)-Estatal" varchar ,"DAFNNA-PREVERP-Platicas" varchar ,"DAFNNA-PREVERP-Platicas(adicciones)-Estatal" varchar ,"DAFNNA-PREVERP-Talleres" varchar
)
INNER JOIN tbl_captura_padrones AS cappad ON cappad.capturas_id IN(287343,287350,287351,287355,287369,287375,287386,287465,287504,287529,287553,287602,287653,287654,287659,287668,287681,287688,287718,287735,287742,287743,287756,287762,287796,287799,287802,287804,287808,287815,287826,287834,287844,287845,287862,287865,287919,287920,287938,287950,287952,287995,288016,288023,288048,288054,288119,288120,288199,288210,288211,288216,288265,288271,288274,288311,288352,288373,288375,288402,288404,288420,288441,288463,288464,288480,288526,288551,288559,288594,288595,288599,288614,288619,288621,288623,288626,288634,288645,288649,288742,288756,288788,288824,288827,288848,288857,288872,288875,288887,288905,288906,288909,288931,288937,288949,288960,289003,289053,289055,289059,289061,289073,289080,289083,289123,289141,289165,289166,289262,289265,289269,289274,289343,289346,289392,289409,289459,289461,289529,289534,289564,289565,289589,289623,289633,289645,289661,289687,289706,289707,289742,289763,289804,289826,289827,289833,289838,289840,289845,289851,289857,289879,289930,289932,289956,290008,290009,290020,290047,290064,290068,290132,290179,290207,290258,290264,290288,290293,290334,290375,290427,290696)
AND cappad.id = ct.id
INNER JOIN tbl_capturas AS cap ON cap.id = cappad.capturas_id
INNER JOIN tbl_municipios AS capmun ON capmun.id = cap.municipios_id
INNER JOIN tbl_padrones AS p ON p.id = cappad.padrones_id
INNER JOIN tbl_sexos AS s ON s.id = p.sexos_id
INNER JOIN tbl_localidades AS l ON l.id = p.domicilio_localidades_id
INNER JOIN tbl_municipios AS m ON m.id = l.municipios_id
LEFT JOIN tbl_centros_trabajo_seg AS ctseg ON ctseg.id = p.centros_trabajo_seg_id
ORDER BY capmun.nombre, p.primer_apellido, p.segundo_apellido, p.nombre;
----------------------------------------------------------------
SELECT
datos."Municipio de captura",
datos."Primer apellido",
datos."Segundo apellido",
datos."Nombre(s)",
datos."Sexo",
datos."Fecha de nacimiento",
datos."CURP",
datos."Cve. municipio",
datos."Municipio",
datos."Cve. localidad",
datos."Localidad",
datos."Cve. CT SEG",
datos."Calle",
datos."Colonia",
datos."Num. exterior",
sum(datos."DAFNNA-PREVERP-Chimalli Escolar"::integer) AS "DAFNNA-PREVERP-Chimalli Escolar",
sum(datos."DAFNNA-PREVERP-Conferencias"::integer) AS "DAFNNA-PREVERP-Conferencias",
sum(datos."DAFNNA-PREVERP-Conferencias"::integer) AS "DAFNNA-PREVERP-Conferencias",
sum(datos."DAFNNA-PREVERP-Foros (adicciones)"::integer) AS "DAFNNA-PREVERP-Foros (adicciones)",
sum(datos."DAFNNA-PREVERP-Foros(adicciones)-Estatal"::integer) AS "DAFNNA-PREVERP-Foros(adicciones)-Estatal",
sum(datos."DAFNNA-PREVERP-Foros (suicidio)-Estatal"::integer) AS "DAFNNA-PREVERP-Foros (suicidio)-Estatal",
sum(datos."DAFNNA-PREVERP-Platicas"::integer) AS "DAFNNA-PREVERP-Platicas",
sum(datos."DAFNNA-PREVERP-Platicas(adicciones)-Estatal"::integer) AS "DAFNNA-PREVERP-Platicas(adicciones)-Estatal",
sum(datos."DAFNNA-PREVERP-Talleres"::integer) AS "DAFNNA-PREVERP-Talleres"
FROM tmp_padron_apoyos as datos WHERE datos."CURP" like 'AANA040606HGTVJRA2'
GROUP BY
datos."Municipio de captura",
datos."Primer apellido",
datos."Segundo apellido",
datos."Nombre(s)",
datos."Sexo",
datos."Fecha de nacimiento",
datos."CURP",
datos."Cve. municipio",
datos."Municipio",
datos."Cve. localidad",
datos."Localidad",
datos."Cve. CT SEG",
datos."Calle",
datos."Colonia",
datos."Num. exterior";
--------------------------------------------------------------------------------------------
SELECT * from fn_beneficiarios_apoyos('SELECT p.id , p.nombre FROM tbl_padrones as p limit 5');
CREATE OR REPLACE FUNCTION fn_beneficiarios_apoyos(_sql text) RETURNS TABLE(id integer, nombre varchar) AS $$
BEGIN
EXECUTE E'CREATE TEMP TABLE tmp_padron_apoyos_2 AS
SELECT
capmun.nombre AS \"Municipio de captura\",
p.primer_apellido AS \"Primer apellido\",
p.segundo_apellido AS \"Segundo apellido\",
p.nombre AS \"Nombre(s)\",
s.nombre AS \"Sexo\",
p.fecha_nacimiento AS \"Fecha de nacimiento\",
p.curp AS \"CURP\",
m.id AS \"Cve. municipio\",
m.nombre AS \"Municipio\",
l.id AS \"Cve. localidad\",
l.nombre AS \"Localidad\",
ctseg.clave AS \"Cve. CT SEG\",
CASE COALESCE(p.calle, '') WHEN '' THEN \'S/R\' ELSE p.calle END AS \"Calle\",
CASE COALESCE(p.colonia, '') WHEN '' THEN \'S/R\' ELSE p.colonia END AS "Colonia",
CASE COALESCE(p.numero_ext, '') WHEN '' THEN \'S/R\' ELSE p.numero_ext END AS "Num. exterior",
ct.\"DAFNNA-PREVERP-Chimalli Escolar\",
ct.\"DAFNNA-PREVERP-Conferencias\",
ct.\"DAFNNA-PREVERP-Foros (adicciones)\",
ct.\"DAFNNA-PREVERP-Foros(adicciones)-Estatal\",
ct.\"DAFNNA-PREVERP-Foros (suicidio)-Estatal\",
ct.\"DAFNNA-PREVERP-Platicas\",
ct.\"DAFNNA-PREVERP-Platicas(adicciones)-Estatal\",
ct.\"DAFNNA-PREVERP-Talleres\"
FROM crosstab(' ||
'SELECT
cp.id,
a.nombre_corto,
sum(cp.cantidad_apoyo) AS cantidad_apoyo
FROM tbl_captura_padrones AS cp
INNER JOIN tbl_capturas AS c ON c.id = cp.capturas_id
INNER JOIN vw_apoyos_simplificado AS a ON a.id = c.apoyos_id
INNER JOIN tbl_programas_dif AS pdif ON pdif.id = a.programas_dif_id
INNER JOIN tbl_direcciones_dif AS ddif ON ddif.id = pdif.direcciones_dif_id
WHERE cp.capturas_id IN(287343,287350,287351,287355,287369,287375,287386,287465,287504,287529,287553,287602,287653,287654,287659,287668,287681,287688,287718,287735,287742,287743,287756,287762,287796,287799,287802,287804,287808,287815,287826,287834,287844,287845,287862,287865,287919,287920,287938,287950,287952,287995,288016,288023,288048,288054,288119,288120,288199,288210,288211,288216,288265,288271,288274,288311,288352,288373,288375,288402,288404,288420,288441,288463,288464,288480,288526,288551,288559,288594,288595,288599,288614,288619,288621,288623,288626,288634,288645,288649,288742,288756,288788,288824,288827,288848,288857,288872,288875,288887,288905,288906,288909,288931,288937,288949,288960,289003,289053,289055,289059,289061,289073,289080,289083,289123,289141,289165,289166,289262,289265,289269,289274,289343,289346,289392,289409,289459,289461,289529,289534,289564,289565,289589,289623,289633,289645,289661,289687,289706,289707,289742,289763,289804,289826,289827,289833,289838,289840,289845,289851,289857,289879,289930,289932,289956,290008,290009,290020,290047,290064,290068,290132,290179,290207,290258,290264,290288,290293,290334,290375,290427,290696)
GROUP BY cp.id, a.nombre_corto,c.municipios_id
ORDER BY cp.id, a.nombre_corto' || ',' ||
'SELECT DISTINCT
a.nombre_corto
FROM tbl_capturas AS c
INNER JOIN vw_apoyos_simplificado AS a ON a.id = c.apoyos_id
INNER JOIN tbl_programas_dif AS pdif ON pdif.id = a.programas_dif_id
INNER JOIN tbl_direcciones_dif AS ddif ON ddif.id = pdif.direcciones_dif_id
WHERE c.id IN(287343,287350,287351,287355,287369,287375,287386,287465,287504,287529,287553,287602,287653,287654,287659,287668,287681,287688,287718,287735,287742,287743,287756,287762,287796,287799,287802,287804,287808,287815,287826,287834,287844,287845,287862,287865,287919,287920,287938,287950,287952,287995,288016,288023,288048,288054,288119,288120,288199,288210,288211,288216,288265,288271,288274,288311,288352,288373,288375,288402,288404,288420,288441,288463,288464,288480,288526,288551,288559,288594,288595,288599,288614,288619,288621,288623,288626,288634,288645,288649,288742,288756,288788,288824,288827,288848,288857,288872,288875,288887,288905,288906,288909,288931,288937,288949,288960,289003,289053,289055,289059,289061,289073,289080,289083,289123,289141,289165,289166,289262,289265,289269,289274,289343,289346,289392,289409,289459,289461,289529,289534,289564,289565,289589,289623,289633,289645,289661,289687,289706,289707,289742,289763,289804,289826,289827,289833,289838,289840,289845,289851,289857,289879,289930,289932,289956,290008,290009,290020,290047,290064,290068,290132,290179,290207,290258,290264,290288,290293,290334,290375,290427,290696)
ORDER BY a.nombre_corto' ||
')AS ct(
id integer,
\"DAFNNA-PREVERP-Chimalli Escolar\" varchar ,\"DAFNNA-PREVERP-Conferencias\" varchar ,\"DAFNNA-PREVERP-Foros (adicciones)\" varchar ,\"DAFNNA-PREVERP-Foros(adicciones)-Estatal\" varchar ,\"DAFNNA-PREVERP-Foros (suicidio)-Estatal\" varchar ,\"DAFNNA-PREVERP-Platicas\" varchar ,\"DAFNNA-PREVERP-Platicas(adicciones)-Estatal\" varchar ,\"DAFNNA-PREVERP-Talleres\" varchar
)
INNER JOIN tbl_captura_padrones AS cappad ON cappad.capturas_id IN(287343,287350,287351,287355,287369,287375,287386,287465,287504,287529,287553,287602,287653,287654,287659,287668,287681,287688,287718,287735,287742,287743,287756,287762,287796,287799,287802,287804,287808,287815,287826,287834,287844,287845,287862,287865,287919,287920,287938,287950,287952,287995,288016,288023,288048,288054,288119,288120,288199,288210,288211,288216,288265,288271,288274,288311,288352,288373,288375,288402,288404,288420,288441,288463,288464,288480,288526,288551,288559,288594,288595,288599,288614,288619,288621,288623,288626,288634,288645,288649,288742,288756,288788,288824,288827,288848,288857,288872,288875,288887,288905,288906,288909,288931,288937,288949,288960,289003,289053,289055,289059,289061,289073,289080,289083,289123,289141,289165,289166,289262,289265,289269,289274,289343,289346,289392,289409,289459,289461,289529,289534,289564,289565,289589,289623,289633,289645,289661,289687,289706,289707,289742,289763,289804,289826,289827,289833,289838,289840,289845,289851,289857,289879,289930,289932,289956,290008,290009,290020,290047,290064,290068,290132,290179,290207,290258,290264,290288,290293,290334,290375,290427,290696)
AND cappad.id = ct.id
INNER JOIN tbl_capturas AS cap ON cap.id = cappad.capturas_id
INNER JOIN tbl_municipios AS capmun ON capmun.id = cap.municipios_id
INNER JOIN tbl_padrones AS p ON p.id = cappad.padrones_id
INNER JOIN tbl_sexos AS s ON s.id = p.sexos_id
INNER JOIN tbl_localidades AS l ON l.id = p.domicilio_localidades_id
INNER JOIN tbl_municipios AS m ON m.id = l.municipios_id
LEFT JOIN tbl_centros_trabajo_seg AS ctseg ON ctseg.id = p.centros_trabajo_seg_id
ORDER BY capmun.nombre, p.primer_apellido, p.segundo_apellido, p.nombre';
RETURN QUERY
EXECUTE 'SELECT p.id , p.nombre FROM tbl_padrones as p limit 5';
END;
$$ LANGUAGE plpgsql;CREATE FUNCTION mostrar_cliente(id integer) RETURNS tbl_padrones AS $$
SELECT * FROM tbl_padrones WHERE id = id;
$$ LANGUAGE sql;
SELECT (mostrar_cliente(1)).nombre;
CREATE FUNCTION fn_mostrar_beneficiarios(OUT nombre varchar, OUT curp varchar) RETURNS SETOF record AS $$
SELECT nombre, curp FROM tbl_padrones;
$$ LANGUAGE sql;