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
pqxx::result res(trans.exec(/*there is this SQL query*/));//and there is a mistake
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