when inserting to table, text type parameter become NULL (after big assignment to this parameter) - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">Hi, </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">I am trying to get server parameters (pg_settings) using a cursor and insert them to one column
table.</span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">A stored procedure parameter holds the long string.</span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial">The insert of this parameter succeeds. </span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">The problem is that the string (the parameter value) is not inserted to the table.</span></font><p
class="MsoNormal"><fontface="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">When selecting, this column shows NULL value. </span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">The procedure : </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span style="font-size:
12.0pt;font-family:Arial;color:blue">CREATE OR REPLACE FUNCTION dbu_show_server(monitor_table_id text)</span></font><p
class="MsoNormal"><fontcolor="blue" face="Arial" size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">RETURNS int AS</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">$BODY$</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">DECLARE</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">cur1 cursor for</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">select name,setting,source</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">from pg_settings;</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">p_name text default '';</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">p_value text default '';</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">p_source text default '';</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">p_out text default '';</span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">BEGIN       </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">open cur1;</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">  </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">  loop</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">       </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">       fetch cur1 into p_name,p_value,p_source; </span></font><p
class="MsoNormal"><fontcolor="blue" face="Arial" size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">       exit when not found;  </span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">       </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">       p_out := p_out || p_name || '=' || p_value || ' source=' || p_source ||
',';</span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">          </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">   end loop;</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">close cur1;</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">raise notice 'p_out is %' ,p_out;</span></font><p class="MsoNormal"><font
color="blue"face="Arial" size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">insert into dbu_monitor_table(id,printable_output)
values(monitor_table_id,p_out); </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">return 0; </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span
style="font-size:
12.0pt;font-family:Arial;color:blue">END;</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">$BODY$</span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">LANGUAGE 'plpgsql' VOLATILE;</span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">To activate the procedure: </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span style="font-size:
12.0pt;font-family:Arial;color:blue">select dbu_show_server('Sunday')</span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">The monitor table creation: </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font color="blue" face="Arial" size="3"><span style="font-size:
12.0pt;font-family:Arial;color:blue">create table dbu_monitor_table(id text, printable_output text)</span></font><p
class="MsoNormal"><fontface="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">I noticed that it is a matter of the parameter length. </span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial">When I defined p_out to be shorter, there was no problem.</span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">For example instead of: </span></font><p class="MsoNormal"><font color="blue" face="Arial"
size="3"><spanstyle="font-size: 
12.0pt;font-family:Arial;color:blue">p_out := p_out || p_name || '=' || p_value || ' source=' || p_source || ',';
</span></font><pclass="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">I wrote (here it gets only p_name variable): </span></font><p class="MsoNormal"><font color="blue"
face="Arial"size="3"><span style="font-size: 
12.0pt;font-family:Arial;color:blue">p_out := p_out || p_name || '=' || p_value</span></font><font face="Arial"><span
style="font-family:Arial">;</span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">In this case column printable_output was filled as expected…</span></font><p class="MsoNormal"><font
face="Arial"size="3"><span style="font-size:12.0pt; 
font-family:Arial">Please help. </span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">Thanks </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Yuval Sofer<br /> BMC Software<br /> CTM&D Business Unit</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">DBA Team<br /> 972-52-4286-282<br /><a
href="mailto:yuval_sofer@bmc.com">yuval_sofer@bmc.com</a></span></font><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font><pclass="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt"> </span></font></div>