Try a preview of AI-powered search below!

Querying "pg_proc" explicitly

The pg_proc section in the PostgreSQL documentation, within the System Catalogs enclosing chapter, describes the dedicated catalog table for subprogram metadata. It's a wide table with a column for every single fact that characterizes functions and procedures.

Create a test function and procedure thus:

drop schema if exists s1 cascade;
drop schema if exists s2 cascade;
create schema s1;
create schema s2;

create procedure s1.p(i in int)
  security definer
  set timezone = 'America/New_York'
  language plpgsql
as $body$
begin
  execute format('set my_namespace.x = %L', i::text);
end;
$body$;

create function s2.f(i in int)
  returns text
  security invoker
  immutable
  set statement_timeout = 1
  language plpgsql
as $body$
begin
  return 'Result: '||(i*2)::text;
end;
$body$;

Now try this example query. It assumes that you connected as the role u1 so that it can restrict the query by that to reduce noise. Change it to suit your test and to select the attributes that interest you.

with subprograms(
  schema,
  name,
  type,
  security,
  volatility,
  settings)
as (
  select
    pronamespace::regnamespace::text,
    proname::text,
    case prokind
      when 'p' then 'procedure'
      when 'f' then 'function'
      end,
    case
      when prosecdef then 'definer'
      else 'invoker'
    end,
    case
      when provolatile = 'v' then 'volatile'
      when provolatile = 's' then 'stable'
      when provolatile = 'i' then 'immutable'
    end,
    proconfig
  from pg_proc
  where proowner::regrole::text = 'u1')
select *
from subprograms
order by
  schema,
  name;

The result will include at least these rows (depending on your history):

 schema | name |   type    | security | volatility |          settings
--------+------+-----------+----------+------------+-----------------------------
 s1     | p    | procedure | definer  | volatile   | {TimeZone=America/New_York}
 s2     | f    | function  | invoker  | immutable  | {statement_timeout=1}

42P13: invalid attribute in procedure definition

The '\df' and '\sf' meta-commands

YSQL's ysqlsh command line interpreter is derived directly from the source code of PostgreSQL's psql and it therefore supports the same set of meta-commands.

Unlike with, say, the \echo meta-command, you can direct the output from \df and \sf to a file with the \o meta-command.

'\df'

The \df meta-command, when you use it bare, lists pre-determined metadata for every subprogram in the database. This is rarely useful because the number of results is typically large and often uninteresting. You can restrict the results. But you don't use SQL syntax. The rules are explained in the ysqlsh section in the dedicated subsection for \df. This example lists the subprograms in the schema s2:

\df s2.*

This is result (following what you created using the code above):

 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
 s2     | f    | text             | i integer           | func

'\sf'

This produces a canonically formatted create or replace statement for the nominated subprogram. The optional + qualifier shows the line numbers. This can be useful for debugging runtime errors.

The \sf meta-command takes a single argument: the (optionally) fully qualified subprogram_name and its signature. (Its subprogram_call_signature is sufficient—and it's conventional always to use just this.)

Try this example. Notice that the function bad(int) is contrived to cause a runtime error. Notice, too, that the various attributes besides the source text have been written in an order (following the source text) that, while it is legal, is unusual.

drop function if exists s1.bad(numeric);
create function s1.bad(i in numeric)
  returns numeric
as $body$
declare
  one constant numeric not null := 1.0;
  r numeric not null := 0;
begin
  -- Designed to fail at runtime when invoked with zero.
  r := one/i;
  return r;
end;
$body$
set timezone = 'America/Los_Angeles'
security definer
language plpgsql;

\sf+ s1.bad(numeric)

This is the \sf output:

        CREATE OR REPLACE FUNCTION s1.bad(i numeric)
         RETURNS numeric
         LANGUAGE plpgsql
         SECURITY DEFINER
         SET "TimeZone" TO 'America/Los_Angeles'
1       AS $function$
2       declare
3         one constant numeric not null := 1.0;
4         r numeric not null := 0;
5       begin
6         -- Designed to fail at runtime when invoked with zero.
7         r := one/i;
8         return r;
9       end;
10      $function$

The preamble (up to and including the line that has been numbered 1) has been canonicalized thus:

• Keywords are rendered in upper case.
• The source text follows the preamble as the final item in the generated text.
• The ordering of the non-source-text attributes is system-generated and different from the order in the create statement that defined the subprogram.
• The syntax of the set timezone attribute has been canonicalized.
• The $function$ dollar-quote has been used to surround the source text. (Correspondingly, $procedure$ is used around a procedure's source text.)

You can confirm with an ad hoc test that if you had already used $function$ within the statement that created the function that you process with \sf, then the generated double quote would be changed to, say, $function1$. (The corresponding accommodation is made for procedures.)

You might take this as a gentle hint always to place the source text as the very last item—but not to care about the mutual ordering of the other attributes.

Now invoke bad() to cause the planned runtime error:

select s1.bad(0);

This is how the error is reported:

ERROR:  division by zero
CONTEXT:  PL/pgSQL function s1.bad(numeric) line 7 at assignment

When the source text is long, it can often be hard to identify the erroring line (in a .sql script with other statements than the one that creates the erroring subprogram) where the runtime error occurred.