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
ormysqli_fetch_field_direct
functions can give the following values for each field in the query
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" :
The good thing aboutmysqli_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 ofmysqli_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 whilemysqli_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.
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 thatfetchAll
incurs the usual memory penalty, as a possibly large result is loaded into your process. You should only usefetchAll
if the result set is known to always be small. Otherwise, usefetch
orfetchColumn
in a loop and page the query withLIMIT 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.
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.
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL.
It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.