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):
Some Examples:
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"
Comments
Post a Comment