mysql - PHP - mysqli_fetch_field equivalent for PostgreSQL
I'm trying to move to my project to PostgreSQL from MySQL. For MySQL, PHP offers good functionality to fetch SELECT query metadata.
mysqli_fetch_field
or mysqli_fetch_field_direct
functions can give the following values for each field in the query
- name
- orgname
- table
- orgtable
- max_length
- length
- type
- (and a few others I'm not interested in currently)
For instance, an example query and metadata for the field "id" frm the query will look like this:
Query:
SELECT id as id2 FROM table1 t1
Metadata for field "id" :
- name: id2
- orgname : id
- table : t1
- orgtable : table1
- length: 765
- type: 253
The good thing about mysqli_fetch_field
is that it is capable of returning both alias and original name of the fields and tables in the query.
When I try to imitate this functionality for PostgreSQL, I observe that there is no equivalent of mysqli_fetch_field
function for PostgreSQL, instead, there are several pg_field_* functions each of which give a single information for a field.
pg_field_name
returns name only and for this example it gives "id2"pg_field_table
returns original table name, e.g. "table1"pg_field_type
returns the type (no problem here)pg_field_size
returns the length of the field (it is also OK)
Obviously, pg_field_* functions do not offer the programmer original names of the tables/fields if they are renamed in the query while mysqli_fetch_field
does.
Is there a way that I can fetch "detailed" metadata for a query in PostgreSQL? It can be using an additional query or a tool that the DBMS offers like EXPLAIN and DESCRIBE (I tried these too) or anything else.
Answer
Solution:
Are you invested into the Postgres specific function API? If not, check out PDO, which provides you one common interface to your database, no matter if it is MySQL or Postgres. It also provides you a way to fetch specific columns:
$pdo = new PDO(...);
$statement = $pdo->prepare("SELECT foo FROM bar WHERE baz = ?");
$statement->execute([42]);
$foo = $statement->fetchColumn(0);
// $foo is the column value or false
You can also fetch column values of all result rows:
$foos = $statement->fetchAll(\PDO::FETCH_COLUMN, 0);
// $foos is an array, possibly empty
Note that fetchAll
incurs the usual memory penalty, as a possibly large result is loaded into your process. You should only use fetchAll
if the result set is known to always be small. Otherwise, use fetch
or fetchColumn
in a loop and page the query with LIMIT n, m
clauses.
Fetching a specific column is resource friendly if only one column is queried. When you have other columns in your query, they would still be fetched into your process, just to be thrown away. You should make sure to not include any unnecessary columns.
For column metadata, use getColumnMeta:
$meta = $statement->getColumnMeta(0);
// $meta is false or array{native_type, driver:decl_type, flags, name, table, len, precision, pdo_type}
To get more detailed metadata, you should query into information_schema.columns. Best to do it before the data query, because the data query might be executed in a loop. Metadata need to be fetched only once beforehand.
Answer
Solution:
After some research, I am eventually convinced that neither PHP Postgres API nor PostreSQL itself provides such functionality. Therefore, I could manage to solve the problem by a combination of pg_field_* functions and an SQL Parser written in PHP, namely PHP-SQL-Parser
Even though the SQL parser is written MySQL in mind, the default functionality worked for my use case and it is extensible in any case.
Source