PL/Perl Functions for Getting number of segments and Executing the Shell Commands

I was going through the one of user posting over EnterpriseDB Forum, on which user has asked if there is any function exists in PostgreSQL/Postgres Plus Advanced Server, which can be use for Finding the number of segments of a table/relation on server.

So, I thought to write about it. Currently, PostgreSQL/Postgres Plus Advanced Server doesn't come with any such function. However, if some one wants, he can write a plperl function which can find the number of segments of a table for him, as I have done in following sample code:

Perl Program to get the number of segments of a table:

CREATE OR REPLACE FUNCTION get_number_of_segments(text,text) returns  table(tablename text, segments int)   
as   
$$  
 my $sql = "select 'ls -1 '||case reltablespace when 0 then setting||'/base/'||pg_database.oid||'/'||relfilenode||'.*|grep -v vm|grep -v fsm|wc -l' else (select spclocation||'/'||pg_database.oid||'/'||relfilenode||'.*|grep -v vm|grep -v fsm|wc -l'  from pg_tablespace where pg_class.reltablespace=pg_tablespace.oid) END as location from pg_class, pg_database,pg_settings,pg_namespace where name='data_directory' and relname='$_[0]' and datname=current_database() and pg_namespace.oid=pg_class.relnamespace and nspname='$_[1]';"; 
    my $relname = $_[1].".".$_[0]; 
  my $rv = spi_exec_query($sql); 
  my $nrows = $rv->{processed}; 
  if ($nrows == 0) {
   return_next{'tablename' => $relname , 'segments' =>0};
   return undef;
}
  my $cmd = $rv->{rows}[0];  
  my $command = $cmd -> {location};  
  open(CMD, "$command |");  
    $count = ;  
  close(CMD);  
    @count=split(/[\n\r]+/,$count);  
  
  return_next {'tablename' => $relname , 'segments' => $count[0]+1};  
  return;  
$$ language plperlu;

Example of usage:

select * from get_number_of_segments('test2','enterprisedb');                                                                                                                                    
     tablename      | segments 
--------------------+----------
 enterprisedb.test2 |        1
(1 row)

Similarly some times people also ask about a function which can execute Shell Command connecting to database. Following is a sample code which can be use for executing shell command using plperl function.

Pl Perl Program to execute Shell Command:

CREATE OR REPLACE FUNCTION execute_shell(text) returns setof text
as
$$
$output=`$_[0] 2>&1`;
@output=split(/[\n\r]+/,$output);
foreach $out (@output)
{ return_next($out);
}
return undef;
$$ language plperlu;

Example:

edb=# select execute_shell('ls -ltr');
                               execute_shell                               
---------------------------------------------------------------------------
 total 100
 -rw------- 1 enterprisedb enterprisedb     4 Apr 12 11:18 PG_VERSION
 drwx------ 2 enterprisedb enterprisedb  4096 Apr 12 11:18 pg_twophase
 drwx------ 4 enterprisedb enterprisedb  4096 Apr 12 11:18 pg_multixact
 -rw------- 1 enterprisedb enterprisedb  1631 Apr 12 11:18 pg_ident.conf
 drwx------ 2 enterprisedb enterprisedb  4096 Apr 12 11:18 pg_subtrans
 drwx------ 2 enterprisedb enterprisedb  4096 Apr 12 11:18 pg_clog
 drwx------ 3 enterprisedb enterprisedb  4096 Apr 12 11:18 pg_xlog
 drwx------ 7 enterprisedb enterprisedb  4096 Apr 12 11:21 base
 -rw------- 1 enterprisedb enterprisedb   963 Apr 13 11:54 server.key.org
 -rw------- 1 enterprisedb enterprisedb   887 Apr 13 11:54 server.key
 -rw------- 1 enterprisedb enterprisedb   749 Apr 13 11:54 server.csr
 -rw-r--r-- 1 enterprisedb enterprisedb 19474 Apr 13 11:55 postgresql.conf
 -rw------- 1 enterprisedb enterprisedb   928 Apr 13 11:58 server.crt
 -rw------- 1 enterprisedb enterprisedb    77 Apr 13 12:01 postmaster.opts
 -rw------- 1 enterprisedb enterprisedb  3452 Apr 13 12:29 pg_hba.conf
 drwx------ 2 enterprisedb enterprisedb  4096 Apr 16 02:47 pg_tblspc
 drwx------ 3 enterprisedb enterprisedb  4096 Apr 16 02:47 tblspc_1
 -rw------- 1 enterprisedb enterprisedb    55 Apr 16 04:32 postmaster.pid
 drwxr-xr-x 2 enterprisedb enterprisedb  4096 Apr 17 00:00 pg_log
 drwx------ 2 enterprisedb enterprisedb  4096 Apr 17 02:33 global
 drwx------ 2 enterprisedb enterprisedb  4096 Apr 17 13:20 pg_stat_tmp
(22 rows)
edb=# select execute_shell('ls -lt t');
                 execute_shell                  
------------------------------------------------
 ls: cannot access t: No such file or directory
(1 row)