From: Sofer, Yuval <Yuval_Sofer@bmc.com>
To: pgsql-sql@postgresql.org
Subject: when inserting to table, text type parameter become NULL (after big assignment to this parameter)
Date: Wed, 17 Oct 2007 09:09:35 +0200
Message-ID: <F8B9B12641FB6B4FAC761BDEF9D63B040EE5D4BD@tlv-ex-01.adprod.bmc.com> (raw)
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 succeeds.
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@bmc.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Yuval_Sofer@bmc.com
Subject: Re: when inserting to table, text type parameter become NULL (after big assignment to this parameter)
In-Reply-To: <F8B9B12641FB6B4FAC761BDEF9D63B040EE5D4BD@tlv-ex-01.adprod.bmc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox