Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PostgreSQL procedural language learning     - Several Methods of SSH Auto - login (Linux)

- Virtualization and IT cooperation (Linux)

- How do I upgrade to Ubuntu 15.04 (Beta) (Linux)

- Nginx1.8 version upgrade method AMH4.2 Free manually compile (Server)

- Ubuntu Linux installation GAMIT10.6 (Linux)

- Nine artifact control disk partition under Linux (Linux)

- Node.js form --formidable (Programming)

- Hibernate4 The Hello World (basic environmental structures) (Programming)

- C ++ free store and heap (Programming)

- ElasticSearch - Basic Concepts (Server)

- Java reflection by calling the class method (Programming)

- To install Jetty server configuration in detail (Server)

- Google open source TCP team repaired the Linux kernel TCP flaw (Linux)

- How to use the process on the desktop xkill end Linux (Linux)

- Orionode source Linux Mint installation (Linux)

- How to use the TF / SD card making Exynos 4412 u-boot boot disk in Ubuntu (Linux)

- Use Oracle 11g show spparameter command (Database)

- Https (SSL / TLS) Detailed principles (Server)

- Vim useful plugin: EasyGrep (Linux)

- Hadoop 2.0 Detailed Configuration Tutorial (Server)

 
         
  PostgreSQL procedural language learning
     
  Add Date : 2017-08-31      
         
         
         
  Some time ago, a new company to replace PostgreSQL data mart system process to download a pdf file postgresqlAPI study a little PostgreSQL data marts. Found that the use PostgreSQL procedural language can greatly enhance the efficiency of their own data processing, the following will give you an example! I believe we looked after, will also like PostgreSQL procedural language .......

Firstly, some SQL script, the SQL query arrears data September 10, 2015 of:

SELECT DISTINCT
A.DAY_ID Statistics Date
, A.CHANNEL_NAME Branch name
, A.OWE_MONTH account of arrears
, SUM (B.current_charge) Recycling Day
FROM Table 1 A LEFT JOIN Table 2 B ON A.SERV_ID = B.SERV_ID
WHERE A.AREA_ID = 27 AND B.mkt_area_ID = 27 AND A.DAY_ID = 20150910 AND A.OWE_MONTH = 201508 AND B.ACCT_MONTH = 201508
GROUP BY A.DAY_ID, A.CHANNEL_NAME, A.OWE_MONTH

Although this script can extract a day of arrears data, then the question is, if you want to extract data arrears, September 1 to 10, that is not to execute the script ten times what?

Of course, there is a better way to extract data arrears, that is the section SQL scripts written in PostgreSQL procedural language, then only need to perform custom functions can be extracted ten days arrears data, and this greatly improve their productivity.

Finally, improved PostgreSQL procedural language functions and postgresqlAPI pdf file (Chinese version forehead!):

Function name wx_qf_hdb (date statistics, 01,508); format: wx_qf_hdb (20150901, 201508);
Function: Extract the current arrears aging cutoff date statistics for each branch office of the total number of arrears recovery
* /
--start
CREATE FUNCTION wx_qf_hdb (day_id numeric, acct_month numeric) RETURNS TEXT AS $$
DECLARE
LS_SQL VARCHAR (5000);
today VARCHAR (8);
qf_month VARCHAR (6);
month_l INTEGER;
num numeric;
BEGIN
today: = TRIM (to_char (day_id, 99999999)); - Deadline into a string
qf_month: = TRIM (to_char (acct_month, 999999)); - month arrears into a string
num: = to_number (substr (today, 1,6), 999999); - the front region of the string 6
month_l: = to_date (to_char (day_id + 1, '9999-99-99'), 'yyyy-mm-dd') - to_date (to_char (num, '9999-99') || '-01', 'yyyy -mm-dd '); - the number of days of the month
FOR i IN 1..month_l LOOP
    LS_SQL: = 'create table temp_wuxi_qf' || i || 'as
SELECT DISTINCT
A.DAY_ID Statistics Date
, A.CHANNEL_NAME Branch name
, A.OWE_MONTH account of arrears
, SUM (B.current_charge) Recycling Day
FROM Table 1A LEFT JOIN Table 2 B ON A.SERV_ID = B.SERV_ID
WHERE A.AREA_ID = 27 AND B.mkt_area_ID = 27 AND A.DAY_ID = '|| to_char (day_id-month_l + i, 99999999) ||' AND A.OWE_MONTH = '|| qf_month ||' AND B.ACCT_MONTH = '|| qf_month ||'
GROUP BY A.DAY_ID, A.CHANNEL_NAME, A.OWE_MONTH ';
    EXECUTE LS_SQL;
END LOOP;
- Data Retrieval days
LS_SQL: = 'create table temp_wuxi_qf' || qf_month || 'as';
FOR i IN 1..month_l LOOP
    LS_SQL: = LS_SQL || 'SELECT * FROM temp_wuxi_qf' || i || 'UNION';
END LOOP;
LS_SQL: = LS_SQL || 'SELECT * FROM temp_wuxi_qf' || month_l;
EXECUTE LS_SQL;
- Summary daily data
FOR i IN 1..month_l LOOP
    LS_SQL: = 'DROP TABLE temp_wuxi_qf' || i;
    EXECUTE LS_SQL;
END LOOP;
- Delete all temporary tables
RETURN LS_SQL;
END;
$$ LANGUAGE plpgsql;
--end
select wx_qf_hdb (20150903,201508); - run function wx_qf_hdb (statistical date, arrears billing period)
DROP FUNCTION wx_qf_hdb (day_id numeric, acct_month numeric); - Delete Function
select * from temp_wuxi_qf arrears billing period; - lookup table
drop table temp_wuxi_qf arrears billing period; - delete the table
     
         
         
         
  More:      
 
- Install Git on CentOS (Linux)
- AppCode developed Mac OS X application or shared library experience summary (Programming)
- Linux screen commonly commands (Linux)
- XP virtual machine under VirtualBox solve occupy 100% CPU problem (Linux)
- Ubuntu 14.10 Install Ubuntu Touch Music App 2.0 (Linux)
- Linux_Logo - output color ANSI Linux distributions logo command-line tool (Linux)
- Android first line of code study notes (Programming)
- Secondary exponential smoothing prediction method implemented in Python (Programming)
- linux smartd [FAILED] appears at startup (Linux)
- A well-known but rarely used skills: object pool (Programming)
- Install Python 3.3.4 under CentOS 6.4 (Linux)
- See how --nand flash timing diagram of a read operation Comments (Programming)
- How to upgrade Docker 1.6 on Fedora / CentOS (Server)
- 7 extremely dangerous Linux commands (Linux)
- Rails 4.1.6 start being given Could not find a JavaScript runtime (Linux)
- Some of the bibliographic management tools to good use on linux (Linux)
- Install the latest Pinta graphics editing software on Ubuntu 14.04 (Linux)
- Ubuntu 14.04 solved using MyEclipse 10.7 flash back (Linux)
- Java interface (Programming)
- Zabbix monitoring Oracle Database use Orabbix plug (Enhanced Edition) (Database)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.