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

One thought on “PostgreSQL – Function – SQL

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s