[SQL] array handling on 8.0.3
Hi,
I have to run a function on a 9.0.3 server. Following part
(of a bigger function):
create or replace function t(p varchar) returns void as
$$
declare
v varchar; f varchar; a varchar[]; i integer;
begin
a:= '{}';
if p like '%/%' then
v:= trim_null(split_part(p, '/', 1));
f:= v; a:= a||v; i:= 2;
while v != '' loop
v:= trim_null(split_part(p, '/', i));
raise INFO 'splitted=%',v;
a:= a||v; i:= i+1;
raise INFO 'a=%',a;
end loop;
raise INFO 'a=% ready',a;
else
f:= p;
end if;
return;
end;
$$
language 'plpgsql' volatile;
raises (if called as t('a/b')):
INFO: splitted=b
INFO: a={a,b}
INFO: splitted=
INFO: a=
INFO: a= ready
It looks that value of the array `a' is lost after the loop. Is this
normal? What do I do wrong?
Daniel
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] finding unused indexes?
resurrecting an old thread: so is it safe to say that an index that has pg_stat_user_indexes.idx_scan, pg_stat_user_indexes.idx_tup_read, and pg_stat_user_indexes.idx_tup_fetch all equal to 0 has not been used (since stats have been reset)? i have a bunch of those and all of them have pg_statio_user_indexes.idx_blks_read > 0 and most of those have pg_statio_user_indexes.idx_blks_hit > 0. when/why would that happen? i guess i don't entirely understand those two values so an explanation would be very welcome (maybe an example of when each of the five values gets incremented. thanks! george > -Original Message- > From: Brad Nicholson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 01, 2006 9:12 AM > To: George Pavlov > Cc: [email protected] > Subject: Re: [SQL] finding unused indexes? > > On Tue, 2006-08-01 at 09:05 -0700, George Pavlov wrote: > > Anybody have a clever way to quickly find whether there are > any unused > > indexes in a PG DB? One way I have done is to take queries > from the DB > > log, prepend an explain to each and grep the results, but I > am wondering > > if there are either any index usage stats maintained > somewhere inside > > Postgres or if there is a slicker/less cumbersome way of > doing it. Also > > indexes used by functions are hard to simulate that way. > > > Check out pg_stat_user_indexes, pg_stat_sys_indexes and > pg_statio_all_indexes > > You can very clearly see the index usage there. You might > have to mess > with the statistics collector section in the postgresql.conf file in > order to collect the information. > > Brad. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] array handling on 8.0.3
On Tue, 9 Oct 2007, Daniel Drotos wrote: I have to run a function on a 9.0.3 server. Following part (of a bigger ^ I mean 8.0.3 sorry Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] array handling on 8.0.3
Hi, I found my bug, please forget my (stupid) question! Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
