koshachok koshachok - 3 months ago 10
SQL Question

Request from Firebird to PostgreSQL

I have a Firebird SQL 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.