use postgres function to get clustername

Question

I know this is probably more of a dev type question, but I'm asking anyways. Has anyone figured out a solid way of being able to query the clustername of an aurora postgres cluster inside a function?

I can pass the :'HOST' psql variable to a function and get the results needed, but I'd like to get this embedded into the function itself.

This is how I pass it into the function 
SELECT func_name(:'HOST','value2');

I've tried something like the following in the function and it doesn't work. It does not like that ":" at the beginning of the variable.

select :'HOST' into v_host;

I've see some custom extensions that do this work, but Aurora PG won't allow for these custom extensions.

Thoughts? Any advice?

thanks in advance!

Accepted Answer

1) In order to fetch the respective instance name (reader or writer) to which you are connected to, you can use below :- (please note you have to connect using cluster endpoint in order to get cluster name)

[userr@machine]$ psql -h database-3.cluster-xxxxx.region.rds.amazonaws.com -U postgres -d postgres

postgres=> SELECT :'HOST';
--------------------------------------------------------------
database-3.cluster-xxxxx.region.rds.amazonaws.com

-- The above output can be further trimmed to just have cluster name

postgres=> SELECT SPLIT_PART(:'HOST','.',1);
-----------------------------------
database-3

-- To use above as variable in function , following workaround can be used as ":" is considered as variable in the psql.

postgres=>create table host_name_tab (ht varchar);
postgres=> insert into host_name_tab select :'HOST';

postgres=> create or replace function get_host()
        returns varchar
       language plpgsql
      as
      $$
     declare
     host_name varchar;
begin
   SELECT SPLIT_PART(ht,'.',1) into host_name from host_name_tab ;
   return host_name;
end;
$$;

postgres=>  select get_host();
  get_host
------------
 database-3
(1 row)

2)  If you which to get the instance name you are connected to you can use below :

postgres=>  select aurora_db_instance_identifier() as host_name;
             host_name
-----------------------------------
 database-3-instance-1-ap-south-1a
(1 row)

Please note as this is development related query it can be achieved by different logic.

use postgres function to get clustername

postgres=> SELECT :'HOST';

postgres=> SELECT SPLIT_PART(:'HOST','.',1);

postgres=> select get_host(); get_host

postgres=> select aurora_db_instance_identifier() as host_name; host_name

関連するコンテンツ