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)
 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 = <cmd>;

  return_next {'tablename' => $_[0] , 'segments' => $count[0]+1};
  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"

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s