将查询从Firebird转换为PostgreSQL [英] Translating query from Firebird to PostgreSQL

问题描述

我有一个Firebird查询，应该将其重写为PostgreSQL代码。

I have a Firebird query which I should rewrite into PostgreSQL code.

SELECT TRIM(RL.RDB$RELATION_NAME), TRIM(FR.RDB$FIELD_NAME), FS.RDB$FIELD_TYPE
FROM RDB$RELATIONS RL 
    LEFT OUTER JOIN RDB$RELATION_FIELDS FR ON FR.RDB$RELATION_NAME = RL.RDB$RELATION_NAME 
    LEFT OUTER JOIN RDB$FIELDS FS ON FS.RDB$FIELD_NAME = FR.RDB$FIELD_SOURCE 
WHERE (RL.RDB$VIEW_BLR IS NULL) 
ORDER BY RL.RDB$RELATION_NAME, FR.RDB$FIELD_NAME

我了解SQL，但不知道如何使用<$ c $这样的系统表c> RDB $ RELATIONS 等。如果有人帮助我做到这一点真的很棒，但是即使与此表相关的某些链接也可以。

I understand SQL, but have no idea, how to work with this system tables like RDB$RELATIONS etc. It would be really great if someone helped me with this, but even some links with this tables explanation will be OK.

这条查询是用C ++代码编写的，当我尝试执行此操作时：

This piece of query is in C++ code, and when I'm trying to do this :

pqxx::connection conn(serverAddress.str());
pqxx::work trans(conn);
pqxx::result res(trans.exec(/*there is this SQL query*/));//and there is a mistake

它写道：

RDB $ RELATIONS不存在。

RDB$RELATIONS doesn't exist.

推荐答案

Postgres提供了另一种存储系统内容信息的方法。这称为系统目录。

Postgres has another way of storing information about system content. This is called System Catalogs.

在Firebird中，您的查询基本上为每个架构中的表的每一列返回一行，并附加一个映射到字段数据类型的Integer列。

In Firebird your query basically returns a row for every column of a table in every schema with an additional Integer column that maps to a field datatype.

在使用 pg_catalog 模式的系统表的Postgres中，可以使用以下查询实现类似的操作：

In Postgres using system tables in pg_catalog schema something similar can be achieved using this query:

SELECT 
  TRIM(c.relname) AS table_name, TRIM(a.attname) AS column_name, a.atttypid AS field_type
FROM pg_class c
LEFT JOIN pg_attribute a ON 
  c.oid = a.attrelid
  AND a.attnum > 0 -- only ordinary columns, without system ones
WHERE c.relkind = 'r' -- only tables
ORDER BY 1,2

上面的查询也返回系统目录。如果要排除，则需要向 pg_namespace 添加另一个JOIN，并向其中添加 pg_namespace.nspname的where子句。 <> 'pg_catalog'，因为这是存储系统目录的架构。

Above query does return system catalogs as well. If you'd like to exclude them you need to add another JOIN to pg_namespace and a where clause with pg_namespace.nspname <> 'pg_catalog', because this is the schema where system catalogs are stored.

如果您还想查看数据类型名称而不是它们的名称代表编号在 pg_type 中添加一个JOIN。

If you'd also like to see datatype names instead of their representative numbers add a JOIN to pg_type.

信息架构由视图集合组成。在大多数情况下，您不需要位于视图后面的整个SQL查询，因此使用系统表将为您提供更好的性能。不过，您可以检查视图定义，只是为了开始使用用于形成输出的表和条件。

Information schema consists of collection of views. In most cases you don't need the entire SQL query that stands behind the view, so using system tables will give you better performance. You can inspect views definition though, just to get you started on the tables and conditions used to form an output.

这篇关于将查询从Firebird转换为PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！