PostgreSQL uses dynamic statement implementation functions

CREATE FUNCTION "public". " Newproc "(in" p_date "varchar, out" OUT_VV "int4)
RETURNS "Pg_catalog". " Int4 "as $BODY $
Declare
V_sqlcode INTEGER;
V_date date;
V_pre_date date;
V_SQLERRM varchar (4000);
V_begintime varchar (20);
V_endtime varchar (20);
V_sql varchar (4000);
Activations INTEGER;
Pro_name varchar (4000);
V_column INTEGER;
INTRO_PV INTEGER;
Intro_uv INTEGER;
CM_PV INTEGER;
Cm_uv INTEGER;
RP_PV INTEGER;
Rp_uv INTEGER;
CP_PV INTEGER;
Cp_uv INTEGER;
Registers INTEGER;
Begin
V_date: =to_date (p_date, ' yyyymmdd ');
V_pre_date: =v_date+1;
V_begintime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');
pro_name:= ' Sp_stat_client_visit ';

--Connecting to a remote database
PERFORM Dblink_connect_u (' Test_dblink ', ' Dbname=redfinger host=10.100.0.254 port=7432 user=redfinger password= Redfinger ');
--Get the activation amount to Android
--v_sql:= ' SELECT COUNT (*) from Rf_version_log t ';
V_sql:= ' SELECT COUNT (*) from rf_version_log_tmp t where T.install_type = ' 1 ' and client_type= ' Android '
and T.create_time >= ' | | Quote_literal (v_date) | | ' and T.create_time < ' | | Quote_literal (v_pre_date) | | ";
SELECT * from Dblink (' Test_dblink ', v_sql) as T (Total_num Int.) into activations;

GET Diagnostics v_column = Row_count;

V_endtime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');

--Record log
Insert into T_log (Pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count)
VALUES (PRO_NAME,P_DATE,V_BEGINTIME,V_ENDTIME,V_SQLERRM, ' Y ', v_sql,v_column);

--all cuid of the activated user are queried and saved to the temporary table Lsb_active_info
Delete from Lsb_active_info;
V_sql: = ' SELECT distinct cuid from rf_version_log_tmp where install_type= ' 1 ' and Create_time >= ' | | Quote_literal (v_date) | | ' and Create_time < ' | | Quote_literal (v_pre_date) | | ' and client_type= ' Android ';
Insert into lsb_active_info (cuid)
SELECT * from Dblink (' Test_dblink ', v_sql) as T (cuid VARCHAR);

GET Diagnostics v_column = Row_count;
V_endtime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');
Insert into T_log (Pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count)
VALUES (PRO_NAME,P_DATE,V_BEGINTIME,V_ENDTIME,V_SQLERRM, ' Y ', v_sql,v_column);

--Saves the cuid that visited the boot page to the cuid_tmp temp table, and then joins the table with the active cuid table, querying the PV and Uvs that access the boot page type_id=1
V_sql:= ' select count (i.cuid) Pv,count (distinct i.cuid) UV from Lsb_active_info i,lsb_cbh_test b
where I.cuid = B.cuid and b.type_id=1 ';
Execute v_sql into INTRO_PV,INTRO_UV;
GET Diagnostics v_column = Row_count;
V_endtime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');
Insert into T_log (Pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count)
VALUES (PRO_NAME,P_DATE,V_BEGINTIME,V_ENDTIME,V_SQLERRM, ' Y ', v_sql,v_column);

--Statistics of PV and UV type_id=2 for visitors to the Cloud mobile page
V_sql:= ' select count (i.cuid) Pv,count (distinct i.cuid) UV from active_info_tmp i,lsb_cbh_test b
where I.cuid = B.cuid and b.type_id=2 ';
Execute v_sql into CM_PV,CM_UV;
GET Diagnostics v_column = Row_count;
V_endtime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');
Insert into T_log (Pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count)
VALUES (PRO_NAME,P_DATE,V_BEGINTIME,V_ENDTIME,V_SQLERRM, ' Y ', v_sql,v_column);

--statistical access to the registration page of PV and UV type_id=3
V_sql:= ' select count (i.cuid) Pv,count (distinct i.cuid) UV from active_info_tmp i,lsb_cbh_test b
where I.cuid = B.cuid and b.type_id=3 ';
Execute v_sql into RP_PV,RP_UV;
GET Diagnostics v_column = Row_count;
V_endtime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');
Insert into T_log (Pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count)
VALUES (PRO_NAME,P_DATE,V_BEGINTIME,V_ENDTIME,V_SQLERRM, ' Y ', v_sql,v_column);

--Statistical SMS verification code for PV and UV
V_sql: = ' select count (t.sms_mobile), COUNT (distinct t.sms_mobile) from rf_sms t where T.sms_source = ' 3 ' and CLIENT_SOURC E= ' Android '
and T.send_time >= ' | | Quote_literal (v_date) | | ' and T.send_time < ' | | Quote_literal (v_pre_date) | | ";
SELECT * from Dblink (' Test_dblink ', v_sql) as T (CP_PV Int,cp_uv int.) into CP_PV,CP_UV;

GET Diagnostics v_column = Row_count;

V_endtime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');

--Record log
Insert into T_log (Pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count)
VALUES (PRO_NAME,P_DATE,V_BEGINTIME,V_ENDTIME,V_SQLERRM, ' Y ', v_sql,v_column);

--Statistics of users
V_sql: = ' SELECT count (*) from Rf_user t where t.create_time>= ' | | Quote_literal (v_date) | | ' and T.create_time < ' | | Quote_literal (v_pre_date) | | ' and client = ' Android ';
SELECT * from Dblink (' Test_dblink ', v_sql) as T (registers int.) into registers;

GET Diagnostics v_column = Row_count;

V_endtime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');

--Record log
Insert into T_log (Pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count)
VALUES (PRO_NAME,P_DATE,V_BEGINTIME,V_ENDTIME,V_SQLERRM, ' Y ', v_sql,v_column);

--Insert Result data
Delete from lsb_stat_client_visit_temp where stat_date=v_date;
v_sql:= ' INSERT into lsb_stat_client_visit_temp (activations,intro_pv,intro_uv,cm_pv,cm_uv,rp_pv,rp_uv,stat_date, Cp_pv,cp_uv,registers) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) ';
Execute v_sql using Activations,intro_pv,intro_uv,cm_pv,cm_uv,rp_pv,rp_uv,v_date,cp_pv,cp_uv,registers;
GET Diagnostics v_column = Row_count;
V_endtime:=to_char (now (), ' yyyy/mm/dd hh24:mi:ss ');
Insert into T_log (Pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count)
VALUES (PRO_NAME,P_DATE,V_BEGINTIME,V_ENDTIME,V_SQLERRM, ' Y ', v_sql,v_column);

out_vv:=0;

PERFORM dblink_disconnect (' Test_dblink ');

EXCEPTION
When OTHERS Then

V_SQLERRM: =substr (SQLERRM, 1, 2000);

Insert into T_log (Pro_name,tj_date,begin_time,err_time,err_infor,run_result,v_sql,sql_count)
VALUES (Pro_name,p_date,v_begintime,now (), V_SQLERRM, ' N ', v_sql,v_column);
PERFORM dblink_disconnect (' Test_dblink ');
OUT_VV: =-1;
End
$BODY $
LANGUAGE ' plpgsql ' VOLATILE cost 100
;

ALTER FUNCTION "public". " Newproc "(in" p_date "varchar, out" OUT_VV "int4) OWNER to" Sys_report ";

PostgreSQL uses dynamic statement implementation functions