pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedwhen inserting to table, text type parameter become NULL (after big assignment to this parameter)
3+ messages / 3 participants
[nested] [flat]
* when inserting to table, text type parameter become NULL (after big assignment to this parameter)
@ 2007-10-16 11:56 Sofer, Yuval <Yuval_Sofer@bmc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sofer, Yuval @ 2007-10-16 11:56 UTC (permalink / raw)
To: pgsql-admin
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@bmc.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: when inserting to table, text type parameter become NULL (after big assignment to this parameter)
@ 2007-10-16 16:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sofer, Yuval <Yuval_Sofer@bmc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-10-16 16:50 UTC (permalink / raw)
To: Sofer, Yuval <Yuval_Sofer@bmc.com>; +Cc: pgsql-admin
"Sofer, Yuval" <Yuval_Sofer@bmc.com> writes:
> I am trying to get server parameters (pg_settings) using a cursor and
> insert them to one column table.
> The problem is that the string (the parameter value) is not inserted to
> the table.
> When selecting, this column shows NULL value.
Hm, your example works fine for me in every current 8.x release.
What PG version is this exactly?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: when inserting to table, text type parameter become NULL (after big assignment to this parameter)
@ 2007-10-17 10:36 Moiz Kothari <moizpostgres@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Moiz Kothari @ 2007-10-17 10:36 UTC (permalink / raw)
To: Sofer, Yuval <Yuval_Sofer@bmc.com>; +Cc: pgsql-admin@postgresql.org, "Tom Lane" <tgl@sss.pgh.pa.us>
It worked fine for me too.
Regards,
Moiz Kothari
On 10/16/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Sofer, Yuval" <Yuval_Sofer@bmc.com> writes:
> > I am trying to get server parameters (pg_settings) using a cursor and
> > insert them to one column table.
> > The problem is that the string (the parameter value) is not inserted to
> > the table.
> > When selecting, this column shows NULL value.
>
> Hm, your example works fine for me in every current 8.x release.
> What PG version is this exactly?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Hobby Site : http://dailyhealthtips.blogspot.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-10-17 10:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-16 11:56 when inserting to table, text type parameter become NULL (after big assignment to this parameter) Sofer, Yuval <Yuval_Sofer@bmc.com>
2007-10-16 16:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-17 10:36 ` Moiz Kothari <moizpostgres@gmail.com>
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