sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql12-server
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

download:
https://www.postgresql.org/download/linux/redhat/
https://yum.postgresql.org/rpmchart/
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7.6-x86_64/

postgresql12-libs-12.7-1PGDG.rhel7.x86_64.rpm 
postgresql12-contrib-12.7-1PGDG.rhel7.x86_64.rpm
postgresql12-12.7-1PGDG.rhel7.x86_64.rpm 
postgresql12-server-12.7-1PGDG.rhel7.x86_64.rpm

yum install ./postgresql12-libs-12.7-1PGDG.rhel7.x86_64.rpm 
yum install ./postgresql12-12.7-1PGDG.rhel7.x86_64.rpm 
yum install ./postgresql12-contrib-12.7-1PGDG.rhel7.x86_64.rpm -y
yum install ./postgresql12-server-12.7-1PGDG.rhel7.x86_64.rpm -y

$ locate bin/postgres
$ /usr/pgsql-12/bin/postgres -V
postgres (PostgreSQL) 12.7

su - postgres
进入bash，输入
#psql
就进入到plsql命令窗口，执行\l就可以看到所有db
为啥默认角色:postgres呢，执行：
\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 _gvm      |                                                            | {dba}
 dba       | Superuser, No inheritance, Cannot login                    | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

可以创建更多角色
sudo -u postgres createuser owning_user
sudo -u postgres createdb -O owning_user dbname

\l \list
\c [DATABASE]
\d
\d TABLE_NAME
\du

\dx
SELECT * FROM pg_extension;

--format output
\x on

解锁 unlock
dbname=# select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relname = 'tablename';
dbname=# SELECT pg_cancel_backend(the id);
SELECT pg_terminate_backend(3567);
select * from pg_stat_activity where pid=3567;

\copy (query) to result.csv csv header

### 1.4 Config 

#### 理解一下posgresql的用户概念

the `postgres` PostgreSQL user account already exists and is configured to be accessible via `peer` authentication for unix sockets in `pg_hba.conf`

https://www.liquidweb.com/kb/what-is-the-default-password-for-postgresql/

本机如果直接通过plsql连接，可以修改local用户(默认用户postgres)：

https://www.hostinger.com/tutorials/how-to-install-postgresql-on-centos-7/

https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge



#### 开启远程连接：

https://blog.csdn.net/zhangzeyuaaa/article/details/77941039

## 2. Syntax

Assign null or empty ‘’ to numberic,  to_number(‘’) throw exception, use if else instead
Bigint default value is NULL not 0

### 2.1 Basic

https://www.tutorialspoint.com/postgresql/index.htm

https://www.postgresql.org/docs/10/app-psql.html

表空间

#### System

#### Data Format

**varchar to numeric**

**Date**

ORACLE VS POSTGRES

https://www.postgresql.org/docs/9.1/functions-formatting.html

https://www.postgresql.org/docs/9.1/static/functions-datetime.html

https://thebuild.com/blog/2018/08/07/does-anyone-really-know-what-time-it-is/

https://www.postgresql.org/docs/9.2/functions-datetime.html

#### Tables and Views
The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types) (...)

### 2.2 CRUD

#### update from select subquery

```sql
UPDATE dummy
SET customer=subquery.customer,
    address=subquery.address,
    partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
      FROM  /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;

OR

WITH subquery AS (
    SELECT address_id, customer, address, partn
    FROM  /* big hairy SQL */ ...
)
UPDATE dummy
SET customer = subquery.customer,
    address  = subquery.address,
    partn    = subquery.partn
FROM subquery
WHERE dummy.address_id = subquery.address_id;

if exists (select * from table where ...) then
	update
else
	insert
end if;

Oracle merge into
Pgsql insert into
https://www.postgresql.org/docs/9.5/static/sql-insert.html
insert into table1 as t1(col1,col2, col3)
select col1, col2,col3
from ( select col1,col2, max(col3) as col3
 from table2
 where col3 >= date'2012-01-01'
 and col3 <date'2013-01-01'
 and col1 is not null
 and col2 is not null
 group by col1, col2 ) a
on conflict(col1, col2) do
update set col3 = excluded.col3,
   	 col2 = case when excluded.col2 is null then t1.col2 else excluded.col2 end

WITH moved_rows AS (
		DELETE FROM t_bticoin_index
		WHERE index_time/1000<=
		extract(epoch FROM cast(current_date-interval '7 day' as text)::timestamp AT TIME ZONE 'SGT')::int
		RETURNING index_name,index_value,index_time,server_time
	)
	INSERT INTO t_bticoin_index_hist
	SELECT * FROM moved_rows;

if condition_1 then
  statement_1;
elsif condition_2 then
  statement_2
...
elsif condition_n then
  statement_n;
else
  else-statement;
end if;

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
	RAISE EXCEPTION 'employee % not found', myname;
END IF;

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

select 'btc',null,extract(epoch FROM index_time::timestamp AT TIME ZONE 'SGT')::int,(extract(epoch from now())*1000)::bigint
			from generate_series(cast(current_date-interval '1 day'+interval '19 hour'+interval '1 minute' as text)::timestamp AT TIME ZONE 'SGT', 
				cast(current_date+interval '5 hour' as text)::timestamp AT TIME ZONE 'SGT', 
				interval '1 minutes') as s(index_time)
			where index_time not in (select to_timestamp(index_time/1000) from t_bticoin_index_test)
			
查找缺失数据			
SELECT *
FROM generate_series('2022-01-14 12:00:00','2022-01-25 11:19:00', 
				interval '1 minutes') as s(index_time)		
where index_time not in (select to_timestamp(index_time/1000) from t_bticoin_index  where index_time/1000>=
	extract(epoch FROM cast('2022-01-14 12:00:00' as text)::timestamp AT TIME ZONE 'SGT')::int);

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- making use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE FUNCTION process_emp_audit();

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

PARTITIONING-DECLARATIVE:
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

PARTITIONING-USING-INHERITANCE:
ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

CREATE OR REPLACE FUNCTION public.pg_auto_insert_TABLE()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
declare
	partition_column_name	text ;
	strSQL	text;
	curMM	varchar(6);
	isExist	boolean;
	startTime	text;
	endTime	text;
begin
	partition_column_name := TG_ARGV[0];

	EXECUTE 'SELECT $1.'||partition_column_name INTO strSQL USING NEW;
	raise info 'strSQL:%',strSQL;
	curMM := to_char(to_timestamp(strSQL::BIGINT/1000), 'YYYYMM'); 
	raise info 'curMM:%',curMM;

	select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curMM);

    IF ( isExist = false ) THEN  
        startTime := curMM||'01 00:00:00.000';
        endTime := to_char(startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curMM||
                  ' ( CHECK('||partition_column_name||'>='''|| (extract(epoch FROM startTime::timestamp AT TIME ZONE 'SGT')*1000)::bigint ||''' AND '
                             ||partition_column_name||'< '''|| (extract(epoch FROM endTime::timestamp AT TIME ZONE 'SGT')*1000)::bigint ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;';  
        EXECUTE strSQL;

		strSQL := 'CREATE INDEX idx_time_'||curMM||' ON '
						  ||TG_RELNAME||'_'||curMM||' ('||partition_column_name||');';
		EXECUTE strSQL;
	
    END IF;

 	strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*';
    EXECUTE strSQL USING NEW;
	RETURN NULL;
END;
$function$
;

CREATE TRIGGER insert_TABLE
BEFORE INSERT
ON t_TABLE
FOR EACH ROW
EXECUTE PROCEDURE pg_auto_insert_TABLE('index_time');

CREATE OR REPLACE FUNCTION public.pg_houseclean()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare
result integer;
previous_month varchar;
strSql varchar;
begin
	select to_char(current_date - interval '1' month, 'YYYYMM') into previous_month;
	strSql :='ALTER TABLE t_TABLE_'||previous_month||' NO INHERIT t_TABLE;';
	raise info 'strSQL:%',strSql;
	EXECUTE strSql;
	select 100 into result;
	RETURN result;
END;
$function$
;

select * from pg_class c join pg_namespace ns on c.relnamespace = ns.oid where c.relname='idx_time_202204';

Oracle bulk collect into
Pgsql Offset 
/*CREATE TYPE t_my AS (
    col1    numeric(16,6),
    col2    numeric,
    col3 bpchar(36)
    );*/
    
do $$DECLARE   
c cursor(os int) for select * from table limit 5 offset os;
r record;
v_name varchar(100)[];
recs t_my[];
BEGIN

    select array (select row(0, col, 'aaa') from table1 limit 5 offset 10 ) into recs;
    --v_games:= array_to_string(v_name,',');
    raise notice '%', recs[1].col1;
END
$$;

select  col1, col2, min(col3)  points
 from (select t1.col1, t1.col2,
        	first_value(col3) over w as col3  
        	from table1  t1
        	where t1.date >= to_date('2018-01-01','yyyy-mm-dd')
        	and t1.date<to_date('2018-07-01','yyyy-mm-dd')
        	window w AS(
   			 partition by col1,col2
   			 order by date desc
   		 )      	 
) t group by col1, col2
order by 1,2;

In sp pg_test:
	INOUT results refcursor)
	results := 'cur';
_query_:=’select * from table’;
perform pg_dyncursor('cur', _query_);
Then in plsql:
	Begin
		Call pg_test();
	End;
FETCH ALL IN cur;
close cur;

CREATE OR REPLACE FUNCTION pg_dyncursor(cursor_name text, query text)
	RETURNS void
	LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN 
	EXECUTE 'DECLARE' || quote_ident(cursor_name) || ' CURSOR WITH HOLD FOR ' || query;
END 
$function$

pom.xml:
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        
application.yml:       
spring:
  ##Druid DataSource数据库访问配置
  datasource:
    #is-dynamic-datasource: true
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:postgresql://HOST:5432/test
    username: postgres
    password: postgres
    driver-class-name: org.postgresql.Driver
    druid:
      #连接池配置
      #初始化时建立物理连接的个数
      initialSize: 1
      #最小连接池数量
      minIdle: 0
      #最大连接池数量
      maxActive: 5
      #获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，
      #如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
      maxWait: 60000
      #配置相隔多久进行一次检测(检测可以关闭的空闲连接),此处设置为1分钟检测一次。
      timeBetweenEvictionRunsMillis: 60000
      #一个连接在池中最小生存的时间(ms),此处设置为半小时。
      minEvictableIdleTimeMillis: 1800000
      #一个连接在池中最大生存的时间(ms),此处设置为7天。
      maxEvictableIdleTimeMillis: 25200000
      #用来检测连接是否有效的sql; 如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会启作用。
      validationQuery: SELECT 1 FROM DUAL
      #检测连接是否有效的超时时间,默认-1(单位:秒).
      validationQueryTimeout: 5
      #建议配置为true，不影响性能，并且保证安全性，申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，
      #执行validationQuery检测连接是否有效。
      testWhileIdle: true
      #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
      testOnBorrow: false
      #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      testOnReturn: false
      #是否缓存preparedStatement，也就是PSCache;PSCache对支持游标的数据库性能提升巨大，比如说oracle;
      #在mysql5.5以下的版本中没有PSCache功能，建议关闭掉。
      poolPreparedStatements: true
      #打开PSCache，并且指定每个连接上PSCache的大小
      maxPoolPreparedStatementPerConnectionSize: 20
      sharePreparedStatements: false
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      #connectionProperties: druid.stat.mergeSql=true;druid.stat.logSlowSql=true;druid.stat.slowSqlMillis=5000
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,log4j2
      #要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
      #在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
      #此处默认为-1
      maxOpenPreparedStatements: 10
      #合并多个DruidDataSource的监控数据
      useGlobalDataSourceStat: true
      
      
@Service("jdbcService")
public class JdbcServiceImpl implements JdbcService{
    private Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    public DataSource dataSource;
    
    @Autowired
    JdbcTemplate jdbcTemplate;

    private Connection connection;

    public Connection getConnection() {
        try {
            if (connection == null || connection.isClosed()) {
                connection = initConnection();
            }
        } catch (SQLException e) {
            logger.error("Initialize connection failed: {}", e.getMessage(), e);
        }
        return connection;
    }

    public Connection initConnection(){
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            logger.error("Initialize connection failed: {}", e.getMessage(), e);
        }
        return conn;
    }


    @Override
    public void savetList(List<Test> list) {
        String sql = "insert into t_table(col1,col2) select ?,?,?,? where not exists (select col1 from t_table where col1=?)";
        jdbcTemplate.batchUpdate(sql, list, 10000, new ParameterizedPreparedStatementSetter<Test>() {
            @Override
            public void setValues(java.sql.PreparedStatement ps, Test ac) throws SQLException {
                ps.setString(1, ac.getName());
                ps.setBigDecimal(2, ac.getValue());
            }
        });
    }

    @Override
    public void callPgFunction() {
        final SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName("pg_calc_rate");
        final Map<String, Object> result = jdbcCall.execute();

        logger.info("jdbcCall result: {}",result.get("returnvalue"));
    }

pg_calc_rate:

CREATE OR REPLACE FUNCTION public.pg_calc_rate()
	RETURNS integer
	LANGUAGE plpgsql
AS $$
declare
	v_condition integer := -100;
	v_product t_fundingrate.product%type := 'BTCP';
	v_session t_fundingrate."session"%type := 'T+1';
	v_trade_date t_fundingrate.trade_date%type := to_char(current_date-1,'yyyymmdd');
	v_rate_type t_fundingrate.rate_type%type;
	v_funding_rate t_fundingrate.funding_rate%type;
	v_benchmark_price t_fundingrate.benchmark_price%type;
	result integer;
begin
	
	if not exists (select * from t_askbidprice where price_time/1000>=extract(epoch FROM cast(current_date-interval '1 day'+interval '19 hour'+interval '1 minute' as text)::timestamp AT TIME ZONE 'SGT')::int
    and price_time/1000<=extract(epoch FROM cast(current_date+interval '5 hour' as text)::timestamp AT TIME ZONE 'SGT')::int) then
		raise info 'Not exists impactmid data between [%,%]',cast(current_date-interval '1 day'+interval '19 hour'+interval '1 minute' as text)::timestamp,cast(current_date+interval '5 hour' as text)::timestamp;
		v_condition := -200;
	elsif not exists (select * from t_bticoin_index where index_time/1000>=extract(epoch FROM cast(current_date-interval '1 day'+interval '19 hour'+interval '1 minute' as text)::timestamp AT TIME ZONE 'SGT')::int) then
		raise info 'Not exists bticoin_index data >= %',cast(current_date-interval '1 day'+interval '19 hour'+interval '1 minute' as text)::timestamp;
    	v_condition := -100;
	elsif not exists (select * from t_bticoin_index where index_time/1000>=extract(epoch FROM cast(current_date+interval '5 hour' as text)::timestamp AT TIME ZONE 'SGT')::int) then
    	raise info 'Not exists bticoin_index data >= %',cast(current_date+interval '5 hour' as text)::timestamp;
    	raise info 'To generate Tentative funding rate';
    	v_condition := 0;
    	v_rate_type := 'Tentative'; 
    else
		raise info 'To generate Confirmed funding rate';
		v_condition := 100;
		v_rate_type := 'Confirmed';
	end if;

	if v_condition >= 0 then
		
		select funding_rate,benchmark_price
		into v_funding_rate,v_benchmark_price
		from(
			select 
			avg((GREATEST(0::decimal,t2.impact_bid_price-t1.index_value)-GREATEST(0::decimal,t1.index_value-t2.impact_ask_price))/t1.index_value) as funding_rate,
			avg(t1.index_value) as benchmark_price
			from t_bticoin_index t1 
			inner join t_askbidprice t2 on t1.index_name=t2.product and t1.index_time=t2.price_time
			where t1.index_time/1000<=
			extract(epoch FROM cast(current_date+interval '5 hour' as text)::timestamp AT TIME ZONE 'SGT')::int
			and t1.index_time/1000>=
			extract(epoch FROM cast(current_date-interval '1 day'+interval '19 hour'+interval '1 minute' as text)::timestamp AT TIME ZONE 'SGT')::int
			group by t1.index_name
		) t;
		
		if exists (select * from t_fundingrate where product=v_product and "session"=v_session and trade_date=v_trade_date) then
			raise info 'Update exists record product=%,session=%,trade_date=%',v_product,v_session,v_trade_date;
			update t_fundingrate set funding_rate=v_funding_rate,remark=v_rate_type,rate_type=v_rate_type,publish_time=(extract(epoch from now())*1000)::bigint,benchmark_price=v_benchmark_price
			where product=v_product and "session"=v_session and trade_date=v_trade_date;
		else
			raise info 'Insert new record';
			insert into t_fundingrate
			(product, "session", trade_date, funding_rate, remark, rate_type, publish_time, benchmark_price, currency)
				select v_product,v_session,v_trade_date, v_funding_rate,
				v_rate_type,v_rate_type,(extract(epoch from now())*1000)::bigint,v_benchmark_price,'USD';
		end if;
		
	end if;
	select v_condition into result;
    RETURN result;
END;
$$
;

call pg_test('9cc4afef-aab7-4c35-bdcd-a9f3d0152eb4','');

begin
call pg_test2('9cc4afef-aab7-4c35-bdcd-a9f3d0152eb4','');
fetch all in cur;
commit;
end

-- PROCEDURE: sgc2.pg_test(character varying, text)

-- DROP PROCEDURE sgc2.pg_test(character varying, text);

CREATE OR REPLACE PROCEDUREpg_test(
	userid character varying,
	INOUT results text)
LANGUAGE 'plpgsql'

AS $BODY$
DECLARE

BEGIN
	
	select cast(999 as decimal) as result;

END;
$BODY$;

public string Execute2(string spName, IEnumerable<NpgsqlParameter> parameters)
       {
           string result;
           try
           {
               using (var conn = new NpgsqlConnection(_connectionString))
               {
                   conn.Open();
                   List<NpgsqlParameter> _params = parameters.ToList();
                   using (var cmd = conn.CreateCommand())
                   {
                       _params.Add(new NpgsqlParameter("", "")); // Add empty string for refcursor param
                       cmd.CommandText = BuildQuery(spName, _params);
                       //cmd.Parameters.Add(new NpgsqlParameter() { ParameterName = "results", Direction = ParameterDirection.InputOutput, Value = "" });
                       var res = cmd.ExecuteScalar();
                       result = res?.ToString();
                  
                   }
                   //string sql = "select pg_test3('9cc4afef-aab7-4c35-bdcd-a9f3d0152eb4');";
                   //var cmd2 = new NpgsqlCommand(sql, conn);
                   //var result2 = cmd2.ExecuteScalar();
                   //var test = JsonConvert.DeserializeObject<ResultModel>(result);
                   conn.Close();
               }
           }
           catch (Exception e)
           {
               _log.Exception(e);
               throw;
           }
           return result;
       }

type rec_tk is record(
	tkno VARCHAR2(100),
	cg_zdj number(12, 0) := 0,
	cg_jsf number(12, 0) := 0
);
type tklist is table of rec_tk index by binary_integer;

create type rec_tk as(
tkno VARCHAR(100),
cg_zdj numeric(12,0),
cg_jsf numeric(12,0)
);

#函数外执行创建类型的SQL
create type rec_cjr as(
cjrid varchar(30),
tk rec_tk[]
);
#函数内对table的使用修改为数组的使用，数组的下标从1开始
p_cjrs rec_cjr[];

CREATE OR REPLACE FUNCTION public.pg_calc_nightsession_funding_rate()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare
	v_condition integer := -100;
	v_nightsession_flag boolean := false;
	v_from_timestamp integer;
	v_to_timestamp integer;
	v_total_points_count integer;
	v_instrument t_fundingrate.instrument%type := 'BTCP';
	v_session t_fundingrate."session"%type := 'T';
	v_trade_date t_fundingrate.trade_date%type := to_char(current_date,'yyyymmdd');
	v_rate_type t_fundingrate.rate_type%type;
	v_funding_rate t_fundingrate.funding_rate%type;
	v_benchmark_price t_fundingrate.benchmark_price%type;
	result integer;
begin
	if CURRENT_TIMESTAMP>=cast(current_date+interval '19 hour'+interval '1 minute' as text)::timestamp then
		v_nightsession_flag := true;
	end if;
	
	if v_nightsession_flag then
		v_from_timestamp := extract(epoch from cast(current_date+interval '19 hour'+interval '1 minute' as text)::timestamp AT TIME ZONE 'SGT')::int;
		v_to_timestamp := extract(epoch from now())::int;
	else
		v_from_timestamp := extract(epoch from cast(current_date-interval '1 day'+interval '19 hour'+interval '1 minute' as text)::timestamp AT TIME ZONE 'SGT')::int;
		v_to_timestamp := extract(epoch from cast(current_date+interval '5 hour' as text)::timestamp AT TIME ZONE 'SGT')::int;
	end if;

	if extract(dow from current_date)::int = 5 and v_nightsession_flag then
		v_trade_date := to_char(current_date+3,'yyyymmdd');
		raise info 'Next trading date is %',v_trade_date;
	elsif extract(dow from current_date)::int = 6 then
		v_trade_date := to_char(current_date+2,'yyyymmdd');
		raise info 'Next trading date is %',v_trade_date;
	end if;
	
	if not exists (select * from t_impactmid where price_time/1000>=v_from_timestamp and price_time/1000<=v_to_timestamp) then
		raise info 'Not exists impactmid data between [%,%]',v_from_timestamp,v_to_timestamp;
		v_condition := -200;
	elsif not exists (select * from t_bticoin_index where index_time/1000>=v_from_timestamp) then
		raise info 'Not exists bticoin_index data >= %',v_from_timestamp;
    	v_condition := -100;
	elsif v_nightsession_flag or not exists (select * from t_bticoin_index where index_time/1000>=v_to_timestamp) then
    	raise info 'To generate Tentative funding rate';
    	v_condition := 0;
    	v_rate_type := 'Tentative'; 
    else
    	select count(1) into v_total_points_count from t_bticoin_index 
		where extract(second from to_timestamp(index_time/1000)) = '00' 
			and index_time/1000<=v_to_timestamp
			and index_time/1000>=v_from_timestamp;
    	if v_total_points_count <= 300 then
			raise info 'bticoin_index data is less than 50 percent';
			v_condition := -300;
		else
			raise info 'To generate Confirmed funding rate';
			v_condition := 100;
			v_rate_type := 'Confirmed';
		end if;
	end if;

	if v_condition >= 0 then
		
		select funding_rate,benchmark_price
		into v_funding_rate,v_benchmark_price
		from(
			select 
			avg((GREATEST(0::decimal,t2.impact_bid_price-t1.index_value)-GREATEST(0::decimal,t1.index_value-t2.impact_ask_price))/t1.index_value) as funding_rate,
			avg(t1.index_value) as benchmark_price
			from t_bticoin_index t1 
			inner join t_impactmid t2 on t1.index_name=t2.instrument and t1.index_time=t2.price_time
			where t1.index_time/1000<=v_to_timestamp and t1.index_time/1000>=v_from_timestamp
			group by t1.index_name
		) t;
		
		if exists (select * from t_fundingrate where instrument=v_instrument and "session"=v_session and trade_date=v_trade_date) then
			raise info 'Update exists record instrument=%,session=%,trade_date=%',v_instrument,v_session,v_trade_date;
			update t_fundingrate set funding_rate=v_funding_rate,remark=v_rate_type,rate_type=v_rate_type,publish_time=(extract(epoch from now())*1000)::bigint,benchmark_price=v_benchmark_price
			where instrument=v_instrument and "session"=v_session and trade_date=v_trade_date;
		else
			raise info 'Insert new record';
			insert into t_fundingrate
			(instrument, "session", trade_date, funding_rate, remark, rate_type, publish_time, benchmark_price, currency)
				select v_instrument,v_session,v_trade_date, v_funding_rate,
				v_rate_type,v_rate_type,(extract(epoch from now())*1000)::bigint,v_benchmark_price,'USD';
		end if;
		
	end if;
	select v_condition into result;
    RETURN result;
END;
$function$
;