Mastering PostgreSQL 10

Sharing data across function calls

Sometimes, it is necessary to share data across calls. The infrastructure has means to actually do that. In Perl, a hash can be used to store whatever data is needed:

CREATE FUNCTION perl_shared(text) RETURNS int AS 
$$ 
if ( !defined $_SHARED{$_[0]} ) 
{ 
  $_SHARED{$_[0]} = 0; 
} 
else 
{ 
  $_SHARED{$_[0]}++; 
} 
return $_SHARED{$_[0]}; 
$$ LANGUAGE 'plperl'; 

The $_SHARED variable will be initialized with 0 as soon as we figure out that the key passed to the function is not there yet. For every other call, 1 is added to the counter, leaving us with the following output:

test=# SELECT perl_shared('some_key') FROM  generate_series(1, 3);  perl_shared 
------------- 
         0 
         1 
         2 
(3 rows) 

In case of a more complex statement, the ...