Let's say I want to find a list of functions that can work on
jsonb, what is the most effective way to do this from the terminal without using google? Assuming only access to
- man pages
- postgresql tables
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There are a few ways, but let's try and address all three of them..
There are no
man pages that will address these questions. There is a lot of PostgreSQL documentation but currently there is nothing like that nor
perldoc for PostgreSQL. The docs covering this are only distributed as HTML -- which you can still view with a terminal browser (like elinks, lynx, w3m etc). Most distributions package this up separately, in Ubuntu you'd do this.
sudo apt-get install postgresql-doc elinks /usr/share/doc/postgres*/html/index.html
To use PSQL for this, you'll need to use
\dfS. You can see a listing of all the psql commands with
\?. After you do
\dfS just search for the type. In the event
\dfS is using a viewer that wraps on terminal width, you can tell it not to wrap by setting using `\setenv, most pagers accept arguments by their environmental variable so to use LESS without wrapping you can use
\setenv PAGER less \setenv LESS -S
All of this is buried in the
man page for
psql. Most of the output of
\? the in-shell psql help is better explained in that man page too, for instance this is what
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
And, this is what
man psql shows,
Lists functions, together with their arguments, return types, and function types, which are classified as “agg” (aggregate), “normal”, “trigger”, or “window”. To display only functions of specific type(s), add the corresponding letters a, n, t, or w to the command. If pattern is specified, only functions whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If the form \df+ is used, additional information about each function is shown, including security classification, volatility, owner, language, source code and description.
Tip To look up functions taking arguments or returning values of a specific type, use your pager's search capability to scroll through the \df output.
First you need to have an idea of what to query. That's the complex party. In the above, we know that we're using
\dfS, so we can get a starting point with
psql like this:
psql -E -c"\dfS" -d myDB | less -S
This will start off with with the query,
********* QUERY ********** SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE WHEN p.proisagg THEN 'agg' WHEN p.proiswindow THEN 'window' WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' ELSE 'normal' END as "Type" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.oid) ORDER BY 1, 2, 4; **************************
Now, we now we
p.proname as "Name", and
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types" and we can put that together to get a simplified version of
\dfS which shows just what you want,
SELECT p.proname AS "Name", pg_get_function_arguments(p.oid) AS "Argument data types" FROM pg_proc AS p WHERE pg_get_function_arguments(p.oid) like '%jsonb%';
A little more research into pg_proc and we can see
proargtypes - oidvector - pg_type.oid - An array with the data types of the function arguments. This includes only input arguments (including INOUT and VARIADIC arguments), and thus represents the call signature of the function.
This would allow us to do a very specific query to
SELECT proname AS "Name" FROM pg_proc INNER JOIN pg_type ON ( pg_type.oid = ANY(proargtypes) ) WHERE typname = 'jsonb';
And, that gets us exactly what we want.
While maybe not applicable here, generally speaking you can find some information in the
information_schema. This generic interface doesn't support everything, it's slow, and psql doesn't use it internally. I did not see this information in
information_schema.routines at all.
Some of this answer was shamelessly inspired from this question but aimed to be a more friendly resource.
I want to find a list of functions that can work on
Your answer so far only finds:
jsonbparameters in the function signature.
A good start, but only a subset of what you are asking for.
Your query can be simplified and improved using object identifier types:
SELECT oid::regprocedure AS func_with_params, prorettype::regtype AS return_type FROM pg_proc WHERE 'jsonb'::regtype = ANY(proargtypes);
Understanding this query is the minimum requirement to start looking at the next:
WITH RECURSIVE cte AS ( SELECT 'jsonb'::regtype AS my_type UNION -- fold dupes SELECT a.attrelid::regclass::text::regtype FROM cte t JOIN pg_attribute a ON a.atttypid = t.my_type ) SELECT p.oid::regprocedure AS func, prorettype::regtype AS return_type FROM pg_proc p WHERE EXISTS ( SELECT 1 FROM cte WHERE my_type = ANY (proargtypes) OR my_type = prorettype ) ORDER BY 1;
This includes more functions that can work on
To test nested types:
CREATE TEMP TABLE j2(t text, j jsonb); CREATE TEMP TABLE j22(j j2); CREATE TEMP TABLE j222(j j22); CREATE FUNCTION pg_temp.f_j() RETURNS jsonb AS 'SELECT NULL::jsonb' LANGUAGE sql; CREATE FUNCTION pg_temp.f_j2(j2) RETURNS bool AS 'SELECT true' LANGUAGE sql; CREATE FUNCTION pg_temp.f_j222(j222) RETURNS bool AS 'SELECT true' LANGUAGE sql;
And run the above query again.
And we haven't even talked about these, yet:
Polymorphic functions (can be called with various data types).
Functions working with
jsonb in the body but neither take nor return
jsonb. For example, PL/pgSQL function do not even store a dependency in the system table because the function body is stored a string and parsed and planned at the first call in the session ...
Implicit or assignment casts and function type resolution.
The data type in question may very well be valid input to other functions after an implicit type cast.
Domains, typed tables, maybe other exotic features.