Search code examples
sqlpostgresqlfirebirdcode-translation

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.

Solution

  • 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. You can inspect views definition though, just to get you started on the tables and conditions used to form an output.