psql: Add \dL to show languages - Mailing list pgsql-hackers

Hi all,

I'd like to revive Fernando Ike's patch implementing the "\dL" command
for psql to list available languages, last version here:
  http://archives.postgresql.org/pgsql-hackers/2009-07/msg01092.php

The original patch produced columns "Name", "Owner", "Procedural
Language", "Trusted", "Call Handler", and "Validator". I propose
simplifying the non-verbose output of \dL to look like this:

Name                 | Owner | Trusted
---------------------+-------+---------
 plperl              | josh  | t
 plpgsql             | josh  | t
 plpythonu           | josh  | f
(3 rows)

since the rest of the columns in the original patch seem like they
would be distracting noise the majority of the time[2]. I've kept most
of the original columns in the verbose output.

Tom Lane and Peter Eisentraut gave feedback on the original patch. I
think these concerns raised by Peter should now be addressed:

> 1) This is obviously wrong:
>
> CASE WHEN l.lanispl = 't' THEN 'Trusted' WHEN l.lanispl = 'f' THEN 'Untrusted' END

I ripped out this "Procedural Language" column[1].

> 2) It may be better to use lanispl to determine whether a language is a system
> object or not.  It's kind of obscure, but pg_dump does it that way, so it'd at
> least be consistent.

I added a "System Object" column in the verbose output with this information.

> 3) Your code does processSQLNamePattern(), but neither the help nor the
> documentation mention that \dL accepts a pattern.  A pattern for listing
> languages might be overkill, but at least the documentation needs to match
> what the code attempts to do.

I added a note to the psql-ref.sgml documentation that \dL accepts a
pattern. I agree it's probably overkill to support pattern matching
when most folks will have maybe 1-3 additional languages installed,
but it's easy enough to add in, and similar psql functions support
patterns as well.

> 4) Instead of LEFT JOIN pg_catalog.pg_proc p on l.lanplcallfoid = p.oid etc,
> just cast the oid field to regprocedure.  See examples elsewhere in
> describe.c.

Done, though I didn't see anything else in describe.c using casts to
regprocedure. Maybe there's a better way?

I've also fixed the tab-completion for \dL's pattern input. I haven't
yet test backwards compatibility with older server versions, though it
looks like this patch should work fine by not querying for "lanowner"
on 8.2 and earlier; I didn't see any other columns missing in
pg_language back to at least 8.1.

Josh

--
[1] I'm not sure what Fernando intended the original "Procedural
Language" column to be, but that column displayed "Trusted" or
"Untrusted" in addition to the "Trusted" column. Maybe this was a typo
in the patch? In any event, I don't think it's useful to have a
separate "Name" and "Procedural Language" column. If we did want to
include a Procedural Language column in addition to the Name, I'm not
sure offhand where to get this information, e.g. how to get the string
"PL/pgSQL" given pg_language.lanname = 'plpgsql'

[2]  For example, the command "droplang --list" only prints out "Name"
and "Trusted?" columns.