Re: [pgsql-es-ayuda] Nuevo campo en todas las tablas de la base de datos

[mauricio pullabuestan]

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 
 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 :

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]
>   

Re: [pgsql-es-ayuda] Nuevo campo en todas las tablas de la base de datos

[Jorge Gonzalez]

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 :

> 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 :
>
>> 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 

Re: [pgsql-es-ayuda] Nuevo campo en todas las tablas de la base de datos

[Jorge Gonzalez]

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 :

> 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',
> 

Re: [MASSMAIL] Re: [pgsql-es-ayuda] Nuevo campo en todas las tablas de la base de datos

[Gilberto Castillo]

> Buenos dias:
>
> On Wed, Apr 13, 2016 at 12:33 AM, mauricio pullabuestan
>  wrote:
>> 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.
>
> Asi por encima tu problema parece ser que seleccionas de donde no
> debes ( o para ser exacto no seleccionas de todos los sitios donde
> debes ):
>
>>  SELECT table_schema, table_name
>>   FROM information_schema.columns
> ...
>>SELECT table_schema, table_name, column_name = 'mi_campo' As
>> existe_campo
>> FROM information_schema.columns
> ...
>
> Si vas a las docs veras ( lo siento, en ingles, no se donde estan las
> docs en español ):
>
> http://www.postgresql.org/docs/9.5/static/infoschema-columns.html

La doc en español esta acá, solo espera por la constribución de nosotros
en la traducción: http://pootle.postgresql.org.ar/pootle


>
> 34.16. columns
>
> The view columns contains information about all table columns (or view
> columns) in the database. System columns (oid, etc.) are not included.
> Only those columns are shown that the current user has access to (by
> way of being the owner or having some privilege).
>
> Prueba a hacer un join con tables para coger solo las tablas:
>
> http://www.postgresql.org/docs/9.5/static/infoschema-tables.html
>
> 34.52. tables
>
> The view tables contains all tables and views defined in the current
> database. Only those tables and views are shown that the current user
> has access to (by way of being the owner or having some privilege).
> 
> table_type - character_data - Type of the table: BASE TABLE for a
> persistent base table (the normal table type), VIEW for a view,
> FOREIGN TABLE for a foreign table, or LOCAL TEMPORARY for a temporary
> table
>
> ( Podrias probar a hacer un not exists o u nouter join filtrado con
> views, pero creo que tables.table_type te dara mejor resultado ya que
> te permite decidir que hacer con las FOREIGN TABLE y/o TEMPORARY TABLE
> ).
>
>
> Fancisco Olarte.
>
> -
> Enviado a la lista de correo pgsql-es-ayuda
> (pgsql-es-ayuda@postgresql.org)
> Para cambiar tu suscripci�n:
> http://www.postgresql.org/mailpref/pgsql-es-ayuda
>


-- 
Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba


-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda@postgresql.org)
Para cambiar tu suscripci�n:
http://www.postgresql.org/mailpref/pgsql-es-ayuda

Re: [pgsql-es-ayuda] Nuevo campo en todas las tablas de la base de datos

[Francisco Olarte]

Buenos dias:

On Wed, Apr 13, 2016 at 12:33 AM, mauricio pullabuestan
 wrote:
> 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.

Asi por encima tu problema parece ser que seleccionas de donde no
debes ( o para ser exacto no seleccionas de todos los sitios donde
debes ):

>  SELECT table_schema, table_name
>   FROM information_schema.columns
...
>SELECT table_schema, table_name, column_name = 'mi_campo' As 
> existe_campo
> FROM information_schema.columns
...

Si vas a las docs veras ( lo siento, en ingles, no se donde estan las
docs en español ):

http://www.postgresql.org/docs/9.5/static/infoschema-columns.html

34.16. columns

The view columns contains information about all table columns (or view
columns) in the database. System columns (oid, etc.) are not included.
Only those columns are shown that the current user has access to (by
way of being the owner or having some privilege).

Prueba a hacer un join con tables para coger solo las tablas:

http://www.postgresql.org/docs/9.5/static/infoschema-tables.html

34.52. tables

The view tables contains all tables and views defined in the current
database. Only those tables and views are shown that the current user
has access to (by way of being the owner or having some privilege).

table_type - character_data - Type of the table: BASE TABLE for a
persistent base table (the normal table type), VIEW for a view,
FOREIGN TABLE for a foreign table, or LOCAL TEMPORARY for a temporary
table

( Podrias probar a hacer un not exists o u nouter join filtrado con
views, pero creo que tables.table_type te dara mejor resultado ya que
te permite decidir que hacer con las FOREIGN TABLE y/o TEMPORARY TABLE
).


Fancisco Olarte.

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda@postgresql.org)
Para cambiar tu suscripci�n:
http://www.postgresql.org/mailpref/pgsql-es-ayuda

[pgsql-es-ayuda] Nuevo campo en todas las tablas de la base de datos

[mauricio pullabuestan]

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@postgresql.org)
Para cambiar tu suscripci�n:
http://www.postgresql.org/mailpref/pgsql-es-ayuda