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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s