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

 

PostgreSQL – Function – SQL (v2)

Background

A quick follow-up to our introduction post on writing functions in PostgreSQL.

Lineage

  1. PostgreSQL – Function – SQL
    Link

Code

Outline

  1. Here are the changes
    • Using “Type of ” Table name rather than having to replicate the table definition
    • Like it, because it reminds of same construct in Oracle
  2. Variable Name
    • Using same name for variable name and column/attribute name
    • Prefix variable name with function/procedure name

SQL


CREATE or replace FUNCTION func_customer_get_asTable
(
      country    varchar(15)
    , region     varchar(15)
    , city       varchar(15)
)
returns setof customers 

AS
$func$

    select
            tblC.customer_id

            , company_name
            , contact_name
            , contact_title
            , address
            , city
            , region
            , postal_code
            , country
            , phone
            , fax

    from    customers tblC

    where   tblC.country
                = coalesce
                    (
                          func_customer_get_asTable.country
                        , tblC.country
                    )

    and     tblC.region
                = coalesce
                    (
                          region
                        , tblC.region
                    )

    and     tblC.city
                = coalesce
                    (
                          city
                        , tblC.city
                    )

$func$ 

LANGUAGE sql 

stable

;

COMMENT ON FUNCTION func_customer_get_asTable IS
    'List of active customers - Criteria is country, region, city'
    ;

References

  1. PostgreSQL
    • Extending SQL
      • Query Language (SQL) Functions
        Link
    • PL/pgSQL – SQL Procedural Language
      • PL/pgSQL Under the Hood
        Link
  2. Stack Overflow
    • Database Administrators
      • Naming conflict between function parameter and result of JOIN with USING clause
        Link

PostgreSQL – Function – SQL

Background

Let us create a new Function in PostgreSQL.

What will the Function do ?

It will accept three arguments ( country, region, and city ).

Based on that criteria it will return customers from the Northwind database.

SQL

Code


CREATE or replace FUNCTION func_customer_get
(
      country_in varchar(15)
    , region_in  varchar(15)
    , city_in    varchar(15)
)
RETURNS 

table
    (
          id            bpchar(5)
        , company       varchar(40)
        , contact       varchar(30)
        , contactTitle  varchar(30)
        , address       varchar(60)
        , city          varchar(15)
        , region        varchar(15)
        , postalCode    varchar(10)
        , country       varchar(15)
        , phone         varchar(24)
        , fax           varchar(24)
    )
AS
$func$

    select
            tblC.customer_id

            , company_name
            , contact_name
            , contact_title
            , address
            , city
            , region
            , postal_code
            , country
            , phone
            , fax

    from    customers tblC

    where   tblC.country
                = coalesce(country_in, tblC.country)

    and     tblC.region
                = coalesce(region_in, tblC.region)

    and     tblC.city
                = coalesce(city_in, tblC.city)

$func$ 

LANGUAGE sql 

stable

;

Invoke

Invoke #1


SELECT *

from   public.func_customer_get
       (
            country_in := 'Brazil'
          , region_in  := 'SP'
          , city_in    := 'Sao Paulo'
       ) 

Output

invoke_Brazil_SP_SaoPaulo

Invoke #2


SELECT *

from   public.func_customer_get
       (
            country_in := 'Brazil'
          , region_in  := null
          , city_in    := null
       ) 

Output

invoke_Brazil

References

  1. Stack Overflow
    • Difference between language sql and language plpgsql in PostgreSQL functions
      Link