Re: Nuevo campo en todas las tablas de la base de datos

Buenos días Mauricio porque no pruebas con esta vista??

-- View: v_tablas

-- DROP VIEW v_tablas;

CREATE OR REPLACE VIEW v_tablas AS
SELECT n.nspname AS schmema,
c.relname AS name,
a.attnum AS id,
a.attname AS column_name,
t.typname AS column_type,
CASE
WHEN (a.atttypmod - 4) < 0 THEN 0
ELSE a.atttypmod - 4
END AS type_lenght,
( SELECT COALESCE(( SELECT d.adsrc
FROM pg_attrdef d
WHERE d.adrelid = c.oid AND d.adnum = a.attnum),
''::text) AS "coalesce") AS default_value,
a.attnotnull AS not_null,
CASE
WHEN (( SELECT count(DISTINCT sqa.attname) AS count
FROM pg_class sqc1,
pg_attribute sqa
WHERE sqa.attrelid = sqc1.oid AND sqa.attname = a.attname AND
(sqc1.oid IN ( SELECT sqi.indexrelid
FROM pg_index sqi,
pg_class sqc2,
pg_namespace sqn
WHERE sqc2.relname ~~* c.relname::text AND sqc2.oid =
sqi.indrelid AND sqi.indisunique = true AND sqn.nspname ~~*
"current_schema"()::text)))) > 0 THEN true
ELSE false
END AS "unique",
CASE
WHEN (( SELECT count(DISTINCT sqa.attname) AS count
FROM pg_class sqc1,
pg_attribute sqa
WHERE sqa.attrelid = sqc1.oid AND sqa.attname = a.attname AND
(sqc1.oid IN ( SELECT sqi.indexrelid
FROM pg_index sqi,
pg_class sqc2,
pg_namespace sqn
WHERE sqc2.relname ~~* c.relname::text AND sqc2.oid =
sqi.indrelid AND sqi.indisprimary = true AND sqn.nspname ~~*
"current_schema"()::text)))) > 0 THEN true
ELSE false
END AS primary_key,
( SELECT COALESCE(( SELECT sqd.description
FROM pg_description sqd
WHERE sqd.objoid = a.attrelid AND sqd.objsubid =
a.attnum), ''::text) AS "coalesce") AS description,
( SELECT COALESCE(( SELECT sqcf.relname
FROM pg_attribute sqa
JOIN pg_class sqc ON sqc.oid = sqa.attrelid AND
sqc.relkind = 'r'::"char"
JOIN pg_namespace sqn ON sqn.oid = sqc.relnamespace
JOIN pg_constraint sqct ON sqct.conrelid =
sqa.attrelid AND sqct.confrelid <> 0::oid AND sqct.conkey[1] = sqa.attnum
JOIN pg_class sqcf ON sqcf.oid = sqct.confrelid AND
sqcf.relkind = 'r'::"char"
JOIN pg_namespace sqnf ON sqnf.oid = sqcf.relnamespace
JOIN pg_attribute sqaf ON sqaf.attrelid =
sqct.confrelid AND sqaf.attnum = sqct.confkey[1]
WHERE sqn.nspname ~~* "current_schema"()::text AND
sqc.relname ~~* c.relname::text AND sqa.attname ~~* a.attname::text),
''::name) AS "coalesce") AS foreign_table,
( SELECT COALESCE(( SELECT sqaf.attname
FROM pg_attribute sqa
JOIN pg_class sqc ON sqc.oid = sqa.attrelid AND
sqc.relkind = 'r'::"char"
JOIN pg_namespace sqn ON sqn.oid = sqc.relnamespace
JOIN pg_constraint sqct ON sqct.conrelid =
sqa.attrelid AND sqct.confrelid <> 0::oid AND sqct.conkey[1] = sqa.attnum
JOIN pg_class sqcf ON sqcf.oid = sqct.confrelid AND
sqcf.relkind = 'r'::"char"
JOIN pg_namespace sqnf ON sqnf.oid = sqcf.relnamespace
JOIN pg_attribute sqaf ON sqaf.attrelid =
sqct.confrelid AND sqaf.attnum = sqct.confkey[1]
WHERE sqn.nspname ~~* "current_schema"()::text AND
sqc.relname ~~* c.relname::text AND sqa.attname ~~* a.attname::text),
''::name) AS "coalesce") AS foreign_column
FROM pg_class c,
pg_namespace n,
pg_attribute a,
pg_type t
WHERE c.relkind = 'r'::"char" AND n.oid = c.relnamespace AND a.attrelid =
c.oid AND a.atttypid = t.oid AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY c.relname, a.attnum;

ALTER TABLE v_tablas
OWNER TO postgres;

2016-04-12 18:03 GMT-04:30 mauricio pullabuestan <jmauriciopb(at)yahoo(dot)es>:

> Buen día.
>
> Necesito crear un campo en todas que no tengan un campo en concreto para
> ello tengo 2 funciones, el problema se da es que la funcion
> campo_check_fnc me devuelve también las vista y se produce un error.
>
> Como puedo fitrar que solo me devuelva tablas.
>
> CREATE OR REPLACE FUNCTION public.campo_check_fnc()
> RETURNS void AS
> $BODY$
> DECLARE rs RECORD;
> BEGIN
> FOR rs IN
> (
> SELECT table_schema, table_name
> FROM information_schema.columns
> Where table_schema Not In ('pg_catalog', 'information_schema',
> 'public', 'prueba')
> And table_schema || table_name Not IN
> (Select t.table_schema || t.table_name
> From
> (
> SELECT table_schema, table_name, column_name = 'mi_campo' As
> existe_campo
> FROM information_schema.columns
> Where table_schema Not In ('pg_catalog', 'information_schema',
> 'public', 'prueba')
> ) t
> Where t.existe_campo = TRUE
> )
> GROUP BY table_schema, table_name
> ORDER BY table_schema, table_name ) LOOP
>
> PERFORM * FROM public.campo_crea_fnc(rs.table_schema::VARCHAR,
> rs.table_name::VARCHAR);
> END LOOP;
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
> ALTER FUNCTION public.migracion_campo_check_fnc()
> OWNER TO postgres;
>
>
>
>
> CREATE OR REPLACE FUNCTION public.campo_crea_fnc(
> p_schema character varying,
> p_tabla character varying)
> RETURNS void AS
> $BODY$
> BEGIN
>
> EXECUTE 'ALTER TABLE ' || p_schema || '.' || p_tabla || ' ADD COLUMN
> mi_campo CHAR(2) DEFAULT ''NO''';
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
>
> Saludos.
> Mauricio
>
> -
> Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org
> )
> Para cambiar tu suscripción:
> http://www.postgresql.org/mailpref/pgsql-es-ayuda
>

--
Ing. Jorge A. González V.
Especialista de Base de Datos
Gerencia de Tecnología
Oficina (Máster): +58 (251) 232 16 34
Oficina (Directo): +58 (251) 250 43 99
*Móvil: *+584261534450
Fax: +58 (251) 231 77 64