pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
when 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>
  2007-10-16 16:50 ` Re: when inserting to table, text type parameter become NULL (after big assignment to this parameter) Tom Lane <tgl@sss.pgh.pa.us>
  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 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   ` Re: when inserting to table, text type parameter become NULL (after big assignment to this parameter) Moiz Kothari <moizpostgres@gmail.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-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 ` Re: when inserting to table, text type parameter become NULL (after big assignment to this parameter) Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-10-17 10:36   ` Moiz Kothari <moizpostgres@gmail.com>
  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