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

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