koshachok koshachok - 3 months ago 8
SQL Question

Translating query from Firebird to 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


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.

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


it writes that:


RDB$RELATIONS doesn't exist.

Answer

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

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.

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

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.

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


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.