Thread: extract psql meta-commands into library?

I'd like to provide access to psql's "backslash" meta-commands for
IPython's %sql magic, as well as some other non-psql tools.  When I
asked about standalone implementations of the meta-commands, Craig
Ringer suggested that extracting them from psql into a libpsql library
(which psql would then use) would make that easy for all matter of
external uses.

Does this sound doable / of interest?

(Originally asked at
http://stackoverflow.com/questions/22211916/standalone-psql-meta-command-implementation)

ipython-sql: https://pypi.python.org/pypi/ipython-sql/0.3.1

Thanks!
--
- Catherine
http://catherinedevlin.blogspot.com

Catherine Devlin <catherine.devlin@gmail.com> writes:
> I'd like to provide access to psql's "backslash" meta-commands for
> IPython's %sql magic, as well as some other non-psql tools.  When I
> asked about standalone implementations of the meta-commands, Craig
> Ringer suggested that extracting them from psql into a libpsql library
> (which psql would then use) would make that easy for all matter of
> external uses.

> Does this sound doable / of interest?

Hm ... the code in psql's describe.c is not terribly conducive to that.
Parsing of the backslash command, execution of the query/queries, and
presentation of the results is all rather tightly bound up; you'd have
to think about how to decouple those.

If you could do it in a way that didn't result in a quantum jump in
the complexity/unreadability of the code, I think there'd be interest.

            regards, tom lane

On Thu, Mar 6, 2014 at 12:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hm ... the code in psql's describe.c is not terribly conducive to that.
> Parsing of the backslash command, execution of the query/queries, and
> presentation of the results is all rather tightly bound up; you'd have
> to think about how to decouple those.

I just looked into describe.c, but it was virtually my first look at C
in 15 years and I'm pretty intimidated.

Come to think of it, I really like the idea of moving the query
execution and presentation of results out into plpgsql functions in
template0; then it would be super-easy for applications to utilize
them.  Parsing the command could stay with psql, because the
applications would probably want to do that parsing themselves,
anyway.

The trouble is that psql is written to work against older databases,
so if the code were stripped from describe.c in favor of calling
stored functions, those commands would stop working against older
PostgreSQL installations; and if the code were left in describe.c,
then the same function would exist in two different forms in two
different places, with all the maintanability problems that implies.

Is there a way around this I'm not thinking of?

Alternately, I may just give up and write my own, freestanding,
approximate implementation of the meta-commands with no effort to
actually replace the native one.  It could still be useful to
application developers if they find out about it.

--
- Catherine
http://catherinedevlin.blogspot.com

Catherine Devlin <catherine.devlin@gmail.com> writes:
> I just looked into describe.c, but it was virtually my first look at C
> in 15 years and I'm pretty intimidated.

It's ugly and messy even to someone who does C every day, so don't feel bad.

> Come to think of it, I really like the idea of moving the query
> execution and presentation of results out into plpgsql functions in
> template0; then it would be super-easy for applications to utilize
> them.

That sort of thing has been discussed before, and rejected every time.
Pavel noted a couple of practical reasons against it, but the really big
picture is that there's not much agreement about exactly what views
applications might want to see, if they don't want to look directly at
the catalogs nor at the standardized information_schema views.

A new client-side library would have that issue too of course, but at
least it's relatively easy to ignore such a library if it doesn't do
exactly what you want.  Also, since such a library could offer support for
older server versions too, it would have a much better chance of getting
significant uptake in less than several years.

> Alternately, I may just give up and write my own, freestanding,
> approximate implementation of the meta-commands with no effort to
> actually replace the native one.  It could still be useful to
> application developers if they find out about it.

Yeah.  I'm not entirely convinced that you'd want to be bug-compatible
with the psql commands anyway.  There's a lot of details in there that
are legacy decisions and/or tuned for manual use rather than
programmatic use.

            regards, tom lane