mysql - PHP - mysqli_fetch_field equivalent for PostgreSQL

Question

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.

question from:https://stackoverflow.com/questions/65560150/php-mysqli-fetch-field-equivalent-for-postgresql

Answer 1

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.