I have a
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
RDB$RELATIONS doesn't exist.
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
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.