---
# System prepended metadata

title: DB Migration File

---

# DB Migration File
## Notice
1. 針對主題用檔案命名 `[編號]_[需求].sql` 檔名勿超過50字
(範例： 如果是新增[fn_platform_report]此Function、或是新增[platform]此table)
```sql
00001_add_fn_platform_report.sql
```
2. 若超過一個以上指令請用DO Scope
3. 若檔名看不出需求是什麼，寫註解(範例)
![](https://i.imgur.com/R2M2LoQ.png)
4. Function 命名請用in_、out_銜接(範例)、Declare 用tmp
```sql
CREATE OR REPLACE FUNCTION fn_get_bet_sum_lottery_list(in_site_id bigint, in_game_code varchar, in_s_date timestamp without time zone...
```
```sql
RETURNS TABLE(out_user_id bigint, out_account character varying, out_real_amt_sum numeric, out_pay_off numeric, out_created_at date, out_superior bigint, out_superior_account character varying, out_bet_order_num bigint)
```
```sql
DECLARE
    tmp_bet_people int;
    tmp_users_arr bigint[];
    tmp_filter_disable_game int;
BEGIN
```
5. Column需要comment；Function需要input、output comment；
(if not exists都必須要使用)


## pgagent & pgAdmin
1.對pgAgent Jobs點右鍵->Create->pgAgent Job...
![](https://i.imgur.com/kflnFW9.png)
2.輸入schedule名稱，選擇Job class:Routine Maintenance
![](https://i.imgur.com/Cw1PHCr.png)
3.點開Steps並點選右上的加號 輸入名稱並設定其選項
![](https://i.imgur.com/zA1HI59.png)
4.寫上要使用的function
![](https://i.imgur.com/5a3kOVN.png)
5.切到Schedules 選擇開始及結束日期
![](https://i.imgur.com/7lXlsCU.png)
6.選擇重複執行的時間
![](https://i.imgur.com/SjrQJVv.png)
7.產生的SQL
![](https://i.imgur.com/heWXEBJ.png)
