Appendix F. Additional Supplied Modules

F.15. fuzzystrmatch

F.15.1. Soundex

The Soundex system is a method of matching similar-sounding names by converting them to the same code. (Soundex is not very useful for non-English names). The soundex function converts a string to its Soundex code. The difference function converts two strings to their Soundex codes and then reports the number of matching code positions. Since Soundex codes have four characters, the result ranges from zero to four, with zero being no match and four being an exact match. (Thus, the function is misnamed — similarity would have been a better name.)

SELECT soundex('Anne'), soundex('Ann'), difference('Anne', 'Ann');
     
SELECT soundex('Anne'), soundex('Andrew'), difference('Anne', 'Andrew');
SELECT soundex('Anne'), soundex('Margaret'), difference('Anne', 'Margaret');
    

F.15.3. Metaphone

Metaphone & Double Metaphone are algorithms that produce variable length keys for indexing words by their sound. definitely better compared to SOUNDEX because it is more precise compared to the fixed 4 character code of SOUNDEX. 

Metaphone, like Soundex, is based on the idea of constructing a representative code for an input string. Two strings are then deemed similar if they have the same codes.(source has to be a non-null string with a maximum of 255 characters)

SELECT metaphone('GUMBO', 4);
 metaphone
-----------
 KM

F.15.4. Double Metaphone
The Double Metaphone system computes two "sounds like" strings for a given input string — a "primary" and an "alternate". In most cases they are the same, but for non-English names especially they can be a bit different, depending on pronunciation. These functions compute the primary and alternate codes:

select dmetaphone('gumbo');
 dmetaphone
------------
 KMP

http://www.postgresql.org/docs/devel/static/fuzzystrmatch.html

Pass Cursor data through a function

ParentFunction - pass cursor data through function

CREATE OR REPLACE FUNCTION process_original_matched_records()

  RETURNS void AS

$BODY$

DECLARE

    datarec to_process_struct%ROWTYPE;

    datarec1 to_process_struct;

BEGIN

    perform select_records_to_process();

    for datarec in select * from to_process where original_check=1 and puin is not NULL and update_status is NULL LOOP

--RAISE NOTICE 'in process_orginal_matched_records before upsert % ', datarec.puin;

        datarec1 := datarec;

        --RAISE NOTICE 'in process_orginal_matched_records after datarec1=datarec % ', datarec.puin;

        PERFORM upsert_all_tables(datarec, cast(1 as smallint), datarec.puin, cast(1 as smallint));      

    END LOOP;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE;

Child Function - use cursor date from parent table

CREATE OR REPLACE FUNCTION upsert_all_tables(datarec to_process_struct, i_update_status smallint, puin_to_use character, i_statusid smallint)

  RETURNS void AS

$BODY$

DECLARE

   voicephone text; orig_statusid smallint;  update_statusid smallint;verified smallint;

BEGIN

insert into pre_update_data

select ...... m.prim_specialityid, m.sub_specialityid,m.hp_id,c.panelid,now() as update_date, datarec.studyid, datarec.sam_no, i.biz1,i.biz2,i.biz3,i.biz4,i.biz5,i.biz6

from contact c inner join address a on c.panelid=a.panelid left outer join combined_phones cp on cp.panelid=c.panelid

left outer join medical_extra m on m.panelid=c.panelid left outer join it_demogs i on i.panelid=c.panelid

where c.panelid=puin_to_use;

        if datarec.stat_type in ('WN','DS','FX') THEN voicephone := NULL;

        ELSE

            voicephone := datarec.voicephone;

        END IF;

        if datarec.studyid in (select studyid from study inner join survey on study.surveyid=survey.surveyid

inner join project on project.projectid=survey.projectid where project.projectid=424) THEN

if datarec.stat_type='CP' THEN

verified := 1;

ELSE

verified := 0;

END IF;

END IF;

        PERFORM upsert_phone_or_email(puin_to_use, datarec.fax, 5, 'phone');

        PERFORM upsert_phone_or_email(puin_to_use, datarec.deptphone, 6, 'phone');

        

select statusid from contact into orig_statusid where panelid=puin_to_use;

IF FOUND THEN

select assign_statusid(datarec.stat_type, orig_statusid) into update_statusid;

ELSE

update_statusid := i_statusid;

END IF;

PERFORM * from webinc_track where puin=puin_to_use;

IF FOUND THEN

RAISE NOTICE 'Here is a puin in upsert_all_tables before block 1 %', datarec.puin;

PERFORM upsert_contact_details(puin_to_use, datarec.salutation, datarec.forename, datarec.surname, datarec.title, cast(datarec.pxtype as integer), orig_statusid, verified);

RAISE NOTICE 'Here is a puin in upsert_all_tables after block 1 %', datarec.puin;

END IF;

        PERFORM upsert_address_details(puin_to_use, datarec.company, datarec.street, datarec.city, datarec.state, datarec.zipcode, cast(datarec.ccode as smallint));

        END IF;       

        if datarec.pxtype = 1 THEN

            PERFORM upsert_it_demogs(

            puin_to_use, 

            cast(datarec.biz1 as integer),           

        END IF;

        PERFORM upsert_contact_statistics(puin_to_use, datarec.stat_type);

        update interviewed_sample set update_status=i_update_status, update_date=current_date, panelmatchpuin=puin_to_use where studyid=datarec.studyid and sam_no=datarec.sam_no;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

Useful PostgreSQL Commands

Update statement joining tables
update deduction set sum=sum+1 from employee e
where e.id=deduction.employeeid and e.groupid=2;

update hall_sample h set panel_id = s.panelid 
    from (select lower(trim(address)) as email, e.panelid from email e 
    inner join (select panelid from contact where statusid = 1 and typeid = 2) AS c 
    on e.panelid = c.panelid) AS s where lower(trim(h.email)) = lower(trim(s.email))

Find duplicates when more than one unique values
select s.studyid, s.sam_no, count(*) from table_name i 
  inner join table_name s on (i.studyid = s.studyid AND i.sam_no = s.sam_no) 
  group by 1,2 having count(*) > 1

SELECT key, count(*) FROM ibmcvar.disposition GROUP BY 1 HAVING count(*) > 1; 

SELECT * FROM ibmcvar.disposition where key in (select key from (SELECT key, count(*) 
    FROM ibmcvar.disposition 
    GROUP BY 1 HAVING count(*) > 1) as t) order by key

Delete one from duplicate rows (minimum fatiguedate)
delete from ibmcvar_2012.disposition where (key, fatiguedate) in (

select key, min(fatiguedate) from ibmcvar_2012.disposition where key in
(select key from (select key, count(*)  from ibmcvar_2012.disposition
group by 1 having count(*) > 1 ) as x ) 
group by 1 order by 2 )

Find records with latest update value
SELECT * FROM table a JOIN (SELECT ID, max(date) maxDate
   FROM table GROUP BY ID) b ON a.ID = b.ID AND a.date = b.maxDate

Add sequence to existing column in existing table
First set the maximum number of the field as current value in sequence'

ALTER TABLE ccadmin.tblemployees ALTER COLUMN recordid SET DEFAULT nextval('ccadmin.tblemployees_sec'::regclass)


Select last 5 values for 2 columns as fields for selected column value(person, project)

select puin, 
max(case when (row_number = 1) then job_number else null end )  as jobnumber1,
max(case when (row_number = 1) then web_stat_type else null end )  as stat_type1,
max(case when (row_number = 2) then job_number else null end )  as jobnumber2,
max(case when (row_number = 2) then web_stat_type else null end )  as stat_type2,
max(case when (row_number = 3) then job_number else null end )  as jobnumber3,
max(case when (row_number = 3) then web_stat_type else null end )  as stat_type3,
max(case when (row_number = 4) then job_number else null end )  as jobnumber4,
max(case when (row_number = 4) then web_stat_type else null end )  as stat_type4,
max(case when (row_number = 5) then job_number else null end )  as jobnumber5,
max(case when (row_number = 5) then web_stat_type else null end )  as stat_type5
from ( WITH summary AS (
select puin, called_or_mailed_date, job_number, stat_type as web_stat_type, 
row_number() over (PARTITION BY puin order by called_or_mailed_date desc)
from reports.add_fields2_samples
where puin in ('C3CE780B-0E3F-4A0E-BA3A', 'EF9ADA63-2D5F-427C-989E')
group by 1,2,3,4 order by puin asc, called_or_mailed_date desc )
SELECT *   FROM summary where row_number < 6 ) as a
group by 1

PostgreSQL 9.2 Volume 2 - Summary

4. SQL Syntax
Value Expression
http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html

Calling Function

Named notation is especially useful for functions that have a large number of parameters (makes the associations between parameters and actual arguments more explicit and reliable)

Positional notation, a function call is written with its argument values in the same order as they are defined in the function declaration (parameters can only be omitted from right to left)

CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
       RETURNS text AS $$
       SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END;
$$ LANGUAGE SQL IMMUTABLE STRICT;
Positional Notation
SELECT concat_lower_or_upper('Hello', 'World', true);
 HELLO WORLD
SELECT concat_lower_or_upper('Hello', 'World');
 hello world
Named notation
SELECT concat_lower_or_upper(a := 'Hello', b := 'World', uppercase := true);
 HELLO WORLD
SELECT concat_lower_or_upper(a := 'Hello', uppercase := true, b := 'World');
 HELLO WORLD
Mixed Notation
SELECT concat_lower_or_upper('Hello', 'World', uppercase := true);
 HELLO WORLD

5. Data Definition
Constraints - following example demonstrates different methods of constraints usage.

CREATE TABLE products (
CHECK (price > 0),
price numeric,
price1 numeric,
CONSTRAINT positive_price CHECK (price1 > 0),
CHECK (price > price1),
CHECK (discounted_price > 0 AND price > discounted_price),
product_no integer UNIQUE,
product_no integer CONSTRAINT must_be_different UNIQUE,
product_no integer REFERENCES products (product_no), -- foreign key
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2),
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE);

RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked. CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.

Modifying Table

ALTER TABLE products ADD COLUMN description text; --adding a column
ALTER TABLE products DROP COLUMN description CASCADE; -- drop column
-- add constrain
ALTER TABLE products ADD CHECK (name <> ''); 
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); 
ALTER TABLE products ADD FOREIGN KEY (product_id) REFERENCES product_groups;
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; -- add default
ALTER TABLE products RENAME COLUMN prod_no TO product_no; -- rename column

6.Partitioning 

Partitioning refers to splitting what is logically one large table into smaller physical pieces. Benefits are:

• Query performance can be improved, when heavily accessed rows of table are in a single partition. The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory.
• When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of using an index..
• Bulk loads and deletes can be accomplished by adding or removing partitions, if that requirement is planned into the partitioning design. 
• Seldom-used data can be migrated to cheaper and slower storage media.

Range Partitioning       

The table is partitioned into "ranges" defined by a key column or set of columns, with no overlap

List Partitioning        

The table is partitioned by explicitly listing which key values appear in each partition.

http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html

Combining Queries

The results of two queries can be combined using the set operations union, intersection, and difference.   

• query1 UNION [ALL] query2 - eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used  
• query1 INTERSECT [ALL] query2 - returns all rows that are both in the result. Duplicate rows are eliminated unless INTERSECT ALL is used.  
• query1 EXCEPT [ALL] query2 - returns all rows that are in the result of query1 but not in the result of query2. (This is sometimes called the difference between two queries.)

7.With Queries (Common Table Expressions)

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

Data-Modifying Statements in WITH

You can use data-modifying statements (INSERT, UPDATE, or DELETE) in WITH. This allows you to perform several different operations in the same query. An example is:

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

This query effectively moves rows from products to products_log. The DELETE in WITH deletes the specified rows from products, returning their contents by means of itsRETURNING clause; and then the primary query reads that output and inserts it into products_log.

8. Data type

Chapter 9. Functions and Operators

9.3. Mathematical Functions and Operators
http://www.postgresql.org/docs/9.1/interactive/functions-math.html
9.4. String Functions and Operators
http://www.postgresql.org/docs/9.1/interactive/functions-string.html
9.5. Binary String Functions and Operators
http://www.postgresql.org/docs/9.1/interactive/functions-binarystring.html
9.7. Pattern Matching
http://www.postgresql.org/docs/9.1/interactive/functions-matching.html
9.8. Data Type Formatting Functions
http://www.postgresql.org/docs/9.1/interactive/functions-formatting.html
9.9. Date/Time Functions and Operators
http://www.postgresql.org/docs/9.1/interactive/functions-datetime.html
9.10. Enum Support Functions
http://www.postgresql.org/docs/9.1/interactive/functions-enum.html 

9.11. Geometric Functions and Operators
http://www.postgresql.org/docs/9.1/interactive/functions-geometry.html
9.12. Network Address Functions and Operators
http://www.postgresql.org/docs/9.1/interactive/functions-net.html
9.13. Text Search Functions and Operators
http://www.postgresql.org/docs/9.1/interactive/functions-textsearch.html
9.14. XML Functions
http://www.postgresql.org/docs/9.1/interactive/functions-xml.html
9.15. Sequence Manipulation Functions
http://www.postgresql.org/docs/9.1/interactive/functions-sequence.html
9.16. Conditional Expressions
Case

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

NULLIF

The NULLIF function returns a null value if value1 equals value2; otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:

SELECT NULLIF(value, '(none)')

9.17. Array Functions and Operators
http://www.postgresql.org/docs/9.1/interactive/functions-array.html
9.18. Aggregate Functions
http://www.postgresql.org/docs/9.1/interactive/functions-aggregate.html
9.19. Window Functions
http://www.postgresql.org/docs/9.1/interactive/functions-window.html 

9.22. Set Returning Functions
This section describes functions that possibly return more than one row. http://www.postgresql.org/docs/9.1/interactive/functions-info.html

SELECT * FROM generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
SELECT * FROM generate_series(5,1,-2);
 generate_series
-----------------
               5
               3
               1

-- this example relies on the date-plus-integer operator
SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
   dates
------------
 2004-02-05
 2004-02-12
 2004-02-19

SELECT * FROM generate_series('2008-03-01 00:00'::timestamp, '2008-03-04 12:00', '10 hours');
   generate_series   
---------------------
 2008-03-01 00:00:00
 2008-03-01 10:00:00
 2008-03-01 20:00:00
 2008-03-02 06:00:00
 2008-03-02 16:00:00

9.23 System Information Functions
Session Information Functions

Name	Return Type	Description
current_catalog	name	name of current database (called "catalog" in the SQL standard)
current_database()	name	name of current database
current_query()	text	text of the currently executing query, as submitted by the client (might contain more than one statement)
current_schema[()]	name	name of current schema
current_schemas(boolean)	name[]	names of schemas in search path, optionally including implicit schemas
current_user	name	user name of current execution context
inet_client_addr()	inet	address of the remote connection
inet_client_port()	int	port of the remote connection
inet_server_addr()	inet	address of the local connection
inet_server_port()	int	port of the local connection
pg_backend_pid()	int	Process ID of the server process attached to the current session
pg_conf_load_time()	timestamp with time zone	configuration load time
pg_is_other_temp_schema(oid)	boolean	is schema another session's temporary schema?
pg_listening_channels()	setof text	channel names that the session is currently listening on
pg_my_temp_schema()	oid	OID of session's temporary schema, or 0 if none
pg_postmaster_start_time()	timestamp with time zone	server start time
session_user	name	session user name
user	name	equivalent to current_user
version()	text	PostgreSQL version information

9.24. System Administration Functions
http://www.postgresql.org/docs/9.1/interactive/functions-admin.html