Is there a way to know the names and types of columns a query returns?

[vonHabsi]

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.

[anse1]

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?

[vonHabsi]

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?

[anse1]

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.

[gnusupport]

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.

[anse1]

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?

[gnusupport]

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.

[vonHabsi]

Offtopic reply:

Do you have a link to some source code which creates this type of display?

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.

[gnusupport]

• 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.

[gnusupport]

• 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.

[vonHabsi]

• 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:

If you suceed to install those, I can then provide the functions for editing with the tabulated-list-mode

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?

[gnusupport]

• 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.

[vonHabsi]

• 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

Thanks.