I checked through many sources but cudnt get result.
How to create a function in vertica to return count of all sessions in database?
Can someone povide some idea or possible expamples in this topic.
You can create UDX in Vertica, but it does not have a session handle so you can only access data exposed via the API. Session data is not exposed.
You'll need to execute a query to do it.
SELECT COUNT(*) FROM sessions;
See this post for something similar:
SELECT
node_name
,user_name
,'SELECT CLOSE_SESSION(''' || session_id || ''');' AS CloseSession
,statement_start
,(GETDATE() - statement_start)::INTERVAL AS current_statement_duration
,REGEXP_REPLACE(current_statement,'[rnt]',' ') AS current_statement
,session_id
,transaction_id
,statement_id
,client_hostname
,client_os
,login_timestamp
,runtime_priority
,ssl_state
,authentication_method
,transaction_start
,GETDATE() AS Today
FROM v_monitor.sessions ORDER BY current_statement_duration DESC ;
Also you can get more info from this post: Script to List Vertica Active Sessions