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

Buen día.

Gracias a todos por la ayuda, por ahi me dieron la idea y lo puede hacer así

Select x.table_schema, x.table_name
From
(
SELECT table_schema, table_name
FROM information_schema.columns
Where table_schema Not In ('pg_catalog', 'information_schema', 'vfp_a_postgresql', '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_mes
FROM information_schema.columns
Where table_schema Not In ('pg_catalog', 'information_schema', 'vfp_a_postgresql', 'public', 'prueba')
) t
Where t.existe_mes = TRUE
)
GROUP BY table_schema, table_name
) x Inner Join pg_tables pt On x.table_schema = pt.schemaname And x.table_name = pt.tablename
ORDER BY x.table_schema, x.table_name

Saludos.Mauricio

Mauricio. Cuenca-Ecuador

El Miércoles 13 de abril de 2016 8:21, Jorge Gonzalez <jgonzalez(at)itecnologica(dot)com> escribió:

Puedes hacer esta:

select 'ALTER TABLE ' || 'public' || '.' || t.tablename || ' ADD COLUMN tu_columna tipo_dato;'
from
(SELECT tablename FROM pg_tables WHERE schemaname = 'public') t

Con esto generas las sentencias que necesitas para agregar el campo.

Saludos.

2016-04-13 8:36 GMT-04:30 Jorge Gonzalez <jgonzalez(at)itecnologica(dot)com>:

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 34Oficina (Directo): +58 (251) 250 43 99
>Móvil: +584261534450
>Fax: +58 (251) 231 77 64
>

--

Ing. Jorge A. González V.
Especialista de Base de Datos
Gerencia de Tecnología

Oficina (Máster): +58 (251) 232 16 34Oficina (Directo): +58 (251) 250 43 99
Móvil: +584261534450
Fax: +58 (251) 231 77 64

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripcin:
http://www.postgresql.org/mailpref/pgsql-es-ayuda