when inserting to table, text type parameter become NULL (after big assignment to this parameter)

["Sofer, Yuval" <Yuval_Sofer@xxxxxxx>]

Hi,

 

I am trying to get server parameters (pg_settings) using a cursor and insert them to one column table.

 

A stored procedure parameter holds the long string.

The insert of this parameter does not fail.

 

The problem is that the string (the parameter value) is not inserted to the table.

When selecting, this column shows NULL value.

 

The procedure :

 

CREATE OR REPLACE FUNCTION dbu_show_server(monitor_table_id text)

RETURNS int AS

$BODY$

DECLARE

 

cur1 cursor for

select name,setting,source

from pg_settings;

 

p_name text default '';

p_value text default '';

p_source text default '';

 

p_out text default '';

 

BEGIN      

 

open cur1;

 

  loop

      

       fetch cur1 into p_name,p_value,p_source;

       exit when not found; 

      

       p_out := p_out || p_name || '=' || p_value || ' source=' || p_source || ',';

         

   end loop;

 

close cur1;

 

raise notice 'p_out is %' ,p_out;

insert into dbu_monitor_table(id,printable_output) values(monitor_table_id,p_out); 

 

return 0;

 

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

 

To activate the procedure:

 

select dbu_show_server('Sunday')

 

The monitor table creation:

 

create table dbu_monitor_table(id text, printable_output text)

 

I noticed that it is a matter of the parameter length.

When I defined p_out to be shorter, there was no problem.

 

For example instead of:

p_out := p_out || p_name || '=' || p_value || ' source=' || p_source || ',';

I wrote (here it gets only p_name variable):

p_out := p_out || p_name || '=' || p_value ;

 

In this case column printable_output was filled as expected…

Please help.

 

Thanks

 

Yuval Sofer
BMC Software
CTM&D Business Unit

DBA Team
972-52-4286-282
yuval_sofer@xxxxxxx