Friday, April 29, 2011

Get Number of Segments in PostgreSQL 9.0 (PL/Perl)

In my Blog on get_number_of_segments, I had used the simple query to find the number of segments.
Since, from PostgreSQL 9.0, system admin function pg_relation_filepath can be use to find the location of relfilenode, therefore I thought to reduce the code of get_number_function and use the pg_relation_filepath function.
Following is a modified plperl function get_number_of_segments(text) using system admin function pg_relation_filepath(relation reglcass):
CREATE OR REPLACE FUNCTION get_number_of_segments(text) returns  table(tablename text, segments int)     
as     
$$    
 my $sql = spi_prepare("select 'ls -1 '||pg_relation_filepath(\$1)||'.*' as cmd",'TEXT');
 my $q = spi_query_prepared($sql,$_[0]);     
 my $rv = spi_fetchrow($q);   
  my $cmd = $rv->{rows}[0];    
  my $command = $cmd -> {location};    
  open(CMD, "$command |");    
    $count = ;    
  close(CMD);    
    @count=split(/[\n\r]+/,$count);    
    
  return_next {'tablename' => $_[0] , 'segments' => $count[0]+1};  
  spi_freeplan($sql);  
  return undef;    
$$ language plperlu;  

Some Examples:
psql -c " select * from get_number_of_segments('public.test');"
  tablename  | segments 
-------------+----------
 public.test |        1
(1 row)

psql -c " select * from get_number_of_segments('public.testa');"
ERROR:  relation "public.testa" does not exist at line 4.
CONTEXT:  PL/Perl function "get_number_of_segments"

No comments:

Post a Comment