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

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