Is there a way to know the names and types of columns a query returns? #16
Comments
|
libpq itself has a function PQfname to do that, emacs-libpq doesn't
expose it though. It can't be added trivially either since emacs-libpq
doesn't expose the PGresult pointer to the lisp world at all.
…--8<---------------cut here---------------start------------->8---
'PQfname' PQfname
Returns the column name associated with the given column number.
Column numbers start at 0. The caller should not free the result
directly. It will be freed when the associated 'PGresult' handle
is passed to 'PQclear'.
char *PQfname(const PGresult *res,
int column_number);
--8<---------------cut here---------------end--------------->8---
Not being able to get diagnostics about a result such as the names of
the columns is one of the drawbacks of this design descision. I didn't
think much of dismissing this functionality since I hardly need it. You
can get the column names for tables from information_schema.columns fine
and best practice for queries is to name the columns in the query
instead of using "select *". But maybe you have an interesting use case
where this diagnostic info is crucial?
|
|
I am not familiar with working with Postgres at the libpq level and I want to know if those details could be accessed so I know what data types to associate with the columns of the result set. I know of a for finding names and types of a query by creating a query which returns 0 results and using the information_schema to work them out, so I will resort to that. Do you have a list of the libpq functions implemented? |
|
Frank Church writes:
I am not familiar with working with Postgres at the libpq level and I
want to know if those details could be accessed so I know what data
types to associate with the columns of the result set.
emacs-libpq doesn't expose that information.
I know of a for finding names and types of a query by creating a query
which returns 0 results and using the information_schema to work them
out, so I will resort to that.
Do you have a list of the libpq functions implemented?
There's no direct mapping functions in libpq and emacs-libpq functions
since it does a bit of abstraction. The following functions are
available in emacs-libpq:
pq:connectdb
Function: Connect to a PostgreSQL database described by CONNINFO.
pq:escapeIdentifier
Function: Perform identifier value quoting on STRING for CONN.
pq:escapeLiteral
Function: Perform literal value quoting on STRING for CONN.
pq:notifies
Function: Get asynchronous notifications recieved on CONN.
pq:query
Function: Execute COMMAND on CONN with optional PARAMETERS.
pq:reset
Function: Resets the communication channel to the server behind
CONN.
See their docstrings for more details about them. Since the testsuite
test.el uses all of them it has lots examples which may help/clarify
their use.
|
|
I am building tabulated-list-mode based database management, and my main problem is that designation of the column names, and number of columns. psql interface provide that, and it is great. But I would not know how to do it in Emacs. It would be great to be able to receive a list of column names or their comments, as that way I would get also the number and reporting would become way easier. I have to designed from my program names of columns instead of fetching it from PostgreSQL, like SQL does it. I will send US $20 when and if this function is implemented that we can get list of column names. Those empty column names could be designated as nil, why not. |
|
It really isn't easy to fit this with the current design.
Also, adding type awareness like vonHabsi requested is a bathtub-sized
can of worms. Postgres has an extensible type system with inheritance,
composite types, arrays of all types and I have my doubts that it would
help you with anything if emacs-libpq would tell you that there's a
column named "sequence" of type DNA. You have to get expert knowledge
from somewhere doing anything sensible with that type and the schema
you're dealing with and as soon as you have that expert knowledge you
already know what the types and columns of your queries would be in the
first place...
The most elegant - but still inelegant - way I could think of is adding
a adding a function - e.g. pq:diags - that is exactly the same as
pq:query but instead of returning data it returns some shallow metainfo.
E.g.
…--8<---------------cut here---------------start------------->8---
(pq:query nnpq-con "select 1 col, text 'foo' another_col, inet '::1' meh")
([1 "foo" "::1"])
(pq:diags nnpq-con "select 1 col, text 'foo' another_col, inet '::1' meh")
(("col" . "integer")
("another_col" . "text")
("meh" . "inet"))
--8<---------------cut here---------------end--------------->8---
Of course the schema could have changed between these two calls and it
gets messy/unhelpful as soon as there are composite data types but
that's the best I can think of atm.
Opinions?
|
|
Of course the schema could have changed between these two calls and it
gets messy/unhelpful as soon as there are composite data types but
that's the best I can think of atm.
Opinions?
I would like to retract my demand or wish as definitely is such
feature something very specific to Emacs, it would just create
dependencies on non-standard methods.
|
|
Offtopic reply: Do you have a link to some source code which creates this type of display?
|
|
* Frank Church ***@***.***> [2021-05-13 01:13]:
Offtopic reply:
Do you have a link to some source code which creates this type of display?
Programs will be GNU GPL licensed, they are not yet ready, it need
some refactoring.
I could isolate those few functions and make separate universal
package, I am working on it.
|
|
* Frank Church ***@***.***> [2021-05-13 01:13]:
Do you have a link to some source code which creates this type of display?
Here is the RCD Database Basics:
https://hyperscope.link/3/7/4/9/3/RCD-Database-Basics-37493.html
It requires RCD Utilities:
GNU Emacs package: rcd-utilities.el :
https://gnu.support/gnu-emacs/packages/rcd-utilities-el.html
If you suceed to install those, I can then provide the functions for
editing with the tabulated-list-mode
All the database design is based on the fact that there must be
TABLENAME_id column as that is how fetching, updating, etc. becomes
easy.
|
I have installed the above packages and I'm looking forward to the functions. When you say:
does that mean that the above 2 utilities already provide the means to view the output in tabulated-list-mode? Can we continue this discussion in another forum to avoid this issue thread going off-topic? |
|
* Frank Church ***@***.***> [2021-05-19 16:13]:
I have installed the above packages and I'm looking forward to the
functions.
I have prepared a review for you, without all tables, you can install
and see functionality:
https://hyperscope.link/3/7/1/5/5/RCD-Notes-for-Emacs-37155.html#_where_is_the_source
> If you suceed to install those, I can then provide the functions for
**editing** with the tabulated-list-mode
Yes, it is there.
However, this database management is based on principles I have
learned from GeDaFe:
GeDaFe - PostgreSQL Generic Database Interface
http://gedafe.github.io/doc/gedafe-sql.en.html
There are some basics to follow with design of tables, they have to be
designed with TABLE_id being SERIAL NOT NULL PRIMARY KEY, and all
columns should be named TABLE_COLUMN. In order to use the table as a
foreign key, for example to select account types when editing entry in
the table accounts, each table should have its TABLE_combo view which
is easily constructed. There is skeleton cf-sql-table that helps in
that.
does that mean that the above 2 utilities already provide the means
to **view** the output in tabulated-list-mode?
I would not know which utilities you mean.
If you wish to list SQL in tabulated list mode, you do it as
following:
(defun cf-people-by-mark-new ()
"Lists people marked by MARK"
(interactive)
(let* ((mark (cf-marks-choice)) ;; it just selects one mark
(sql (format "SELECT markassignments_contact, get_full_contacts_name(markassignments_contact) FROM markassignments WHERE markassignments_mark = %s AND markassignments_contact IS NOT NULL" mark))
(prompt "People by mark"))
(rcd-db-sql-report prompt sql [("ID" 7 t) ("Full name" 50 t)] "people" nil 'cf-people-by-mark-new)))
The `rcd-db-sql-report` is then using SQL and `tabulated-list-format`
to construct a view.
Can we continue this discussion in another forum to avoid this issue
thread going off-topic?
You can use Emacs devel mailing list or gnu-help-emacs, which is best
place.
|
Thanks. |
Is there a way to know the names and types of columns a query returns?
I'm not acquainted with using libpq at such a low level and I wonder if there is an API for it.
The text was updated successfully, but these errors were encountered: