Faça Você Mesmo: Diagramas de Classe com as Tabelas do Catálogo do PostgreSQL

Automatizar tarefas complexas não é uma tarefa fácil. Dentro da área de banco de dados a elaboração e manutenção da modelagem do banco de dados é uma tarefa das mais difíceis. Os modelos simplesmente não refletem a realidade, ou são incompletos, por limitações das ferramentas ou pela baixa prioridade atribuída à modelagem. Esta postagem mostra as etapas para se fazer a geração automática de diagramas de classe com base no modelo de dados. Foram modelados os esquemas PG_CATALOG e INFORMATION_SCHEMA do postgresql, através do editor de diagramas DIA, que pode ser baixada no sourceforge e funciona tanto no windows quanto no linux.

A escolha da ferramenta de modelagem se deu pelo fato da mesma permitir a geração de diagramas complexos através de scripts em Python. Infelizmente, a documentação encontrada não é das melhores, o que dificulta este tipo de empreitada.

Basicamente, foram seguidas as seguintes etapas para a geração dos diagramas:
1 - Consultas aos metadados do postgresql, relativas aos esquemas desejados, gerando como resultado um script python;
2 - Salvamento deste script em um arquivo texto;
3 - Ajuste manual do script para alterar detalhes como textos apresentados e remoção de espaços;
4 - Execução do script python de dentro da ferramenta DIA, para a criação do diagrama;
5 - Ajuste manual de alguma imperfeição estética no diagrama gerado.

Antes de saber como fazer, veja o resultado obtido com o mínimo de alterações manuais:

Figura 1 - PG_CATALOG

 

Figura 2 - INFORMATION_SCHEMA

1 - Geração e Edição de Script Python - PG_CATALOG

A geração do script python para a criação do diagrama foi feita utilizando-se sql no esquema do banco de dados desejado.

Abaixo coloco a consulta de geração do diagrama do esquema pg_catalog. É mais simples do que parece, sendo composto por uma sequência de várias consultas, unidas por UNION ALL, gerando linhas de texto com o script:


SELECT CAST ('diagram = dia.new("PG_CATALOG.dia")' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('data = diagram.data' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('display = diagram.display()' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('layer = data.active_layer' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('pg_objtype = "UML - Class"' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('oType = dia.get_object_type (pg_objtype)' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('theObjects = [diagram, data, layer, display, oType]' AS TEXT) as OBJETO
UNION ALL
SELECT CAST(relname || ', h1, h2 = oType.create (' || 20*((row_number() OVER (PARTITION BY schemaname)) % 12) || ',' || 30*((row_number() OVER (PARTITION BY schemaname)) % 5) || ')' as TEXT)  as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog'
UNION ALL
SELECT CAST('theObjects.append(' || relname || ')' as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog'
UNION ALL
SELECT CAST(relname || '.properties["stereotype"] = "pg_catalog"' as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog'
UNION ALL
SELECT CAST(relname || '.properties["comment"] = "' || relname || ' Class"'  as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog'
UNION ALL
SELECT CAST(relname || '.properties["name"] = "' || relname || '"'  as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog'
UNION ALL
SELECT CAST('attributes_' || relname || ' = []' as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog'
UNION ALL
SELECT CAST('attributes_' || table_name || '.append((''' || column_name || ''',''' || data_type ||''',''Value_not_set'',''' || column_name || '-' || data_type || ''',1,0,0)) ' AS TEXT) as OBJETO from information_schema.columns where table_schema = 'pg_catalog' AND table_name IN (SELECT relname as table_name FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog')
UNION ALL
SELECT CAST(relname || '.properties["attributes"] = attributes_' || relname as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog'
UNION ALL
SELECT CAST('layer.add_object (' || relname || ')' as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'pg_catalog'
UNION ALL
SELECT CAST('oType = dia.get_object_type ("UML - Activity")' as TEXT) as OBJETO
UNION ALL
SELECT CAST('oend, h1, h2 = oType.create (0,-1)' as TEXT) as OBJETO
UNION ALL
SELECT CAST('theObjects.append(oend)' as TEXT) as OBJETO
UNION ALL
SELECT CAST('oend.properties["text"] = "PG_CATALOG: Claudio Bezerra Leopoldino - Meu Blog de PostgreSQL - http://postgresqlbr.blogspot.com.br/ - claudiob_br@yahoo.com.br"' as TEXT) as OBJETO
UNION ALL
SELECT CAST('layer.add_object (oend)' as TEXT) as OBJETO
UNION ALL
SELECT CAST('diagram.save()' as TEXT) as OBJETO
UNION ALL
SELECT CAST('print "Generation FINISHED"' as TEXT) as OBJETO;
Após a execução deste script SQL, salve o resultado em arquivo texto com  extensão .py e o edite, acrescentando, excluindo e alterando elementos.

2 - Script Python Produzido - PG_CATALOG

Abaixo coloco a listagem do script python produzido e editado:

diagram=dia.new("PG_CATALOG.dia")
data=diagram.data
display=diagram.display()
layer=data.active_layer
pg_objtype="UML - Class"
oType=dia.get_object_type(pg_objtype)
theObjects=[diagram,data,layer,display,oType]
pg_conversion,h1,h2=oType.create(20,30)
pg_foreign_data_wrapper,h1,h2=oType.create(40,60)
pg_collation,h1,h2=oType.create(60,90)
pg_range,h1,h2=oType.create(80,120)
pg_class,h1,h2=oType.create(100,0)
pg_opclass,h1,h2=oType.create(120,30)
pg_opfamily,h1,h2=oType.create(140,60)
pg_trigger,h1,h2=oType.create(160,90)
pg_auth_members,h1,h2=oType.create(180,120)
pg_operator,h1,h2=oType.create(200,0)
pg_attribute,h1,h2=oType.create(220,30)
pg_ts_parser,h1,h2=oType.create(0,60)
pg_default_acl,h1,h2=oType.create(20,90)
pg_depend,h1,h2=oType.create(40,120)
pg_statistic,h1,h2=oType.create(60,0)
pg_attrdef,h1,h2=oType.create(80,30)
pg_language,h1,h2=oType.create(100,60)
pg_tablespace,h1,h2=oType.create(120,90)
pg_db_role_setting,h1,h2=oType.create(140,120)
pg_seclabel,h1,h2=oType.create(160,0)
pg_shdepend,h1,h2=oType.create(180,30)
pg_event_trigger,h1,h2=oType.create(200,60)
pg_shseclabel,h1,h2=oType.create(220,90)
pg_aggregate,h1,h2=oType.create(0,120)
pg_index,h1,h2=oType.create(20,0)
pg_inherits,h1,h2=oType.create(40,30)
pg_rewrite,h1,h2=oType.create(60,60)
pg_ts_dict,h1,h2=oType.create(80,90)
pg_foreign_table,h1,h2=oType.create(100,120)
pg_constraint,h1,h2=oType.create(120,0)
pg_enum,h1,h2=oType.create(140,30)
pg_amproc,h1,h2=oType.create(160,60)
pg_extension,h1,h2=oType.create(180,90)
pg_shdescription,h1,h2=oType.create(200,120)
pg_pltemplate,h1,h2=oType.create(220,0)
pg_ts_config,h1,h2=oType.create(0,30)
pg_amop,h1,h2=oType.create(20,60)
pg_cast,h1,h2=oType.create(40,90)
pg_user_mapping,h1,h2=oType.create(60,120)
pg_type,h1,h2=oType.create(80,0)
pg_largeobject,h1,h2=oType.create(100,30)
pg_am,h1,h2=oType.create(120,60)
pg_largeobject_metadata,h1,h2=oType.create(140,90)
pg_ts_config_map,h1,h2=oType.create(160,120)
pg_ts_template,h1,h2=oType.create(180,0)
pg_database,h1,h2=oType.create(200,30)
pg_namespace,h1,h2=oType.create(220,60)
pg_proc,h1,h2=oType.create(0,90)
pg_foreign_server,h1,h2=oType.create(20,120)
pg_authid,h1,h2=oType.create(40,0)
pg_description,h1,h2=oType.create(60,30)
theObjects.append(pg_conversion)
theObjects.append(pg_foreign_data_wrapper)
theObjects.append(pg_collation)
theObjects.append(pg_range)
theObjects.append(pg_class)
theObjects.append(pg_opclass)
theObjects.append(pg_opfamily)
theObjects.append(pg_trigger)
theObjects.append(pg_auth_members)
theObjects.append(pg_operator)
theObjects.append(pg_attribute)
theObjects.append(pg_ts_parser)
theObjects.append(pg_default_acl)
theObjects.append(pg_depend)
theObjects.append(pg_statistic)
theObjects.append(pg_attrdef)
theObjects.append(pg_language)
theObjects.append(pg_tablespace)
theObjects.append(pg_db_role_setting)
theObjects.append(pg_seclabel)
theObjects.append(pg_shdepend)
theObjects.append(pg_event_trigger)
theObjects.append(pg_shseclabel)
theObjects.append(pg_aggregate)
theObjects.append(pg_index)
theObjects.append(pg_inherits)
theObjects.append(pg_rewrite)
theObjects.append(pg_ts_dict)
theObjects.append(pg_foreign_table)
theObjects.append(pg_constraint)
theObjects.append(pg_enum)
theObjects.append(pg_amproc)
theObjects.append(pg_extension)
theObjects.append(pg_shdescription)
theObjects.append(pg_pltemplate)
theObjects.append(pg_ts_config)
theObjects.append(pg_amop)
theObjects.append(pg_cast)
theObjects.append(pg_user_mapping)
theObjects.append(pg_type)
theObjects.append(pg_largeobject)
theObjects.append(pg_am)
theObjects.append(pg_largeobject_metadata)
theObjects.append(pg_ts_config_map)
theObjects.append(pg_ts_template)
theObjects.append(pg_database)
theObjects.append(pg_namespace)
theObjects.append(pg_proc)
theObjects.append(pg_foreign_server)
theObjects.append(pg_authid)
theObjects.append(pg_description)
pg_conversion.properties["stereotype"]="pg_catalog"
pg_foreign_data_wrapper.properties["stereotype"]="pg_catalog"
pg_collation.properties["stereotype"]="pg_catalog"
pg_range.properties["stereotype"]="pg_catalog"
pg_class.properties["stereotype"]="pg_catalog"
pg_opclass.properties["stereotype"]="pg_catalog"
pg_opfamily.properties["stereotype"]="pg_catalog"
pg_trigger.properties["stereotype"]="pg_catalog"
pg_auth_members.properties["stereotype"]="pg_catalog"
pg_operator.properties["stereotype"]="pg_catalog"
pg_attribute.properties["stereotype"]="pg_catalog"
pg_ts_parser.properties["stereotype"]="pg_catalog"
pg_default_acl.properties["stereotype"]="pg_catalog"
pg_depend.properties["stereotype"]="pg_catalog"
pg_statistic.properties["stereotype"]="pg_catalog"
pg_attrdef.properties["stereotype"]="pg_catalog"
pg_language.properties["stereotype"]="pg_catalog"
pg_tablespace.properties["stereotype"]="pg_catalog"
pg_db_role_setting.properties["stereotype"]="pg_catalog"
pg_seclabel.properties["stereotype"]="pg_catalog"
pg_shdepend.properties["stereotype"]="pg_catalog"
pg_event_trigger.properties["stereotype"]="pg_catalog"
pg_shseclabel.properties["stereotype"]="pg_catalog"
pg_aggregate.properties["stereotype"]="pg_catalog"
pg_index.properties["stereotype"]="pg_catalog"
pg_inherits.properties["stereotype"]="pg_catalog"
pg_rewrite.properties["stereotype"]="pg_catalog"
pg_ts_dict.properties["stereotype"]="pg_catalog"
pg_foreign_table.properties["stereotype"]="pg_catalog"
pg_constraint.properties["stereotype"]="pg_catalog"
pg_enum.properties["stereotype"]="pg_catalog"
pg_amproc.properties["stereotype"]="pg_catalog"
pg_extension.properties["stereotype"]="pg_catalog"
pg_shdescription.properties["stereotype"]="pg_catalog"
pg_pltemplate.properties["stereotype"]="pg_catalog"
pg_ts_config.properties["stereotype"]="pg_catalog"
pg_amop.properties["stereotype"]="pg_catalog"
pg_cast.properties["stereotype"]="pg_catalog"
pg_user_mapping.properties["stereotype"]="pg_catalog"
pg_type.properties["stereotype"]="pg_catalog"
pg_largeobject.properties["stereotype"]="pg_catalog"
pg_am.properties["stereotype"]="pg_catalog"
pg_largeobject_metadata.properties["stereotype"]="pg_catalog"
pg_ts_config_map.properties["stereotype"]="pg_catalog"
pg_ts_template.properties["stereotype"]="pg_catalog"
pg_database.properties["stereotype"]="pg_catalog"
pg_namespace.properties["stereotype"]="pg_catalog"
pg_proc.properties["stereotype"]="pg_catalog"
pg_foreign_server.properties["stereotype"]="pg_catalog"
pg_authid.properties["stereotype"]="pg_catalog"
pg_description.properties["stereotype"]="pg_catalog"
pg_conversion.properties["comment"]="pg_conversionClass"
pg_foreign_data_wrapper.properties["comment"]="pg_foreign_data_wrapperClass"
pg_collation.properties["comment"]="pg_collationClass"
pg_range.properties["comment"]="pg_rangeClass"
pg_class.properties["comment"]="pg_classClass"
pg_opclass.properties["comment"]="pg_opclassClass"
pg_opfamily.properties["comment"]="pg_opfamilyClass"
pg_trigger.properties["comment"]="pg_triggerClass"
pg_auth_members.properties["comment"]="pg_auth_membersClass"
pg_operator.properties["comment"]="pg_operatorClass"
pg_attribute.properties["comment"]="pg_attributeClass"
pg_ts_parser.properties["comment"]="pg_ts_parserClass"
pg_default_acl.properties["comment"]="pg_default_aclClass"
pg_depend.properties["comment"]="pg_dependClass"
pg_statistic.properties["comment"]="pg_statisticClass"
pg_attrdef.properties["comment"]="pg_attrdefClass"
pg_language.properties["comment"]="pg_languageClass"
pg_tablespace.properties["comment"]="pg_tablespaceClass"
pg_db_role_setting.properties["comment"]="pg_db_role_settingClass"
pg_seclabel.properties["comment"]="pg_seclabelClass"
pg_shdepend.properties["comment"]="pg_shdependClass"
pg_event_trigger.properties["comment"]="pg_event_triggerClass"
pg_shseclabel.properties["comment"]="pg_shseclabelClass"
pg_aggregate.properties["comment"]="pg_aggregateClass"
pg_index.properties["comment"]="pg_indexClass"
pg_inherits.properties["comment"]="pg_inheritsClass"
pg_rewrite.properties["comment"]="pg_rewriteClass"
pg_ts_dict.properties["comment"]="pg_ts_dictClass"
pg_foreign_table.properties["comment"]="pg_foreign_tableClass"
pg_constraint.properties["comment"]="pg_constraintClass"
pg_enum.properties["comment"]="pg_enumClass"
pg_amproc.properties["comment"]="pg_amprocClass"
pg_extension.properties["comment"]="pg_extensionClass"
pg_shdescription.properties["comment"]="pg_shdescriptionClass"
pg_pltemplate.properties["comment"]="pg_pltemplateClass"
pg_ts_config.properties["comment"]="pg_ts_configClass"
pg_amop.properties["comment"]="pg_amopClass"
pg_cast.properties["comment"]="pg_castClass"
pg_user_mapping.properties["comment"]="pg_user_mappingClass"
pg_type.properties["comment"]="pg_typeClass"
pg_largeobject.properties["comment"]="pg_largeobjectClass"
pg_am.properties["comment"]="pg_amClass"
pg_largeobject_metadata.properties["comment"]="pg_largeobject_metadataClass"
pg_ts_config_map.properties["comment"]="pg_ts_config_mapClass"
pg_ts_template.properties["comment"]="pg_ts_templateClass"
pg_database.properties["comment"]="pg_databaseClass"
pg_namespace.properties["comment"]="pg_namespaceClass"
pg_proc.properties["comment"]="pg_procClass"
pg_foreign_server.properties["comment"]="pg_foreign_serverClass"
pg_authid.properties["comment"]="pg_authidClass"
pg_description.properties["comment"]="pg_descriptionClass"
pg_conversion.properties["name"]="pg_conversion"
pg_foreign_data_wrapper.properties["name"]="pg_foreign_data_wrapper"
pg_collation.properties["name"]="pg_collation"
pg_range.properties["name"]="pg_range"
pg_class.properties["name"]="pg_class"
pg_opclass.properties["name"]="pg_opclass"
pg_opfamily.properties["name"]="pg_opfamily"
pg_trigger.properties["name"]="pg_trigger"
pg_auth_members.properties["name"]="pg_auth_members"
pg_operator.properties["name"]="pg_operator"
pg_attribute.properties["name"]="pg_attribute"
pg_ts_parser.properties["name"]="pg_ts_parser"
pg_default_acl.properties["name"]="pg_default_acl"
pg_depend.properties["name"]="pg_depend"
pg_statistic.properties["name"]="pg_statistic"
pg_attrdef.properties["name"]="pg_attrdef"
pg_language.properties["name"]="pg_language"
pg_tablespace.properties["name"]="pg_tablespace"
pg_db_role_setting.properties["name"]="pg_db_role_setting"
pg_seclabel.properties["name"]="pg_seclabel"
pg_shdepend.properties["name"]="pg_shdepend"
pg_event_trigger.properties["name"]="pg_event_trigger"
pg_shseclabel.properties["name"]="pg_shseclabel"
pg_aggregate.properties["name"]="pg_aggregate"
pg_index.properties["name"]="pg_index"
pg_inherits.properties["name"]="pg_inherits"
pg_rewrite.properties["name"]="pg_rewrite"
pg_ts_dict.properties["name"]="pg_ts_dict"
pg_foreign_table.properties["name"]="pg_foreign_table"
pg_constraint.properties["name"]="pg_constraint"
pg_enum.properties["name"]="pg_enum"
pg_amproc.properties["name"]="pg_amproc"
pg_extension.properties["name"]="pg_extension"
pg_shdescription.properties["name"]="pg_shdescription"
pg_pltemplate.properties["name"]="pg_pltemplate"
pg_ts_config.properties["name"]="pg_ts_config"
pg_amop.properties["name"]="pg_amop"
pg_cast.properties["name"]="pg_cast"
pg_user_mapping.properties["name"]="pg_user_mapping"
pg_type.properties["name"]="pg_type"
pg_largeobject.properties["name"]="pg_largeobject"
pg_am.properties["name"]="pg_am"
pg_largeobject_metadata.properties["name"]="pg_largeobject_metadata"
pg_ts_config_map.properties["name"]="pg_ts_config_map"
pg_ts_template.properties["name"]="pg_ts_template"
pg_database.properties["name"]="pg_database"
pg_namespace.properties["name"]="pg_namespace"
pg_proc.properties["name"]="pg_proc"
pg_foreign_server.properties["name"]="pg_foreign_server"
pg_authid.properties["name"]="pg_authid"
pg_description.properties["name"]="pg_description"
attributes_pg_conversion=[]
attributes_pg_foreign_data_wrapper=[]
attributes_pg_collation=[]
attributes_pg_range=[]
attributes_pg_class=[]
attributes_pg_opclass=[]
attributes_pg_opfamily=[]
attributes_pg_trigger=[]
attributes_pg_auth_members=[]
attributes_pg_operator=[]
attributes_pg_attribute=[]
attributes_pg_ts_parser=[]
attributes_pg_default_acl=[]
attributes_pg_depend=[]
attributes_pg_statistic=[]
attributes_pg_attrdef=[]
attributes_pg_language=[]
attributes_pg_tablespace=[]
attributes_pg_db_role_setting=[]
attributes_pg_seclabel=[]
attributes_pg_shdepend=[]
attributes_pg_event_trigger=[]
attributes_pg_shseclabel=[]
attributes_pg_aggregate=[]
attributes_pg_index=[]
attributes_pg_inherits=[]
attributes_pg_rewrite=[]
attributes_pg_ts_dict=[]
attributes_pg_foreign_table=[]
attributes_pg_constraint=[]
attributes_pg_enum=[]
attributes_pg_amproc=[]
attributes_pg_extension=[]
attributes_pg_shdescription=[]
attributes_pg_pltemplate=[]
attributes_pg_ts_config=[]
attributes_pg_amop=[]
attributes_pg_cast=[]
attributes_pg_user_mapping=[]
attributes_pg_type=[]
attributes_pg_largeobject=[]
attributes_pg_am=[]
attributes_pg_largeobject_metadata=[]
attributes_pg_ts_config_map=[]
attributes_pg_ts_template=[]
attributes_pg_database=[]
attributes_pg_namespace=[]
attributes_pg_proc=[]
attributes_pg_foreign_server=[]
attributes_pg_authid=[]
attributes_pg_description=[]
attributes_pg_statistic.append(('starelid','oid','Value_not_set','starelid-oid',1,0,0))
attributes_pg_statistic.append(('staattnum','smallint','Value_not_set','staattnum-smallint',1,0,0))
attributes_pg_statistic.append(('stainherit','boolean','Value_not_set','stainherit-boolean',1,0,0))
attributes_pg_statistic.append(('stanullfrac','real','Value_not_set','stanullfrac-real',1,0,0))
attributes_pg_statistic.append(('stawidth','integer','Value_not_set','stawidth-integer',1,0,0))
attributes_pg_statistic.append(('stadistinct','real','Value_not_set','stadistinct-real',1,0,0))
attributes_pg_statistic.append(('stakind1','smallint','Value_not_set','stakind1-smallint',1,0,0))
attributes_pg_statistic.append(('stakind2','smallint','Value_not_set','stakind2-smallint',1,0,0))
attributes_pg_statistic.append(('stakind3','smallint','Value_not_set','stakind3-smallint',1,0,0))
attributes_pg_statistic.append(('stakind4','smallint','Value_not_set','stakind4-smallint',1,0,0))
attributes_pg_statistic.append(('stakind5','smallint','Value_not_set','stakind5-smallint',1,0,0))
attributes_pg_statistic.append(('staop1','oid','Value_not_set','staop1-oid',1,0,0))
attributes_pg_statistic.append(('staop2','oid','Value_not_set','staop2-oid',1,0,0))
attributes_pg_statistic.append(('staop3','oid','Value_not_set','staop3-oid',1,0,0))
attributes_pg_statistic.append(('staop4','oid','Value_not_set','staop4-oid',1,0,0))
attributes_pg_statistic.append(('staop5','oid','Value_not_set','staop5-oid',1,0,0))
attributes_pg_statistic.append(('stanumbers1','ARRAY','Value_not_set','stanumbers1-ARRAY',1,0,0))
attributes_pg_statistic.append(('stanumbers2','ARRAY','Value_not_set','stanumbers2-ARRAY',1,0,0))
attributes_pg_statistic.append(('stanumbers3','ARRAY','Value_not_set','stanumbers3-ARRAY',1,0,0))
attributes_pg_statistic.append(('stanumbers4','ARRAY','Value_not_set','stanumbers4-ARRAY',1,0,0))
attributes_pg_statistic.append(('stanumbers5','ARRAY','Value_not_set','stanumbers5-ARRAY',1,0,0))
attributes_pg_statistic.append(('stavalues1','anyarray','Value_not_set','stavalues1-anyarray',1,0,0))
attributes_pg_statistic.append(('stavalues2','anyarray','Value_not_set','stavalues2-anyarray',1,0,0))
attributes_pg_statistic.append(('stavalues3','anyarray','Value_not_set','stavalues3-anyarray',1,0,0))
attributes_pg_statistic.append(('stavalues4','anyarray','Value_not_set','stavalues4-anyarray',1,0,0))
attributes_pg_statistic.append(('stavalues5','anyarray','Value_not_set','stavalues5-anyarray',1,0,0))
attributes_pg_type.append(('typname','name','Value_not_set','typname-name',1,0,0))
attributes_pg_type.append(('typnamespace','oid','Value_not_set','typnamespace-oid',1,0,0))
attributes_pg_type.append(('typowner','oid','Value_not_set','typowner-oid',1,0,0))
attributes_pg_type.append(('typlen','smallint','Value_not_set','typlen-smallint',1,0,0))
attributes_pg_type.append(('typbyval','boolean','Value_not_set','typbyval-boolean',1,0,0))
attributes_pg_type.append(('typtype','"char"','Value_not_set','typtype-"char"',1,0,0))
attributes_pg_type.append(('typcategory','"char"','Value_not_set','typcategory-"char"',1,0,0))
attributes_pg_type.append(('typispreferred','boolean','Value_not_set','typispreferred-boolean',1,0,0))
attributes_pg_type.append(('typisdefined','boolean','Value_not_set','typisdefined-boolean',1,0,0))
attributes_pg_type.append(('typdelim','"char"','Value_not_set','typdelim-"char"',1,0,0))
attributes_pg_type.append(('typrelid','oid','Value_not_set','typrelid-oid',1,0,0))
attributes_pg_type.append(('typelem','oid','Value_not_set','typelem-oid',1,0,0))
attributes_pg_type.append(('typarray','oid','Value_not_set','typarray-oid',1,0,0))
attributes_pg_type.append(('typinput','regproc','Value_not_set','typinput-regproc',1,0,0))
attributes_pg_type.append(('typoutput','regproc','Value_not_set','typoutput-regproc',1,0,0))
attributes_pg_type.append(('typreceive','regproc','Value_not_set','typreceive-regproc',1,0,0))
attributes_pg_type.append(('typsend','regproc','Value_not_set','typsend-regproc',1,0,0))
attributes_pg_type.append(('typmodin','regproc','Value_not_set','typmodin-regproc',1,0,0))
attributes_pg_type.append(('typmodout','regproc','Value_not_set','typmodout-regproc',1,0,0))
attributes_pg_type.append(('typanalyze','regproc','Value_not_set','typanalyze-regproc',1,0,0))
attributes_pg_type.append(('typalign','"char"','Value_not_set','typalign-"char"',1,0,0))
attributes_pg_type.append(('typstorage','"char"','Value_not_set','typstorage-"char"',1,0,0))
attributes_pg_type.append(('typnotnull','boolean','Value_not_set','typnotnull-boolean',1,0,0))
attributes_pg_type.append(('typbasetype','oid','Value_not_set','typbasetype-oid',1,0,0))
attributes_pg_type.append(('typtypmod','integer','Value_not_set','typtypmod-integer',1,0,0))
attributes_pg_type.append(('typndims','integer','Value_not_set','typndims-integer',1,0,0))
attributes_pg_type.append(('typcollation','oid','Value_not_set','typcollation-oid',1,0,0))
attributes_pg_type.append(('typdefaultbin','pg_node_tree','Value_not_set','typdefaultbin-pg_node_tree',1,0,0))
attributes_pg_type.append(('typdefault','text','Value_not_set','typdefault-text',1,0,0))
attributes_pg_type.append(('typacl','ARRAY','Value_not_set','typacl-ARRAY',1,0,0))
attributes_pg_authid.append(('rolname','name','Value_not_set','rolname-name',1,0,0))
attributes_pg_authid.append(('rolsuper','boolean','Value_not_set','rolsuper-boolean',1,0,0))
attributes_pg_authid.append(('rolinherit','boolean','Value_not_set','rolinherit-boolean',1,0,0))
attributes_pg_authid.append(('rolcreaterole','boolean','Value_not_set','rolcreaterole-boolean',1,0,0))
attributes_pg_authid.append(('rolcreatedb','boolean','Value_not_set','rolcreatedb-boolean',1,0,0))
attributes_pg_authid.append(('rolcatupdate','boolean','Value_not_set','rolcatupdate-boolean',1,0,0))
attributes_pg_authid.append(('rolcanlogin','boolean','Value_not_set','rolcanlogin-boolean',1,0,0))
attributes_pg_authid.append(('rolreplication','boolean','Value_not_set','rolreplication-boolean',1,0,0))
attributes_pg_authid.append(('rolconnlimit','integer','Value_not_set','rolconnlimit-integer',1,0,0))
attributes_pg_authid.append(('rolpassword','text','Value_not_set','rolpassword-text',1,0,0))
attributes_pg_authid.append(('rolvaliduntil','timestampwithtimezone','Value_not_set','rolvaliduntil-timestampwithtimezone',1,0,0))
attributes_pg_proc.append(('proname','name','Value_not_set','proname-name',1,0,0))
attributes_pg_proc.append(('pronamespace','oid','Value_not_set','pronamespace-oid',1,0,0))
attributes_pg_proc.append(('proowner','oid','Value_not_set','proowner-oid',1,0,0))
attributes_pg_proc.append(('prolang','oid','Value_not_set','prolang-oid',1,0,0))
attributes_pg_proc.append(('procost','real','Value_not_set','procost-real',1,0,0))
attributes_pg_proc.append(('prorows','real','Value_not_set','prorows-real',1,0,0))
attributes_pg_proc.append(('provariadic','oid','Value_not_set','provariadic-oid',1,0,0))
attributes_pg_proc.append(('protransform','regproc','Value_not_set','protransform-regproc',1,0,0))
attributes_pg_proc.append(('proisagg','boolean','Value_not_set','proisagg-boolean',1,0,0))
attributes_pg_proc.append(('proiswindow','boolean','Value_not_set','proiswindow-boolean',1,0,0))
attributes_pg_proc.append(('prosecdef','boolean','Value_not_set','prosecdef-boolean',1,0,0))
attributes_pg_proc.append(('proleakproof','boolean','Value_not_set','proleakproof-boolean',1,0,0))
attributes_pg_proc.append(('proisstrict','boolean','Value_not_set','proisstrict-boolean',1,0,0))
attributes_pg_proc.append(('proretset','boolean','Value_not_set','proretset-boolean',1,0,0))
attributes_pg_proc.append(('provolatile','"char"','Value_not_set','provolatile-"char"',1,0,0))
attributes_pg_proc.append(('pronargs','smallint','Value_not_set','pronargs-smallint',1,0,0))
attributes_pg_proc.append(('pronargdefaults','smallint','Value_not_set','pronargdefaults-smallint',1,0,0))
attributes_pg_proc.append(('prorettype','oid','Value_not_set','prorettype-oid',1,0,0))
attributes_pg_proc.append(('proargtypes','ARRAY','Value_not_set','proargtypes-ARRAY',1,0,0))
attributes_pg_proc.append(('proallargtypes','ARRAY','Value_not_set','proallargtypes-ARRAY',1,0,0))
attributes_pg_proc.append(('proargmodes','ARRAY','Value_not_set','proargmodes-ARRAY',1,0,0))
attributes_pg_proc.append(('proargnames','ARRAY','Value_not_set','proargnames-ARRAY',1,0,0))
attributes_pg_proc.append(('proargdefaults','pg_node_tree','Value_not_set','proargdefaults-pg_node_tree',1,0,0))
attributes_pg_proc.append(('prosrc','text','Value_not_set','prosrc-text',1,0,0))
attributes_pg_proc.append(('probin','text','Value_not_set','probin-text',1,0,0))
attributes_pg_proc.append(('proconfig','ARRAY','Value_not_set','proconfig-ARRAY',1,0,0))
attributes_pg_proc.append(('proacl','ARRAY','Value_not_set','proacl-ARRAY',1,0,0))
attributes_pg_class.append(('relname','name','Value_not_set','relname-name',1,0,0))
attributes_pg_class.append(('relnamespace','oid','Value_not_set','relnamespace-oid',1,0,0))
attributes_pg_class.append(('reltype','oid','Value_not_set','reltype-oid',1,0,0))
attributes_pg_class.append(('reloftype','oid','Value_not_set','reloftype-oid',1,0,0))
attributes_pg_class.append(('relowner','oid','Value_not_set','relowner-oid',1,0,0))
attributes_pg_class.append(('relam','oid','Value_not_set','relam-oid',1,0,0))
attributes_pg_class.append(('relfilenode','oid','Value_not_set','relfilenode-oid',1,0,0))
attributes_pg_class.append(('reltablespace','oid','Value_not_set','reltablespace-oid',1,0,0))
attributes_pg_class.append(('relpages','integer','Value_not_set','relpages-integer',1,0,0))
attributes_pg_class.append(('reltuples','real','Value_not_set','reltuples-real',1,0,0))
attributes_pg_class.append(('relallvisible','integer','Value_not_set','relallvisible-integer',1,0,0))
attributes_pg_class.append(('reltoastrelid','oid','Value_not_set','reltoastrelid-oid',1,0,0))
attributes_pg_class.append(('reltoastidxid','oid','Value_not_set','reltoastidxid-oid',1,0,0))
attributes_pg_class.append(('relhasindex','boolean','Value_not_set','relhasindex-boolean',1,0,0))
attributes_pg_class.append(('relisshared','boolean','Value_not_set','relisshared-boolean',1,0,0))
attributes_pg_class.append(('relpersistence','"char"','Value_not_set','relpersistence-"char"',1,0,0))
attributes_pg_class.append(('relkind','"char"','Value_not_set','relkind-"char"',1,0,0))
attributes_pg_class.append(('relnatts','smallint','Value_not_set','relnatts-smallint',1,0,0))
attributes_pg_class.append(('relchecks','smallint','Value_not_set','relchecks-smallint',1,0,0))
attributes_pg_class.append(('relhasoids','boolean','Value_not_set','relhasoids-boolean',1,0,0))
attributes_pg_class.append(('relhaspkey','boolean','Value_not_set','relhaspkey-boolean',1,0,0))
attributes_pg_class.append(('relhasrules','boolean','Value_not_set','relhasrules-boolean',1,0,0))
attributes_pg_class.append(('relhastriggers','boolean','Value_not_set','relhastriggers-boolean',1,0,0))
attributes_pg_class.append(('relhassubclass','boolean','Value_not_set','relhassubclass-boolean',1,0,0))
attributes_pg_class.append(('relispopulated','boolean','Value_not_set','relispopulated-boolean',1,0,0))
attributes_pg_class.append(('relfrozenxid','xid','Value_not_set','relfrozenxid-xid',1,0,0))
attributes_pg_class.append(('relminmxid','xid','Value_not_set','relminmxid-xid',1,0,0))
attributes_pg_class.append(('relacl','ARRAY','Value_not_set','relacl-ARRAY',1,0,0))
attributes_pg_class.append(('reloptions','ARRAY','Value_not_set','reloptions-ARRAY',1,0,0))
attributes_pg_user_mapping.append(('umuser','oid','Value_not_set','umuser-oid',1,0,0))
attributes_pg_user_mapping.append(('umserver','oid','Value_not_set','umserver-oid',1,0,0))
attributes_pg_user_mapping.append(('umoptions','ARRAY','Value_not_set','umoptions-ARRAY',1,0,0))
attributes_pg_attribute.append(('attrelid','oid','Value_not_set','attrelid-oid',1,0,0))
attributes_pg_attribute.append(('attname','name','Value_not_set','attname-name',1,0,0))
attributes_pg_attribute.append(('atttypid','oid','Value_not_set','atttypid-oid',1,0,0))
attributes_pg_attribute.append(('attstattarget','integer','Value_not_set','attstattarget-integer',1,0,0))
attributes_pg_attribute.append(('attlen','smallint','Value_not_set','attlen-smallint',1,0,0))
attributes_pg_attribute.append(('attnum','smallint','Value_not_set','attnum-smallint',1,0,0))
attributes_pg_attribute.append(('attndims','integer','Value_not_set','attndims-integer',1,0,0))
attributes_pg_attribute.append(('attcacheoff','integer','Value_not_set','attcacheoff-integer',1,0,0))
attributes_pg_attribute.append(('atttypmod','integer','Value_not_set','atttypmod-integer',1,0,0))
attributes_pg_attribute.append(('attbyval','boolean','Value_not_set','attbyval-boolean',1,0,0))
attributes_pg_attribute.append(('attstorage','"char"','Value_not_set','attstorage-"char"',1,0,0))
attributes_pg_attribute.append(('attalign','"char"','Value_not_set','attalign-"char"',1,0,0))
attributes_pg_attribute.append(('attnotnull','boolean','Value_not_set','attnotnull-boolean',1,0,0))
attributes_pg_attribute.append(('atthasdef','boolean','Value_not_set','atthasdef-boolean',1,0,0))
attributes_pg_attribute.append(('attisdropped','boolean','Value_not_set','attisdropped-boolean',1,0,0))
attributes_pg_attribute.append(('attislocal','boolean','Value_not_set','attislocal-boolean',1,0,0))
attributes_pg_attribute.append(('attinhcount','integer','Value_not_set','attinhcount-integer',1,0,0))
attributes_pg_attribute.append(('attcollation','oid','Value_not_set','attcollation-oid',1,0,0))
attributes_pg_attribute.append(('attacl','ARRAY','Value_not_set','attacl-ARRAY',1,0,0))
attributes_pg_attribute.append(('attoptions','ARRAY','Value_not_set','attoptions-ARRAY',1,0,0))
attributes_pg_attribute.append(('attfdwoptions','ARRAY','Value_not_set','attfdwoptions-ARRAY',1,0,0))
attributes_pg_constraint.append(('conname','name','Value_not_set','conname-name',1,0,0))
attributes_pg_constraint.append(('connamespace','oid','Value_not_set','connamespace-oid',1,0,0))
attributes_pg_constraint.append(('contype','"char"','Value_not_set','contype-"char"',1,0,0))
attributes_pg_constraint.append(('condeferrable','boolean','Value_not_set','condeferrable-boolean',1,0,0))
attributes_pg_constraint.append(('condeferred','boolean','Value_not_set','condeferred-boolean',1,0,0))
attributes_pg_constraint.append(('convalidated','boolean','Value_not_set','convalidated-boolean',1,0,0))
attributes_pg_constraint.append(('conrelid','oid','Value_not_set','conrelid-oid',1,0,0))
attributes_pg_constraint.append(('contypid','oid','Value_not_set','contypid-oid',1,0,0))
attributes_pg_constraint.append(('conindid','oid','Value_not_set','conindid-oid',1,0,0))
attributes_pg_constraint.append(('confrelid','oid','Value_not_set','confrelid-oid',1,0,0))
attributes_pg_constraint.append(('confupdtype','"char"','Value_not_set','confupdtype-"char"',1,0,0))
attributes_pg_constraint.append(('confdeltype','"char"','Value_not_set','confdeltype-"char"',1,0,0))
attributes_pg_constraint.append(('confmatchtype','"char"','Value_not_set','confmatchtype-"char"',1,0,0))
attributes_pg_constraint.append(('conislocal','boolean','Value_not_set','conislocal-boolean',1,0,0))
attributes_pg_constraint.append(('coninhcount','integer','Value_not_set','coninhcount-integer',1,0,0))
attributes_pg_constraint.append(('connoinherit','boolean','Value_not_set','connoinherit-boolean',1,0,0))
attributes_pg_constraint.append(('conkey','ARRAY','Value_not_set','conkey-ARRAY',1,0,0))
attributes_pg_constraint.append(('confkey','ARRAY','Value_not_set','confkey-ARRAY',1,0,0))
attributes_pg_constraint.append(('conpfeqop','ARRAY','Value_not_set','conpfeqop-ARRAY',1,0,0))
attributes_pg_constraint.append(('conppeqop','ARRAY','Value_not_set','conppeqop-ARRAY',1,0,0))
attributes_pg_constraint.append(('conffeqop','ARRAY','Value_not_set','conffeqop-ARRAY',1,0,0))
attributes_pg_constraint.append(('conexclop','ARRAY','Value_not_set','conexclop-ARRAY',1,0,0))
attributes_pg_constraint.append(('conbin','pg_node_tree','Value_not_set','conbin-pg_node_tree',1,0,0))
attributes_pg_constraint.append(('consrc','text','Value_not_set','consrc-text',1,0,0))
attributes_pg_inherits.append(('inhrelid','oid','Value_not_set','inhrelid-oid',1,0,0))
attributes_pg_inherits.append(('inhparent','oid','Value_not_set','inhparent-oid',1,0,0))
attributes_pg_inherits.append(('inhseqno','integer','Value_not_set','inhseqno-integer',1,0,0))
attributes_pg_index.append(('indexrelid','oid','Value_not_set','indexrelid-oid',1,0,0))
attributes_pg_index.append(('indrelid','oid','Value_not_set','indrelid-oid',1,0,0))
attributes_pg_index.append(('indnatts','smallint','Value_not_set','indnatts-smallint',1,0,0))
attributes_pg_index.append(('indisunique','boolean','Value_not_set','indisunique-boolean',1,0,0))
attributes_pg_index.append(('indisprimary','boolean','Value_not_set','indisprimary-boolean',1,0,0))
attributes_pg_index.append(('indisexclusion','boolean','Value_not_set','indisexclusion-boolean',1,0,0))
attributes_pg_index.append(('indimmediate','boolean','Value_not_set','indimmediate-boolean',1,0,0))
attributes_pg_index.append(('indisclustered','boolean','Value_not_set','indisclustered-boolean',1,0,0))
attributes_pg_index.append(('indisvalid','boolean','Value_not_set','indisvalid-boolean',1,0,0))
attributes_pg_index.append(('indcheckxmin','boolean','Value_not_set','indcheckxmin-boolean',1,0,0))
attributes_pg_index.append(('indisready','boolean','Value_not_set','indisready-boolean',1,0,0))
attributes_pg_index.append(('indislive','boolean','Value_not_set','indislive-boolean',1,0,0))
attributes_pg_index.append(('indkey','ARRAY','Value_not_set','indkey-ARRAY',1,0,0))
attributes_pg_index.append(('indcollation','ARRAY','Value_not_set','indcollation-ARRAY',1,0,0))
attributes_pg_index.append(('indclass','ARRAY','Value_not_set','indclass-ARRAY',1,0,0))
attributes_pg_index.append(('indoption','ARRAY','Value_not_set','indoption-ARRAY',1,0,0))
attributes_pg_index.append(('indexprs','pg_node_tree','Value_not_set','indexprs-pg_node_tree',1,0,0))
attributes_pg_index.append(('indpred','pg_node_tree','Value_not_set','indpred-pg_node_tree',1,0,0))
attributes_pg_operator.append(('oprname','name','Value_not_set','oprname-name',1,0,0))
attributes_pg_operator.append(('oprnamespace','oid','Value_not_set','oprnamespace-oid',1,0,0))
attributes_pg_operator.append(('oprowner','oid','Value_not_set','oprowner-oid',1,0,0))
attributes_pg_operator.append(('oprkind','"char"','Value_not_set','oprkind-"char"',1,0,0))
attributes_pg_operator.append(('oprcanmerge','boolean','Value_not_set','oprcanmerge-boolean',1,0,0))
attributes_pg_operator.append(('oprcanhash','boolean','Value_not_set','oprcanhash-boolean',1,0,0))
attributes_pg_operator.append(('oprleft','oid','Value_not_set','oprleft-oid',1,0,0))
attributes_pg_operator.append(('oprright','oid','Value_not_set','oprright-oid',1,0,0))
attributes_pg_operator.append(('oprresult','oid','Value_not_set','oprresult-oid',1,0,0))
attributes_pg_operator.append(('oprcom','oid','Value_not_set','oprcom-oid',1,0,0))
attributes_pg_operator.append(('oprnegate','oid','Value_not_set','oprnegate-oid',1,0,0))
attributes_pg_operator.append(('oprcode','regproc','Value_not_set','oprcode-regproc',1,0,0))
attributes_pg_operator.append(('oprrest','regproc','Value_not_set','oprrest-regproc',1,0,0))
attributes_pg_operator.append(('oprjoin','regproc','Value_not_set','oprjoin-regproc',1,0,0))
attributes_pg_opfamily.append(('opfmethod','oid','Value_not_set','opfmethod-oid',1,0,0))
attributes_pg_opfamily.append(('opfname','name','Value_not_set','opfname-name',1,0,0))
attributes_pg_opfamily.append(('opfnamespace','oid','Value_not_set','opfnamespace-oid',1,0,0))
attributes_pg_opfamily.append(('opfowner','oid','Value_not_set','opfowner-oid',1,0,0))
attributes_pg_opclass.append(('opcmethod','oid','Value_not_set','opcmethod-oid',1,0,0))
attributes_pg_opclass.append(('opcname','name','Value_not_set','opcname-name',1,0,0))
attributes_pg_opclass.append(('opcnamespace','oid','Value_not_set','opcnamespace-oid',1,0,0))
attributes_pg_opclass.append(('opcowner','oid','Value_not_set','opcowner-oid',1,0,0))
attributes_pg_opclass.append(('opcfamily','oid','Value_not_set','opcfamily-oid',1,0,0))
attributes_pg_opclass.append(('opcintype','oid','Value_not_set','opcintype-oid',1,0,0))
attributes_pg_opclass.append(('opcdefault','boolean','Value_not_set','opcdefault-boolean',1,0,0))
attributes_pg_opclass.append(('opckeytype','oid','Value_not_set','opckeytype-oid',1,0,0))
attributes_pg_am.append(('amname','name','Value_not_set','amname-name',1,0,0))
attributes_pg_am.append(('amstrategies','smallint','Value_not_set','amstrategies-smallint',1,0,0))
attributes_pg_am.append(('amsupport','smallint','Value_not_set','amsupport-smallint',1,0,0))
attributes_pg_am.append(('amcanorder','boolean','Value_not_set','amcanorder-boolean',1,0,0))
attributes_pg_am.append(('amcanorderbyop','boolean','Value_not_set','amcanorderbyop-boolean',1,0,0))
attributes_pg_am.append(('amcanbackward','boolean','Value_not_set','amcanbackward-boolean',1,0,0))
attributes_pg_am.append(('amcanunique','boolean','Value_not_set','amcanunique-boolean',1,0,0))
attributes_pg_am.append(('amcanmulticol','boolean','Value_not_set','amcanmulticol-boolean',1,0,0))
attributes_pg_am.append(('amoptionalkey','boolean','Value_not_set','amoptionalkey-boolean',1,0,0))
attributes_pg_am.append(('amsearcharray','boolean','Value_not_set','amsearcharray-boolean',1,0,0))
attributes_pg_am.append(('amsearchnulls','boolean','Value_not_set','amsearchnulls-boolean',1,0,0))
attributes_pg_am.append(('amstorage','boolean','Value_not_set','amstorage-boolean',1,0,0))
attributes_pg_am.append(('amclusterable','boolean','Value_not_set','amclusterable-boolean',1,0,0))
attributes_pg_am.append(('ampredlocks','boolean','Value_not_set','ampredlocks-boolean',1,0,0))
attributes_pg_am.append(('amkeytype','oid','Value_not_set','amkeytype-oid',1,0,0))
attributes_pg_am.append(('aminsert','regproc','Value_not_set','aminsert-regproc',1,0,0))
attributes_pg_am.append(('ambeginscan','regproc','Value_not_set','ambeginscan-regproc',1,0,0))
attributes_pg_am.append(('amgettuple','regproc','Value_not_set','amgettuple-regproc',1,0,0))
attributes_pg_am.append(('amgetbitmap','regproc','Value_not_set','amgetbitmap-regproc',1,0,0))
attributes_pg_am.append(('amrescan','regproc','Value_not_set','amrescan-regproc',1,0,0))
attributes_pg_am.append(('amendscan','regproc','Value_not_set','amendscan-regproc',1,0,0))
attributes_pg_am.append(('ammarkpos','regproc','Value_not_set','ammarkpos-regproc',1,0,0))
attributes_pg_am.append(('amrestrpos','regproc','Value_not_set','amrestrpos-regproc',1,0,0))
attributes_pg_am.append(('ambuild','regproc','Value_not_set','ambuild-regproc',1,0,0))
attributes_pg_am.append(('ambuildempty','regproc','Value_not_set','ambuildempty-regproc',1,0,0))
attributes_pg_am.append(('ambulkdelete','regproc','Value_not_set','ambulkdelete-regproc',1,0,0))
attributes_pg_am.append(('amvacuumcleanup','regproc','Value_not_set','amvacuumcleanup-regproc',1,0,0))
attributes_pg_am.append(('amcanreturn','regproc','Value_not_set','amcanreturn-regproc',1,0,0))
attributes_pg_am.append(('amcostestimate','regproc','Value_not_set','amcostestimate-regproc',1,0,0))
attributes_pg_am.append(('amoptions','regproc','Value_not_set','amoptions-regproc',1,0,0))
attributes_pg_amop.append(('amopfamily','oid','Value_not_set','amopfamily-oid',1,0,0))
attributes_pg_amop.append(('amoplefttype','oid','Value_not_set','amoplefttype-oid',1,0,0))
attributes_pg_amop.append(('amoprighttype','oid','Value_not_set','amoprighttype-oid',1,0,0))
attributes_pg_amop.append(('amopstrategy','smallint','Value_not_set','amopstrategy-smallint',1,0,0))
attributes_pg_amop.append(('amoppurpose','"char"','Value_not_set','amoppurpose-"char"',1,0,0))
attributes_pg_amop.append(('amopopr','oid','Value_not_set','amopopr-oid',1,0,0))
attributes_pg_amop.append(('amopmethod','oid','Value_not_set','amopmethod-oid',1,0,0))
attributes_pg_amop.append(('amopsortfamily','oid','Value_not_set','amopsortfamily-oid',1,0,0))
attributes_pg_amproc.append(('amprocfamily','oid','Value_not_set','amprocfamily-oid',1,0,0))
attributes_pg_amproc.append(('amproclefttype','oid','Value_not_set','amproclefttype-oid',1,0,0))
attributes_pg_amproc.append(('amprocrighttype','oid','Value_not_set','amprocrighttype-oid',1,0,0))
attributes_pg_amproc.append(('amprocnum','smallint','Value_not_set','amprocnum-smallint',1,0,0))
attributes_pg_amproc.append(('amproc','regproc','Value_not_set','amproc-regproc',1,0,0))
attributes_pg_language.append(('lanname','name','Value_not_set','lanname-name',1,0,0))
attributes_pg_language.append(('lanowner','oid','Value_not_set','lanowner-oid',1,0,0))
attributes_pg_language.append(('lanispl','boolean','Value_not_set','lanispl-boolean',1,0,0))
attributes_pg_language.append(('lanpltrusted','boolean','Value_not_set','lanpltrusted-boolean',1,0,0))
attributes_pg_language.append(('lanplcallfoid','oid','Value_not_set','lanplcallfoid-oid',1,0,0))
attributes_pg_language.append(('laninline','oid','Value_not_set','laninline-oid',1,0,0))
attributes_pg_language.append(('lanvalidator','oid','Value_not_set','lanvalidator-oid',1,0,0))
attributes_pg_language.append(('lanacl','ARRAY','Value_not_set','lanacl-ARRAY',1,0,0))
attributes_pg_largeobject_metadata.append(('lomowner','oid','Value_not_set','lomowner-oid',1,0,0))
attributes_pg_largeobject_metadata.append(('lomacl','ARRAY','Value_not_set','lomacl-ARRAY',1,0,0))
attributes_pg_aggregate.append(('aggfnoid','regproc','Value_not_set','aggfnoid-regproc',1,0,0))
attributes_pg_aggregate.append(('aggtransfn','regproc','Value_not_set','aggtransfn-regproc',1,0,0))
attributes_pg_aggregate.append(('aggfinalfn','regproc','Value_not_set','aggfinalfn-regproc',1,0,0))
attributes_pg_aggregate.append(('aggsortop','oid','Value_not_set','aggsortop-oid',1,0,0))
attributes_pg_aggregate.append(('aggtranstype','oid','Value_not_set','aggtranstype-oid',1,0,0))
attributes_pg_aggregate.append(('agginitval','text','Value_not_set','agginitval-text',1,0,0))
attributes_pg_database.append(('datname','name','Value_not_set','datname-name',1,0,0))
attributes_pg_database.append(('datdba','oid','Value_not_set','datdba-oid',1,0,0))
attributes_pg_database.append(('encoding','integer','Value_not_set','encoding-integer',1,0,0))
attributes_pg_database.append(('datcollate','name','Value_not_set','datcollate-name',1,0,0))
attributes_pg_database.append(('datctype','name','Value_not_set','datctype-name',1,0,0))
attributes_pg_database.append(('datistemplate','boolean','Value_not_set','datistemplate-boolean',1,0,0))
attributes_pg_database.append(('datallowconn','boolean','Value_not_set','datallowconn-boolean',1,0,0))
attributes_pg_database.append(('datconnlimit','integer','Value_not_set','datconnlimit-integer',1,0,0))
attributes_pg_database.append(('datlastsysoid','oid','Value_not_set','datlastsysoid-oid',1,0,0))
attributes_pg_database.append(('datfrozenxid','xid','Value_not_set','datfrozenxid-xid',1,0,0))
attributes_pg_database.append(('datminmxid','xid','Value_not_set','datminmxid-xid',1,0,0))
attributes_pg_database.append(('dattablespace','oid','Value_not_set','dattablespace-oid',1,0,0))
attributes_pg_database.append(('datacl','ARRAY','Value_not_set','datacl-ARRAY',1,0,0))
attributes_pg_trigger.append(('tgrelid','oid','Value_not_set','tgrelid-oid',1,0,0))
attributes_pg_trigger.append(('tgname','name','Value_not_set','tgname-name',1,0,0))
attributes_pg_trigger.append(('tgfoid','oid','Value_not_set','tgfoid-oid',1,0,0))
attributes_pg_trigger.append(('tgtype','smallint','Value_not_set','tgtype-smallint',1,0,0))
attributes_pg_trigger.append(('tgenabled','"char"','Value_not_set','tgenabled-"char"',1,0,0))
attributes_pg_trigger.append(('tgisinternal','boolean','Value_not_set','tgisinternal-boolean',1,0,0))
attributes_pg_trigger.append(('tgconstrrelid','oid','Value_not_set','tgconstrrelid-oid',1,0,0))
attributes_pg_trigger.append(('tgconstrindid','oid','Value_not_set','tgconstrindid-oid',1,0,0))
attributes_pg_trigger.append(('tgconstraint','oid','Value_not_set','tgconstraint-oid',1,0,0))
attributes_pg_trigger.append(('tgdeferrable','boolean','Value_not_set','tgdeferrable-boolean',1,0,0))
attributes_pg_trigger.append(('tginitdeferred','boolean','Value_not_set','tginitdeferred-boolean',1,0,0))
attributes_pg_trigger.append(('tgnargs','smallint','Value_not_set','tgnargs-smallint',1,0,0))
attributes_pg_trigger.append(('tgattr','ARRAY','Value_not_set','tgattr-ARRAY',1,0,0))
attributes_pg_trigger.append(('tgargs','bytea','Value_not_set','tgargs-bytea',1,0,0))
attributes_pg_trigger.append(('tgqual','pg_node_tree','Value_not_set','tgqual-pg_node_tree',1,0,0))
attributes_pg_event_trigger.append(('evtname','name','Value_not_set','evtname-name',1,0,0))
attributes_pg_event_trigger.append(('evtevent','name','Value_not_set','evtevent-name',1,0,0))
attributes_pg_event_trigger.append(('evtowner','oid','Value_not_set','evtowner-oid',1,0,0))
attributes_pg_event_trigger.append(('evtfoid','oid','Value_not_set','evtfoid-oid',1,0,0))
attributes_pg_event_trigger.append(('evtenabled','"char"','Value_not_set','evtenabled-"char"',1,0,0))
attributes_pg_event_trigger.append(('evttags','ARRAY','Value_not_set','evttags-ARRAY',1,0,0))
attributes_pg_description.append(('objoid','oid','Value_not_set','objoid-oid',1,0,0))
attributes_pg_description.append(('classoid','oid','Value_not_set','classoid-oid',1,0,0))
attributes_pg_description.append(('objsubid','integer','Value_not_set','objsubid-integer',1,0,0))
attributes_pg_description.append(('description','text','Value_not_set','description-text',1,0,0))
attributes_pg_cast.append(('castsource','oid','Value_not_set','castsource-oid',1,0,0))
attributes_pg_cast.append(('casttarget','oid','Value_not_set','casttarget-oid',1,0,0))
attributes_pg_cast.append(('castfunc','oid','Value_not_set','castfunc-oid',1,0,0))
attributes_pg_cast.append(('castcontext','"char"','Value_not_set','castcontext-"char"',1,0,0))
attributes_pg_cast.append(('castmethod','"char"','Value_not_set','castmethod-"char"',1,0,0))
attributes_pg_enum.append(('enumtypid','oid','Value_not_set','enumtypid-oid',1,0,0))
attributes_pg_enum.append(('enumsortorder','real','Value_not_set','enumsortorder-real',1,0,0))
attributes_pg_enum.append(('enumlabel','name','Value_not_set','enumlabel-name',1,0,0))
attributes_pg_conversion.append(('conname','name','Value_not_set','conname-name',1,0,0))
attributes_pg_conversion.append(('connamespace','oid','Value_not_set','connamespace-oid',1,0,0))
attributes_pg_conversion.append(('conowner','oid','Value_not_set','conowner-oid',1,0,0))
attributes_pg_conversion.append(('conforencoding','integer','Value_not_set','conforencoding-integer',1,0,0))
attributes_pg_conversion.append(('contoencoding','integer','Value_not_set','contoencoding-integer',1,0,0))
attributes_pg_conversion.append(('conproc','regproc','Value_not_set','conproc-regproc',1,0,0))
attributes_pg_conversion.append(('condefault','boolean','Value_not_set','condefault-boolean',1,0,0))
attributes_pg_depend.append(('classid','oid','Value_not_set','classid-oid',1,0,0))
attributes_pg_depend.append(('objid','oid','Value_not_set','objid-oid',1,0,0))
attributes_pg_depend.append(('objsubid','integer','Value_not_set','objsubid-integer',1,0,0))
attributes_pg_depend.append(('refclassid','oid','Value_not_set','refclassid-oid',1,0,0))
attributes_pg_depend.append(('refobjid','oid','Value_not_set','refobjid-oid',1,0,0))
attributes_pg_depend.append(('refobjsubid','integer','Value_not_set','refobjsubid-integer',1,0,0))
attributes_pg_depend.append(('deptype','"char"','Value_not_set','deptype-"char"',1,0,0))
attributes_pg_db_role_setting.append(('setdatabase','oid','Value_not_set','setdatabase-oid',1,0,0))
attributes_pg_db_role_setting.append(('setrole','oid','Value_not_set','setrole-oid',1,0,0))
attributes_pg_db_role_setting.append(('setconfig','ARRAY','Value_not_set','setconfig-ARRAY',1,0,0))
attributes_pg_tablespace.append(('spcname','name','Value_not_set','spcname-name',1,0,0))
attributes_pg_tablespace.append(('spcowner','oid','Value_not_set','spcowner-oid',1,0,0))
attributes_pg_tablespace.append(('spcacl','ARRAY','Value_not_set','spcacl-ARRAY',1,0,0))
attributes_pg_tablespace.append(('spcoptions','ARRAY','Value_not_set','spcoptions-ARRAY',1,0,0))
attributes_pg_pltemplate.append(('tmplname','name','Value_not_set','tmplname-name',1,0,0))
attributes_pg_pltemplate.append(('tmpltrusted','boolean','Value_not_set','tmpltrusted-boolean',1,0,0))
attributes_pg_pltemplate.append(('tmpldbacreate','boolean','Value_not_set','tmpldbacreate-boolean',1,0,0))
attributes_pg_pltemplate.append(('tmplhandler','text','Value_not_set','tmplhandler-text',1,0,0))
attributes_pg_pltemplate.append(('tmplinline','text','Value_not_set','tmplinline-text',1,0,0))
attributes_pg_pltemplate.append(('tmplvalidator','text','Value_not_set','tmplvalidator-text',1,0,0))
attributes_pg_pltemplate.append(('tmpllibrary','text','Value_not_set','tmpllibrary-text',1,0,0))
attributes_pg_pltemplate.append(('tmplacl','ARRAY','Value_not_set','tmplacl-ARRAY',1,0,0))
attributes_pg_auth_members.append(('roleid','oid','Value_not_set','roleid-oid',1,0,0))
attributes_pg_auth_members.append(('member','oid','Value_not_set','member-oid',1,0,0))
attributes_pg_auth_members.append(('grantor','oid','Value_not_set','grantor-oid',1,0,0))
attributes_pg_auth_members.append(('admin_option','boolean','Value_not_set','admin_option-boolean',1,0,0))
attributes_pg_shdepend.append(('dbid','oid','Value_not_set','dbid-oid',1,0,0))
attributes_pg_shdepend.append(('classid','oid','Value_not_set','classid-oid',1,0,0))
attributes_pg_shdepend.append(('objid','oid','Value_not_set','objid-oid',1,0,0))
attributes_pg_shdepend.append(('objsubid','integer','Value_not_set','objsubid-integer',1,0,0))
attributes_pg_shdepend.append(('refclassid','oid','Value_not_set','refclassid-oid',1,0,0))
attributes_pg_shdepend.append(('refobjid','oid','Value_not_set','refobjid-oid',1,0,0))
attributes_pg_shdepend.append(('deptype','"char"','Value_not_set','deptype-"char"',1,0,0))
attributes_pg_shdescription.append(('objoid','oid','Value_not_set','objoid-oid',1,0,0))
attributes_pg_shdescription.append(('classoid','oid','Value_not_set','classoid-oid',1,0,0))
attributes_pg_shdescription.append(('description','text','Value_not_set','description-text',1,0,0))
attributes_pg_ts_config.append(('cfgname','name','Value_not_set','cfgname-name',1,0,0))
attributes_pg_ts_config.append(('cfgnamespace','oid','Value_not_set','cfgnamespace-oid',1,0,0))
attributes_pg_ts_config.append(('cfgowner','oid','Value_not_set','cfgowner-oid',1,0,0))
attributes_pg_ts_config.append(('cfgparser','oid','Value_not_set','cfgparser-oid',1,0,0))
attributes_pg_ts_config_map.append(('mapcfg','oid','Value_not_set','mapcfg-oid',1,0,0))
attributes_pg_ts_config_map.append(('maptokentype','integer','Value_not_set','maptokentype-integer',1,0,0))
attributes_pg_ts_config_map.append(('mapseqno','integer','Value_not_set','mapseqno-integer',1,0,0))
attributes_pg_ts_config_map.append(('mapdict','oid','Value_not_set','mapdict-oid',1,0,0))
attributes_pg_ts_dict.append(('dictname','name','Value_not_set','dictname-name',1,0,0))
attributes_pg_ts_dict.append(('dictnamespace','oid','Value_not_set','dictnamespace-oid',1,0,0))
attributes_pg_ts_dict.append(('dictowner','oid','Value_not_set','dictowner-oid',1,0,0))
attributes_pg_ts_dict.append(('dicttemplate','oid','Value_not_set','dicttemplate-oid',1,0,0))
attributes_pg_ts_dict.append(('dictinitoption','text','Value_not_set','dictinitoption-text',1,0,0))
attributes_pg_ts_parser.append(('prsname','name','Value_not_set','prsname-name',1,0,0))
attributes_pg_ts_parser.append(('prsnamespace','oid','Value_not_set','prsnamespace-oid',1,0,0))
attributes_pg_ts_parser.append(('prsstart','regproc','Value_not_set','prsstart-regproc',1,0,0))
attributes_pg_ts_parser.append(('prstoken','regproc','Value_not_set','prstoken-regproc',1,0,0))
attributes_pg_ts_parser.append(('prsend','regproc','Value_not_set','prsend-regproc',1,0,0))
attributes_pg_ts_parser.append(('prsheadline','regproc','Value_not_set','prsheadline-regproc',1,0,0))
attributes_pg_ts_parser.append(('prslextype','regproc','Value_not_set','prslextype-regproc',1,0,0))
attributes_pg_ts_template.append(('tmplname','name','Value_not_set','tmplname-name',1,0,0))
attributes_pg_ts_template.append(('tmplnamespace','oid','Value_not_set','tmplnamespace-oid',1,0,0))
attributes_pg_ts_template.append(('tmplinit','regproc','Value_not_set','tmplinit-regproc',1,0,0))
attributes_pg_ts_template.append(('tmpllexize','regproc','Value_not_set','tmpllexize-regproc',1,0,0))
attributes_pg_extension.append(('extname','name','Value_not_set','extname-name',1,0,0))
attributes_pg_extension.append(('extowner','oid','Value_not_set','extowner-oid',1,0,0))
attributes_pg_extension.append(('extnamespace','oid','Value_not_set','extnamespace-oid',1,0,0))
attributes_pg_extension.append(('extrelocatable','boolean','Value_not_set','extrelocatable-boolean',1,0,0))
attributes_pg_extension.append(('extversion','text','Value_not_set','extversion-text',1,0,0))
attributes_pg_extension.append(('extconfig','ARRAY','Value_not_set','extconfig-ARRAY',1,0,0))
attributes_pg_extension.append(('extcondition','ARRAY','Value_not_set','extcondition-ARRAY',1,0,0))
attributes_pg_foreign_data_wrapper.append(('fdwname','name','Value_not_set','fdwname-name',1,0,0))
attributes_pg_foreign_data_wrapper.append(('fdwowner','oid','Value_not_set','fdwowner-oid',1,0,0))
attributes_pg_foreign_data_wrapper.append(('fdwhandler','oid','Value_not_set','fdwhandler-oid',1,0,0))
attributes_pg_foreign_data_wrapper.append(('fdwvalidator','oid','Value_not_set','fdwvalidator-oid',1,0,0))
attributes_pg_foreign_data_wrapper.append(('fdwacl','ARRAY','Value_not_set','fdwacl-ARRAY',1,0,0))
attributes_pg_foreign_data_wrapper.append(('fdwoptions','ARRAY','Value_not_set','fdwoptions-ARRAY',1,0,0))
attributes_pg_foreign_server.append(('srvname','name','Value_not_set','srvname-name',1,0,0))
attributes_pg_foreign_server.append(('srvowner','oid','Value_not_set','srvowner-oid',1,0,0))
attributes_pg_foreign_server.append(('srvfdw','oid','Value_not_set','srvfdw-oid',1,0,0))
attributes_pg_foreign_server.append(('srvtype','text','Value_not_set','srvtype-text',1,0,0))
attributes_pg_foreign_server.append(('srvversion','text','Value_not_set','srvversion-text',1,0,0))
attributes_pg_foreign_server.append(('srvacl','ARRAY','Value_not_set','srvacl-ARRAY',1,0,0))
attributes_pg_foreign_server.append(('srvoptions','ARRAY','Value_not_set','srvoptions-ARRAY',1,0,0))
attributes_pg_foreign_table.append(('ftrelid','oid','Value_not_set','ftrelid-oid',1,0,0))
attributes_pg_foreign_table.append(('ftserver','oid','Value_not_set','ftserver-oid',1,0,0))
attributes_pg_foreign_table.append(('ftoptions','ARRAY','Value_not_set','ftoptions-ARRAY',1,0,0))
attributes_pg_default_acl.append(('defaclrole','oid','Value_not_set','defaclrole-oid',1,0,0))
attributes_pg_default_acl.append(('defaclnamespace','oid','Value_not_set','defaclnamespace-oid',1,0,0))
attributes_pg_default_acl.append(('defaclobjtype','"char"','Value_not_set','defaclobjtype-"char"',1,0,0))
attributes_pg_default_acl.append(('defaclacl','ARRAY','Value_not_set','defaclacl-ARRAY',1,0,0))
attributes_pg_seclabel.append(('objoid','oid','Value_not_set','objoid-oid',1,0,0))
attributes_pg_seclabel.append(('classoid','oid','Value_not_set','classoid-oid',1,0,0))
attributes_pg_seclabel.append(('objsubid','integer','Value_not_set','objsubid-integer',1,0,0))
attributes_pg_seclabel.append(('provider','text','Value_not_set','provider-text',1,0,0))
attributes_pg_seclabel.append(('label','text','Value_not_set','label-text',1,0,0))
attributes_pg_shseclabel.append(('objoid','oid','Value_not_set','objoid-oid',1,0,0))
attributes_pg_shseclabel.append(('classoid','oid','Value_not_set','classoid-oid',1,0,0))
attributes_pg_shseclabel.append(('provider','text','Value_not_set','provider-text',1,0,0))
attributes_pg_shseclabel.append(('label','text','Value_not_set','label-text',1,0,0))
attributes_pg_collation.append(('collname','name','Value_not_set','collname-name',1,0,0))
attributes_pg_collation.append(('collnamespace','oid','Value_not_set','collnamespace-oid',1,0,0))
attributes_pg_collation.append(('collowner','oid','Value_not_set','collowner-oid',1,0,0))
attributes_pg_collation.append(('collencoding','integer','Value_not_set','collencoding-integer',1,0,0))
attributes_pg_collation.append(('collcollate','name','Value_not_set','collcollate-name',1,0,0))
attributes_pg_collation.append(('collctype','name','Value_not_set','collctype-name',1,0,0))
attributes_pg_range.append(('rngtypid','oid','Value_not_set','rngtypid-oid',1,0,0))
attributes_pg_range.append(('rngsubtype','oid','Value_not_set','rngsubtype-oid',1,0,0))
attributes_pg_range.append(('rngcollation','oid','Value_not_set','rngcollation-oid',1,0,0))
attributes_pg_range.append(('rngsubopc','oid','Value_not_set','rngsubopc-oid',1,0,0))
attributes_pg_range.append(('rngcanonical','regproc','Value_not_set','rngcanonical-regproc',1,0,0))
attributes_pg_range.append(('rngsubdiff','regproc','Value_not_set','rngsubdiff-regproc',1,0,0))
attributes_pg_largeobject.append(('loid','oid','Value_not_set','loid-oid',1,0,0))
attributes_pg_largeobject.append(('pageno','integer','Value_not_set','pageno-integer',1,0,0))
attributes_pg_largeobject.append(('data','bytea','Value_not_set','data-bytea',1,0,0))
attributes_pg_attrdef.append(('adrelid','oid','Value_not_set','adrelid-oid',1,0,0))
attributes_pg_attrdef.append(('adnum','smallint','Value_not_set','adnum-smallint',1,0,0))
attributes_pg_attrdef.append(('adbin','pg_node_tree','Value_not_set','adbin-pg_node_tree',1,0,0))
attributes_pg_attrdef.append(('adsrc','text','Value_not_set','adsrc-text',1,0,0))
attributes_pg_namespace.append(('nspname','name','Value_not_set','nspname-name',1,0,0))
attributes_pg_namespace.append(('nspowner','oid','Value_not_set','nspowner-oid',1,0,0))
attributes_pg_namespace.append(('nspacl','ARRAY','Value_not_set','nspacl-ARRAY',1,0,0))
attributes_pg_rewrite.append(('rulename','name','Value_not_set','rulename-name',1,0,0))
attributes_pg_rewrite.append(('ev_class','oid','Value_not_set','ev_class-oid',1,0,0))
attributes_pg_rewrite.append(('ev_attr','smallint','Value_not_set','ev_attr-smallint',1,0,0))
attributes_pg_rewrite.append(('ev_type','"char"','Value_not_set','ev_type-"char"',1,0,0))
attributes_pg_rewrite.append(('ev_enabled','"char"','Value_not_set','ev_enabled-"char"',1,0,0))
attributes_pg_rewrite.append(('is_instead','boolean','Value_not_set','is_instead-boolean',1,0,0))
attributes_pg_rewrite.append(('ev_qual','pg_node_tree','Value_not_set','ev_qual-pg_node_tree',1,0,0))
attributes_pg_rewrite.append(('ev_action','pg_node_tree','Value_not_set','ev_action-pg_node_tree',1,0,0))
pg_conversion.properties["attributes"]=attributes_pg_conversion
pg_foreign_data_wrapper.properties["attributes"]=attributes_pg_foreign_data_wrapper
pg_collation.properties["attributes"]=attributes_pg_collation
pg_range.properties["attributes"]=attributes_pg_range
pg_class.properties["attributes"]=attributes_pg_class
pg_opclass.properties["attributes"]=attributes_pg_opclass
pg_opfamily.properties["attributes"]=attributes_pg_opfamily
pg_trigger.properties["attributes"]=attributes_pg_trigger
pg_auth_members.properties["attributes"]=attributes_pg_auth_members
pg_operator.properties["attributes"]=attributes_pg_operator
pg_attribute.properties["attributes"]=attributes_pg_attribute
pg_ts_parser.properties["attributes"]=attributes_pg_ts_parser
pg_default_acl.properties["attributes"]=attributes_pg_default_acl
pg_depend.properties["attributes"]=attributes_pg_depend
pg_statistic.properties["attributes"]=attributes_pg_statistic
pg_attrdef.properties["attributes"]=attributes_pg_attrdef
pg_language.properties["attributes"]=attributes_pg_language
pg_tablespace.properties["attributes"]=attributes_pg_tablespace
pg_db_role_setting.properties["attributes"]=attributes_pg_db_role_setting
pg_seclabel.properties["attributes"]=attributes_pg_seclabel
pg_shdepend.properties["attributes"]=attributes_pg_shdepend
pg_event_trigger.properties["attributes"]=attributes_pg_event_trigger
pg_shseclabel.properties["attributes"]=attributes_pg_shseclabel
pg_aggregate.properties["attributes"]=attributes_pg_aggregate
pg_index.properties["attributes"]=attributes_pg_index
pg_inherits.properties["attributes"]=attributes_pg_inherits
pg_rewrite.properties["attributes"]=attributes_pg_rewrite
pg_ts_dict.properties["attributes"]=attributes_pg_ts_dict
pg_foreign_table.properties["attributes"]=attributes_pg_foreign_table
pg_constraint.properties["attributes"]=attributes_pg_constraint
pg_enum.properties["attributes"]=attributes_pg_enum
pg_amproc.properties["attributes"]=attributes_pg_amproc
pg_extension.properties["attributes"]=attributes_pg_extension
pg_shdescription.properties["attributes"]=attributes_pg_shdescription
pg_pltemplate.properties["attributes"]=attributes_pg_pltemplate
pg_ts_config.properties["attributes"]=attributes_pg_ts_config
pg_amop.properties["attributes"]=attributes_pg_amop
pg_cast.properties["attributes"]=attributes_pg_cast
pg_user_mapping.properties["attributes"]=attributes_pg_user_mapping
pg_type.properties["attributes"]=attributes_pg_type
pg_largeobject.properties["attributes"]=attributes_pg_largeobject
pg_am.properties["attributes"]=attributes_pg_am
pg_largeobject_metadata.properties["attributes"]=attributes_pg_largeobject_metadata
pg_ts_config_map.properties["attributes"]=attributes_pg_ts_config_map
pg_ts_template.properties["attributes"]=attributes_pg_ts_template
pg_database.properties["attributes"]=attributes_pg_database
pg_namespace.properties["attributes"]=attributes_pg_namespace
pg_proc.properties["attributes"]=attributes_pg_proc
pg_foreign_server.properties["attributes"]=attributes_pg_foreign_server
pg_authid.properties["attributes"]=attributes_pg_authid
pg_description.properties["attributes"]=attributes_pg_description
layer.add_object(pg_conversion)
layer.add_object(pg_foreign_data_wrapper)
layer.add_object(pg_collation)
layer.add_object(pg_range)
layer.add_object(pg_class)
layer.add_object(pg_opclass)
layer.add_object(pg_opfamily)
layer.add_object(pg_trigger)
layer.add_object(pg_auth_members)
layer.add_object(pg_operator)
layer.add_object(pg_attribute)
layer.add_object(pg_ts_parser)
layer.add_object(pg_default_acl)
layer.add_object(pg_depend)
layer.add_object(pg_statistic)
layer.add_object(pg_attrdef)
layer.add_object(pg_language)
layer.add_object(pg_tablespace)
layer.add_object(pg_db_role_setting)
layer.add_object(pg_seclabel)
layer.add_object(pg_shdepend)
layer.add_object(pg_event_trigger)
layer.add_object(pg_shseclabel)
layer.add_object(pg_aggregate)
layer.add_object(pg_index)
layer.add_object(pg_inherits)
layer.add_object(pg_rewrite)
layer.add_object(pg_ts_dict)
layer.add_object(pg_foreign_table)
layer.add_object(pg_constraint)
layer.add_object(pg_enum)
layer.add_object(pg_amproc)
layer.add_object(pg_extension)
layer.add_object(pg_shdescription)
layer.add_object(pg_pltemplate)
layer.add_object(pg_ts_config)
layer.add_object(pg_amop)
layer.add_object(pg_cast)
layer.add_object(pg_user_mapping)
layer.add_object(pg_type)
layer.add_object(pg_largeobject)
layer.add_object(pg_am)
layer.add_object(pg_largeobject_metadata)
layer.add_object(pg_ts_config_map)
layer.add_object(pg_ts_template)
layer.add_object(pg_database)
layer.add_object(pg_namespace)
layer.add_object(pg_proc)
layer.add_object(pg_foreign_server)
layer.add_object(pg_authid)
layer.add_object(pg_description)
oType=dia.get_object_type("UML - Activity")
oend,h1,h2=oType.create(0,-3)
theObjects.append(oend)
oend.properties["text"]="PG_CATALOG: Claudio Bezerra Leopoldino - Meu Blog de PostgreSQL - http://postgresqlbr.blogspot.com.br/ - claudiob_br@yahoo.com.br"
layer.add_object(oend)
diagram.save()
print"GenerationFINISHED"

3 - Execução do script no DIA

É feita através do python console, acessado pelo menu "Diálogos/Python Console" do DIA.

Para executar, basta digitar no console "execfile("nomedoarquivo")" e confirmar com enter, como na figura abaixo. Após a execução, o DIA criará o diagrama de acord com o script python fornecido.

Figura 3 - Python Console

4 - Geração de Script Python - INFORMATION_SCHEMA

Abaixo coloco a consulta de geração do diagrama do esquema information_schema:

SELECT CAST ('diagram = dia.new("INFORMATION_SCHEMA.dia")' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('data = diagram.data' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('display = diagram.display()' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('layer = data.active_layer' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('pg_objtype = "UML - Class"' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('oType = dia.get_object_type (pg_objtype)' AS TEXT) as OBJETO
UNION ALL
SELECT CAST ('theObjects = [diagram, data, layer, display, oType]' AS TEXT) as OBJETO
UNION ALL
SELECT CAST(relname || ', h1, h2 = oType.create (' || 20*(row_number() OVER (PARTITION BY schemaname)) || ',1)' as TEXT)  as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema'
UNION ALL
SELECT CAST('theObjects.append(' || relname || ')' as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema'
UNION ALL
SELECT CAST(relname || '.properties["stereotype"] = "information_schema"' as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema'
UNION ALL
SELECT CAST(relname || '.properties["comment"] = "' || relname || ' Class"'  as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema'
UNION ALL
SELECT CAST(relname || '.properties["name"] = "' || relname || '"'  as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema'
UNION ALL
SELECT CAST('attributes_' || relname || ' = []' as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema'
UNION ALL
SELECT CAST('attributes_' || table_name || '.append((''' || column_name || ''',''' || data_type ||''',''Value_not_set'',''' || column_name || '-' || data_type || ''',1,0,0)) ' AS TEXT) as OBJETO from information_schema.columns where table_schema = 'information_schema' AND table_name IN (SELECT relname as table_name FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema')
UNION ALL
SELECT CAST(relname || '.properties["attributes"] = attributes_' || relname as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema'
UNION ALL
SELECT CAST('layer.add_object (' || relname || ')' as TEXT) as OBJETO FROM pg_catalog.pg_statio_sys_tables WHERE schemaname = 'information_schema'
UNION ALL
SELECT CAST('oType = dia.get_object_type ("UML - Activity")' as TEXT) as OBJETO
UNION ALL
SELECT CAST('oend, h1, h2 = oType.create (0,-1)' as TEXT) as OBJETO
UNION ALL
SELECT CAST('theObjects.append(oend)' as TEXT) as OBJETO
UNION ALL
SELECT CAST('oend.properties["text"] = "INFORMATION_SCHEMA: Claudio Bezerra Leopoldino - Meu Blog de PostgreSQL - http://postgresqlbr.blogspot.com.br/ - claudiob_br@yahoo.com.br"' as TEXT) as OBJETO
UNION ALL
SELECT CAST('layer.add_object (oend)' as TEXT) as OBJETO
UNION ALL
SELECT CAST('diagram.save()' as TEXT) as OBJETO
UNION ALL
SELECT CAST('print "Generation FINISHED"' as TEXT) as OBJETO;

5 - Script Python Produzido - INFORMATION_SCHEMA 

Abaixo coloco a listagem do script python produzido e editado:

diagram=dia.new("INFORMATION_SCHEMA.dia")
data=diagram.data
display=diagram.display()
layer=data.active_layer
pg_objtype="UML - Class"
oType=dia.get_object_type(pg_objtype)
theObjects=[diagram,data,layer,display,oType]
sql_implementation_info,h1,h2=oType.create(20,1)
sql_sizing_profiles,h1,h2=oType.create(40,1)
sql_features,h1,h2=oType.create(60,1)
sql_languages,h1,h2=oType.create(80,1)
sql_packages,h1,h2=oType.create(100,1)
sql_parts,h1,h2=oType.create(120,1)
sql_sizing,h1,h2=oType.create(140,1)
theObjects.append(sql_implementation_info)
theObjects.append(sql_sizing_profiles)
theObjects.append(sql_features)
theObjects.append(sql_languages)
theObjects.append(sql_packages)
theObjects.append(sql_parts)
theObjects.append(sql_sizing)
sql_implementation_info.properties["stereotype"]="information_schema"
sql_sizing_profiles.properties["stereotype"]="information_schema"
sql_features.properties["stereotype"]="information_schema"
sql_languages.properties["stereotype"]="information_schema"
sql_packages.properties["stereotype"]="information_schema"
sql_parts.properties["stereotype"]="information_schema"
sql_sizing.properties["stereotype"]="information_schema"
sql_implementation_info.properties["comment"]="sql_implementation_infoClass"
sql_sizing_profiles.properties["comment"]="sql_sizing_profilesClass"
sql_features.properties["comment"]="sql_featuresClass"
sql_languages.properties["comment"]="sql_languagesClass"
sql_packages.properties["comment"]="sql_packagesClass"
sql_parts.properties["comment"]="sql_partsClass"
sql_sizing.properties["comment"]="sql_sizingClass"
sql_implementation_info.properties["name"]="sql_implementation_info"
sql_sizing_profiles.properties["name"]="sql_sizing_profiles"
sql_features.properties["name"]="sql_features"
sql_languages.properties["name"]="sql_languages"
sql_packages.properties["name"]="sql_packages"
sql_parts.properties["name"]="sql_parts"
sql_sizing.properties["name"]="sql_sizing"
attributes_sql_implementation_info=[]
attributes_sql_sizing_profiles=[]
attributes_sql_features=[]
attributes_sql_languages=[]
attributes_sql_packages=[]
attributes_sql_parts=[]
attributes_sql_sizing=[]
attributes_sql_implementation_info.append(('implementation_info_id','charactervarying','Value_not_set','implementation_info_id-charactervarying',1,0,0))
attributes_sql_implementation_info.append(('implementation_info_name','charactervarying','Value_not_set','implementation_info_name-charactervarying',1,0,0))
attributes_sql_implementation_info.append(('integer_value','integer','Value_not_set','integer_value-integer',1,0,0))
attributes_sql_implementation_info.append(('character_value','charactervarying','Value_not_set','character_value-charactervarying',1,0,0))
attributes_sql_implementation_info.append(('comments','charactervarying','Value_not_set','comments-charactervarying',1,0,0))
attributes_sql_languages.append(('sql_language_source','charactervarying','Value_not_set','sql_language_source-charactervarying',1,0,0))
attributes_sql_languages.append(('sql_language_year','charactervarying','Value_not_set','sql_language_year-charactervarying',1,0,0))
attributes_sql_languages.append(('sql_language_conformance','charactervarying','Value_not_set','sql_language_conformance-charactervarying',1,0,0))
attributes_sql_languages.append(('sql_language_integrity','charactervarying','Value_not_set','sql_language_integrity-charactervarying',1,0,0))
attributes_sql_languages.append(('sql_language_implementation','charactervarying','Value_not_set','sql_language_implementation-charactervarying',1,0,0))
attributes_sql_languages.append(('sql_language_binding_style','charactervarying','Value_not_set','sql_language_binding_style-charactervarying',1,0,0))
attributes_sql_languages.append(('sql_language_programming_language','charactervarying','Value_not_set','sql_language_programming_language-charactervarying',1,0,0))
attributes_sql_packages.append(('feature_id','charactervarying','Value_not_set','feature_id-charactervarying',1,0,0))
attributes_sql_packages.append(('feature_name','charactervarying','Value_not_set','feature_name-charactervarying',1,0,0))
attributes_sql_packages.append(('is_supported','charactervarying','Value_not_set','is_supported-charactervarying',1,0,0))
attributes_sql_packages.append(('is_verified_by','charactervarying','Value_not_set','is_verified_by-charactervarying',1,0,0))
attributes_sql_packages.append(('comments','charactervarying','Value_not_set','comments-charactervarying',1,0,0))
attributes_sql_parts.append(('feature_id','charactervarying','Value_not_set','feature_id-charactervarying',1,0,0))
attributes_sql_parts.append(('feature_name','charactervarying','Value_not_set','feature_name-charactervarying',1,0,0))
attributes_sql_parts.append(('is_supported','charactervarying','Value_not_set','is_supported-charactervarying',1,0,0))
attributes_sql_parts.append(('is_verified_by','charactervarying','Value_not_set','is_verified_by-charactervarying',1,0,0))
attributes_sql_parts.append(('comments','charactervarying','Value_not_set','comments-charactervarying',1,0,0))
attributes_sql_sizing_profiles.append(('sizing_id','integer','Value_not_set','sizing_id-integer',1,0,0))
attributes_sql_sizing_profiles.append(('sizing_name','charactervarying','Value_not_set','sizing_name-charactervarying',1,0,0))
attributes_sql_sizing_profiles.append(('profile_id','charactervarying','Value_not_set','profile_id-charactervarying',1,0,0))
attributes_sql_sizing_profiles.append(('required_value','integer','Value_not_set','required_value-integer',1,0,0))
attributes_sql_sizing_profiles.append(('comments','charactervarying','Value_not_set','comments-charactervarying',1,0,0))
attributes_sql_features.append(('feature_id','charactervarying','Value_not_set','feature_id-charactervarying',1,0,0))
attributes_sql_features.append(('feature_name','charactervarying','Value_not_set','feature_name-charactervarying',1,0,0))
attributes_sql_features.append(('sub_feature_id','charactervarying','Value_not_set','sub_feature_id-charactervarying',1,0,0))
attributes_sql_features.append(('sub_feature_name','charactervarying','Value_not_set','sub_feature_name-charactervarying',1,0,0))
attributes_sql_features.append(('is_supported','charactervarying','Value_not_set','is_supported-charactervarying',1,0,0))
attributes_sql_features.append(('is_verified_by','charactervarying','Value_not_set','is_verified_by-charactervarying',1,0,0))
attributes_sql_features.append(('comments','charactervarying','Value_not_set','comments-charactervarying',1,0,0))
attributes_sql_sizing.append(('sizing_id','integer','Value_not_set','sizing_id-integer',1,0,0))
attributes_sql_sizing.append(('sizing_name','charactervarying','Value_not_set','sizing_name-charactervarying',1,0,0))
attributes_sql_sizing.append(('supported_value','integer','Value_not_set','supported_value-integer',1,0,0))
attributes_sql_sizing.append(('comments','charactervarying','Value_not_set','comments-charactervarying',1,0,0))
sql_implementation_info.properties["attributes"]=attributes_sql_implementation_info
sql_sizing_profiles.properties["attributes"]=attributes_sql_sizing_profiles
sql_features.properties["attributes"]=attributes_sql_features
sql_languages.properties["attributes"]=attributes_sql_languages
sql_packages.properties["attributes"]=attributes_sql_packages
sql_parts.properties["attributes"]=attributes_sql_parts
sql_sizing.properties["attributes"]=attributes_sql_sizing
layer.add_object(sql_implementation_info)
layer.add_object(sql_sizing_profiles)
layer.add_object(sql_features)
layer.add_object(sql_languages)
layer.add_object(sql_packages)
layer.add_object(sql_parts)
layer.add_object(sql_sizing)
oType=dia.get_object_type("UML - Activity")
oend,h1,h2=oType.create(0,-1)
theObjects.append(oend)
oend.properties["text"]="INFORMATION_SCHEMA: Claudio Bezerra Leopoldino - Meu Blog de PostgreSQL - http://postgresqlbr.blogspot.com.br/ - claudiob_br@yahoo.com.br"
layer.add_object(oend)
diagram.save()
print"GenerationFINISHED"

6 - Limitações e Considerações Finais

O Dia é mais uma ferramenta de modelagem de diagramas visuais que uma ferramenta de banco de dados, e esta não é uma limitação pequena, apesar da grande flexibilidade em se gerar bons resultados através de scripts. Não espere a geração de SQL, ou outras rotinas avançadas em uma ferramenta de diagramas!

O nosso script não faz o layout automático levando em conta os relacionamentos. Nos casos mostrados, não existem relacionamentos na forma foreign keis entre as tabelas de sistema, mas isso pode ser necessário para a sua necessidade. Implemente!

Você está convidado a indicar melhorias nos códigos aqui apresentados, criar seus diagramas, testar novos diagramas, e scripts, e a compartilhá-los!

Nada a temer com Lateral Joins no PostgreSQL 9.3!

A Junção Lateral, ou lateral join, permite subselects mais legíveis e de fácil entendimento, dentro da cláusula FROM dos comandos SELECT. Através deste recurso, um item da cláusula FROM pode referenciar outro que esteja à sua esquerda, isto é, colunas de tabelas já listadas na cláusula FROM podem ser diretamente referenciadas. Lateral joins são compatíveis com CROSS, INNER ou LEFT joins.




Adicionalmente, é possível fazer lateral joins utilizando o resultado de funções executadas na cláusula FROM como tabelas.

Antes de mostrar o funcionamento desta nova funcionalidade da versão 9.3 do postgres, vamos criar algumas tabelas básicas para utilização nos exemplos.

CREATE TABLE r1 (c1 integer, c2 integer);
INSERT INTO r1 VALUES (1,1);
INSERT INTO r1 VALUES (2,2);
INSERT INTO r1 VALUES (null,null);

CREATE TABLE r2 (c1 integer, c2 integer);
INSERT INTO r2 VALUES (1,1);
INSERT INTO r2 VALUES (2,2);
INSERT INTO r2 VALUES (null,null);

* Sintaxe com CROSS JOIN

Com o uso de LATERAL, ao invés de colocar referência ao campo da tabela r1 na cláusula WHERE, a mesma é feita dentro da cláusula FROM consulta, facilitando a legibilidade e apresentando o mesmo resultado final.

Isso ocorre tanto no produto cartesiano, ou cross join, quanto nas outras modalidades de lateral join.

postgres=# SELECT * FROM r1 CROSS JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  1 |  1 |  2 |  2
  2 |  2 |  1 |  1
  2 |  2 |  2 |  2
(4 registros)


* Sintaxe com INNER JOIN

postgres=# SELECT * FROM r1 INNER JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)

* Exemplo de sintaxe com LEFT JOIN

postgres=# SELECT * FROM r1 LEFT JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
    |    |    |  
(3 registros)

* Sintaxe com JOIN tradicional.




postgres=# SELECT * FROM r1 JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)





postgres=# SELECT * FROM r1, LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL
postgres-# WHERE r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)


* Não funciona com FULL JOIN

postgres=# SELECT * FROM r1 FULL JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
ERRO:  referência inválida para tabela "r1" na cláusula FROM
LINHA 1: ...N LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS N...
                                                              ^
DETALHE:  The combining JOIN type must be INNER or LEFT for a LATERAL reference.

* Também não funciona com RIGHT JOIN

postgres=# SELECT * FROM r1 RIGHT JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
ERRO:  referência inválida para tabela "r1" na cláusula FROM
LINHA 1: ...N LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS N...
                                                              ^
DETALHE:  The combining JOIN type must be INNER or LEFT for a LATERAL reference.

* Lateral Join envolvendo três tabelas

Para fazer este teste, é necessário criar a tabela r3. Observe que a segunda junção lateral referencia os dados com base no alias RLATERAL, utilizado na primeira junção.

CREATE TABLE r3 (c1 integer, c2 integer);
INSERT INTO r3 VALUES (1,1);
INSERT INTO r3 VALUES (2,2);
INSERT INTO r3 VALUES (null,null);

postgres=# SELECT *
postgres-# FROM r1
postgres-# JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1
postgres-# JOIN LATERAL (SELECT * FROM r3 WHERE r3.c1 <> 10 and RLATERAL.c1 IS NOT NULL) AS RLATERAL2 ON RLATERAL.c1 = RLATERAL2.c1;
 c1 | c2 | c1 | c2 | c1 | c2
----+----+----+----+----+----
  1 |  1 |  1 |  1 |  1 |  1
  2 |  2 |  2 |  2 |  2 |  2
(2 registros)

* Lateral Joins com Funções

Podem ser utilizadas subconsultas com o retorno de funções e lateral join. O exemplo abaixo ilustra esta funcionalidade.

Exemplo 1: produto cartesiano com a cláusula lateral.

postgres=# SELECT * FROM r1, LATERAL (SELECT generate_series(1,3) AS SERIE) AS RLATERAL;
 c1 | c2 | serie
----+----+-------
  1 |  1 |     1
  1 |  1 |     2
  1 |  1 |     3
  2 |  2 |     1
  2 |  2 |     2
  2 |  2 |     3
    |    |     1
    |    |     2
    |    |     3
(9 registros)

Exemplo 2: referenciando tabela da função.

postgres=# SELECT * FROM r1, LATERAL (SELECT generate_series(1,3) AS SERIE WHERE r1.c1 IS NULL) AS RLATERAL;
 c1 | c2 | serie
----+----+-------
    |    |     1
    |    |     2
    |    |     3
(3 registros)
 
* Lateral Joins e Desempenho

Não foi detectada qualquer diferença em termos de desempenho entre o lateral join e o uso de subqueries. O seu uso deve ser feito em virtude da maior clareza que dá à consulta. Os exemplos abaixo corroboram esta afirmativa:

Exemplo 1: Cross Join

postgres=# EXPLAIN SELECT * FROM r1 CROSS JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Nested Loop  (cost=0.00..56731.49 rows=4532641 width=16)
   ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
         Filter: (c1 IS NOT NULL)
   ->  Materialize  (cost=0.00..47.40 rows=2129 width=8)
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(6 registros)

postgres=# EXPLAIN SELECT * FROM r1 CROSS JOIN (SELECT * FROM r2 WHERE r2.c1 <> 10) AS RLATERAL
postgres-# WHERE r1.c1 IS NOT NULL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Nested Loop  (cost=0.00..56731.49 rows=4532641 width=16)
   ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
         Filter: (c1 IS NOT NULL)
   ->  Materialize  (cost=0.00..47.40 rows=2129 width=8)
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(6 registros)


Exemplo 2: Join Tradicional

postgres=# EXPLAIN
postgres-# SELECT * FROM r1 JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
                            QUERY PLAN                           
------------------------------------------------------------------
 Merge Join  (cost=303.53..654.12 rows=22663 width=16)
   Merge Cond: (r1.c1 = r2.c1)
   ->  Sort  (cost=149.09..154.41 rows=2129 width=8)
         Sort Key: r1.c1
         ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
               Filter: (c1 IS NOT NULL)
   ->  Sort  (cost=154.44..159.76 rows=2129 width=8)
         Sort Key: r2.c1
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(10 registros)

postgres=# EXPLAIN
postgres-# SELECT * FROM r1 JOIN (SELECT * FROM r2 WHERE r2.c1 <> 10) AS RLATERAL ON r1.c1 = RLATERAL.c1
postgres-# WHERE r1.c1 IS NOT NULL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Merge Join  (cost=303.53..654.12 rows=22663 width=16)
   Merge Cond: (r1.c1 = r2.c1)
   ->  Sort  (cost=149.09..154.41 rows=2129 width=8)
         Sort Key: r1.c1
         ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
               Filter: (c1 IS NOT NULL)
   ->  Sort  (cost=154.44..159.76 rows=2129 width=8)
         Sort Key: r2.c1
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(10 registros)

* Considerações finais

Lateral joins são úteis para gerar códigos mais legíveis, evitando a separação entre as condições de junção da cláusula WHERE e a tabela referenciada na cláusula FROM dos SELECTS. Este recurso parece ser particularmente útil para comandos muito grandes, que se tornam difíceis de manter. No entanto, se os desenvolvedores forem familiarizados com a sintaxe padrão, a mesma pode ser mantida sem problemas, evitando problemas no entendimento do SQL na manutenção das consultas.

Para quem deseja maior desempenho, o comando não trouxe benefícios.

Evite o uso de lateral joins caso necessite de portabilidade com outros SGBDs, optando pelas sintaxes mais tradicionais para a realização de consultas.

PostgreSQL 9.3 Lançado!

A aguardada versão 9.3 do PostgreSQL foi oficialmente lançada hoje. Divulgue, baixe, teste e use!

As principais alterações aumentam a performance, flexibilidade e a confiabilidade do banco.

Cito como destaques:
 - Foreign data wrappers que permitem a gravação em dados externos;
- Visões atualizáveis automaticamente;
- Possibilidade de rodar pg_dump com paralelismo, aumentando a performance desta operação;
- Vários outros recursos que você pode conferir aqui.

Você REALMENTE Sabe Lidar com Valores Nulos no PostgreSQL?

O título desta postagem é uma pergunta que me fiz. Eu achava que sim, mas ao fazer uns testes e pesquisas, vi que o assunto é mais amplo do que eu pensava inicialmente. Convido o leitor a explorar todas as principais variações  do tratamento de valores nulos no postgresql.

* Primeiro ponto: Nulo não é zero, nem '', nem espaço.

Nulo significa nulo, que o valor não foi preenchido. Vejamos o exemplo abaixo:

teste=# SELECT (null = '') IS TRUE AS COMPARA1, (null = ' ') IS TRUE AS COMPARA2, (null = 0) IS TRUE AS COMPARA3;
 compara1 | compara2 | compara3
----------+----------+----------
 f        | f        | f
(1 registro)

* Saiba testar se um valor é nulo ou não (IN NULL e IS NOT NULL)

O exemplo abaixo utiliza IS NULL e IS NOT NULL:

teste=# SELECT (1 IS NULL) AS COMPARA1, (1 IS NOT NULL) AS COMPARA2;
 compara1 | compara2
----------+----------
 f        | t
(1 registro)

* Saiba que valores nulos retornam nulo em comparações (IN, NOT IN, =, !=, etc.)

Esta é uma decorrência do padrão SQL.

teste=# SELECT null NOT IN (1,2,3), null IN (1,2,3);
 ?column? | ?column?
----------+----------
          |
(1 registro)

teste=# SELECT 1 NOT IN (null), 1 IN (null);
 ?column? | ?column?
----------+----------
          |
(1 registro)

teste=# SELECT 1 != null, 1 = null;
 ?column? | ?column?
----------+----------
          |
(1 registro)

* Criar tabelas que aceitem e rejeitem campos nulos é importante!

teste=# CREATE TABLE TSTNULL (campo1 INTEGER NOT NULL, campo2 INTEGER NULL, campo3 INTEGER);
CREATE TABLE

* Como manipular valores nulos e não nulos nas tabelas criadas?

Use o valor null para deixar o campo desejado sem valor algum. Caso se utilize DEFAULT, o campo assume valor nulo se não há um valor default definido.

teste=# INSERT INTO TSTNULL VALUES (1, null, DEFAULT); --O DEFAULT é NULO, CASO NÃO FORNECIDO
INSERT 0 1
teste=# INSERT INTO TSTNULL VALUES (null, 1, 1);
ERRO:  valor nulo na coluna "campo1" viola a restrição não-nula
 

teste=#
teste=# SELECT * FROM TSTNULL;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

* Entenda que campos UNIQUE aceitam vários valores nulos.

teste=# CREATE TABLE TSTNULL2 (campo1 INTEGER UNIQUE);
NOTA:  CREATE TABLE / UNIQUE criará índice implícito "tstnull2_campo1_key" na tabela "tstnull2"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL2 VALUES (1);
INSERT 0 1
teste=# INSERT INTO TSTNULL2 VALUES (NULL);
INSERT 0 1
teste=# INSERT INTO TSTNULL2 VALUES (NULL); --NAO GERA ERRO
INSERT 0 1
teste=#
teste=# SELECT * FROM TSTNULL2;
 campo1
--------
      1
      
      
(3 registros)

* Campos PRIMARY KEY não aceitam nulos por definição. Você sabia?

teste=# --CAMPOS PRIMARY KEY NÃO ACEITAM NULOS
teste=# CREATE TABLE TSTNULL3 (campo1 INTEGER PRIMARY KEY);
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "tstnull3_pkey" na tabela "tstnull3"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL3 VALUES (1);
INSERT 0 1
teste=# INSERT INTO TSTNULL3 VALUES (NULL); --ERRO
ERRO:  valor nulo na coluna "campo1" viola a restrição não-nula
teste=#  

* Campos PRIMARY KEY com mais de um campo também não aceitam nulos.

teste=# CREATE TABLE TSTNULL4 (campo1 INTEGER, campo2 INTEGER,
teste(# CONSTRAINT PK_TESTNULL4 PRIMARY KEY (campo1, campo2));
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "pk_testnull4" na tabela "tstnull4"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL4 VALUES (1, 1);
INSERT 0 1
teste=# INSERT INTO TSTNULL4 VALUES (1, NULL); --ERRO
ERRO:  valor nulo na coluna "campo2" viola a restrição não-nula
teste=#

* Consultas podem ordenar os valores nulos, colocando-os na frente ou atrás do resultado da consulta.

Para obter este efeito, empregue as cláusulas NULLS FIRST e NULLS LAST. O padrão é colocar os valores nulos por último no retorno da consulta.

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO2 NULLS FIRST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO2 NULLS LAST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO1 NULLS FIRST, CAMPO2 NULLS LAST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

* A indexação leva em conta campos nulos. Você sabia? 

Para influenciar  este mecanismo, utilize as cláusulas NULLS FIRST e NULLS LAST.

teste=# CREATE INDEX idx_campo1_first ON TSTNULL (campo1 NULLS FIRST);
CREATE INDEX

teste=# CREATE INDEX idx_campo1_last  ON TSTNULL (campo1 NULLS LAST);
CREATE INDEX

teste=# CREATE INDEX idx_campo1_2 ON TSTNULL (campo1 NULLS LAST, campo2 NULLS FIRST);
CREATE INDEX

* Troque os nulos por um valor padrão usando o comando COPY.

Tanto no COPY FROM quanto no COPY TO, você pode substituir os nulos por um valor mais palatável, utilizando a cláusula NULL. Não deixe de indicar o valor que substituirá os nulos.


teste=# COPY TSTNULL TO 'tstnulos.txt' NULL 'NULO' CSV;
teste=# COPY TSTNULL FROM 'tstnulos.txt' NULL 'NULO' CSV;

* É possível "Forçar" valores importados para valores not null, no comando COPY FROM de arquivos CSV. 

Basta utilizar a cláusula cláusula FORCE_NOT_NULL, a partir da versão 9.0 do postgresql.Esta cláusula transforma valores nulos em strings de tamanho zero.

teste=# COPY TSTNULL FROM 'tstnulos.txt' (format csv, FORCE_NOT_NULL(campo1)); -- VALORES NULOS DE CAMPO1 SÃO LIDOS COMO STRINGS DE TAMANHO ZERO, NÃO COMO NULOS

* Você pode tratar parâmetros nulos nas funções com as cláusulas "CALLED ON NULL INPUT", "RETURNS NULL ON NULL INPUT" ou "STRICT".

Quando desejar aceitar tratar parâmetros com valor nulo em uma função, utilize a cláusula "CALLED ON NULL INPUT". O postgresql não vai retornar erro ou fazer qualquer tratamento sobre o parâmetro, passando esta responsabilidade ao programador da função.

O uso de "RETURNS NULL ON NULL INPUT" ou "STRICT" faz com que o retorno de uma função com algum parâmetro nulo, seja sempre null.

O teste é bem simples. Abaixo, coloco alguns exemplos:

CREATE OR REPLACE FUNCTION soma_nulo_1(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql CALLED ON NULL INPUT;
SELECT soma_nulo_1(1,2);
SELECT soma_nulo_1(null,2);

CREATE OR REPLACE FUNCTION soma_nulo_2(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql RETURNS NULL ON NULL INPUT;
SELECT soma_nulo_2(1,2);
SELECT soma_nulo_2(null,2);

CREATE OR REPLACE FUNCTION soma_nulo_3(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql STRICT;
SELECT soma_nulo_3(1,2);
SELECT soma_nulo_3(null,2);

* Considerações Finais

Tratar valores nulos é de vital importância para evitar inconsistências. O postgresql oferece várias opções para lidar com este tipo de ocorrência.