<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style></head>
<body class='hmmessage'><div dir='ltr'><p class="MsoNormal"><p class="MsoNormal">Dear <span style="font-size: 12pt;">Tatsuo,</span><o:p></o:p></p>
<p class="MsoNormal">Thanks for your reply, <o:p></o:p></p>
<p class="MsoNormal"><span style="color: rgb(0, 112, 192);">Metadata structure of my db like this:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: rgb(0, 112, 192);"> hrvw_emp_info (view) --call-->hrvw_employee_trigger
(view) --call-->smfn_get_param (function)--->select * from
temporary_table.<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">CREATE OR REPLACE VIEW <span style="color: red;">hrvw_emp_info</span> AS <o:p></o:p></p>
<p class="MsoNormal"> SELECT *<o:p></o:p></p>
<p class="MsoNormal"> FROM hrvw_employee_trigger t;<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">CREATE OR REPLACE VIEW <span style="color: red;">hrvw_employee_trigger</span> AS <o:p></o:p></p>
<p class="MsoNormal"> SELECT *<o:p></o:p></p>
<p class="MsoNormal"> FROM ( SELECT *<o:p></o:p></p>
<p class="MsoNormal"> FROM
hrtb_employee_infomation<o:p></o:p></p>
<p class="MsoNormal"> WHERE
smfn_get_param('hrvw_employee_infomation.company_id'::character
varying)::numeric = hrtb_employee_infomation.company_id) a;<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">CREATE OR REPLACE FUNCTION <span style="color: red;">smfn_get_param</span>(pm_param_name character
varying)<o:p></o:p></p>
<p class="MsoNormal"> RETURNS character varying AS<o:p></o:p></p>
<p class="MsoNormal">$BODY$<o:p></o:p></p>
<p class="MsoNormal"> var_param_value varchar2(4000);<o:p></o:p></p>
<p class="MsoNormal">begin<o:p></o:p></p>
<p class="MsoNormal"> select
param_value into var_param_value<o:p></o:p></p>
<p class="MsoNormal"> from
smtb_session_param<o:p></o:p></p>
<p class="MsoNormal"> where
param_name = pm_param_name;<o:p></o:p></p>
<p class="MsoNormal"> return
var_param_value;<o:p></o:p></p>
<p class="MsoNormal"> exception<o:p></o:p></p>
<p class="MsoNormal"> when
others then<o:p></o:p></p>
<p class="MsoNormal"> return
null;<o:p></o:p></p>
<p class="MsoNormal">end$BODY$<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> CREATE OR REPLACE FUNCTION <span style="color: red;">smpr_set_param</span>(pm_param_name character
varying, pm_param_value character varying)<o:p></o:p></p>
<p class="MsoNormal"> ..................<o:p></o:p></p>
<p class="MsoNormal"> CREATE
TEMPORARY TABLE smtb_session_param<o:p></o:p></p>
<p class="MsoNormal"> (<o:p></o:p></p>
<p class="MsoNormal"> param_name
character varying(500) NOT NULL,<o:p></o:p></p>
<p class="MsoNormal"> param_value
character varying(4000),<o:p></o:p></p>
<p class="MsoNormal"> CONSTRAINT
smtb_session_param_pk PRIMARY KEY (param_name )<o:p></o:p></p>
<p class="MsoNormal"> )
ON COMMIT DELETE ROWS;<o:p></o:p></p>
<p class="MsoNormal">.............<o:p></o:p></p>
<p class="MsoNormal"> end$BODY$<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color: red;"> I have added smpr_set_param,
smfn_get_param to black_function_list</span><span style="color:red"> </span><span style="color: red;">parameter in pgpool.conf file:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: red;"> black_function_list=
smpr_set_param,smfn_get_param<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: red;"> <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: red;"> But the queries on views
hrvw_emp_info, hrvw_employee_trigger still forward to the slave.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New Roman","serif";
mso-fareast-font-family:"Times New Roman""> </span></p>
<p class="MsoNormal">> Date: Thu, 11 Apr 2013 17:28:42 +0900<br>
> To: hoangthanhtoan@hotmail.com<br>
> CC: pgpool-general@pgpool.net<br>
> Subject: Re: [pgpool-general: 1597] Master/Slave Mode: Temp table used by
view.<br>
> From: ishii@postgresql.org<br>
> <br>
> > Dear all,Please help me.I have a temporary table is used as a literal
in View objects.in Master/Slave Mode (Pgpool): how to detect all query on this
view, and then transfer these queries to master.Please give me some
advices.Thanks a lot. <br>
> Query examples please.<br>
> --<br>
> Tatsuo Ishii<br>
> SRA OSS, Inc. Japan<br>
> English: http://www.sraoss.co.jp/index_en.php<br>
> Japanese: http://www.sraoss.co.jp<o:p></o:p></p>
<p class="MsoNormal"><br></p></p> </div></body>
</html>