PostgreSQL :- Function/Metadata

Background

Now that we have a couple of functions written, let us see how to review metadata on functions.

Code

SQL


select 

         tblNS.nspname

       , tblP.proname

       , tblLang.lanname
            as "language"

        , tblAuth.rolname
            as "owner"

        , tblP.prokind

        , tblType.typname
            as "returnType"

        , tblP.proparallel

        , tblP.provolatile

       , tblP.oid::regprocedure
            as "syntax"

        , tblP.proargnames

        , tblP.prosrc

from pg_catalog.pg_proc tblP

inner join pg_catalog.pg_namespace tblNS

    on tblP.pronamespace = tblNS.oid

join pg_catalog.pg_authid tblAuth

    on tblP.proowner = tblAuth.oid

join pg_catalog.pg_language tblLang

    on tblP.prolang = tblLang.oid

join pg_catalog.pg_type tblType

    on tblP.prorettype = tblType.oid

where tblNS.nspname not in

        (
              'information_schema'
            , 'pg_catalog'
        )

Output

pg_catalog.pg_proc.20190813.0651AM

GUI

DBeaver

Outline

To review function properties, please do the following :-

  1. Launch DBeaver
  2. Connect to PostgreSQL Instance
  3. Navigate to Schema \ {Schema} \ Functions \ {Function}
    • Function Parameters
    • Navigate on Selection
    • From the drop down menu, please choose “Function Parameters”

Image

Image – Function Parameters

func_parameters_20190813_0655AM.PNG

Image – Function Dependencies

func_dependency_20190813_0655AM.PNG

Image – Function Properties/Statistics/Flags

func_properties_20190813_0657AM.PNG

Image – Function – Permissions

func_permissions_20190813_0658AM.PNG

Image – Function – Source

func_source_20190813_0659AM.PNG