How to Call a PostgreSQL Function in Helical Insight Metadata?

Adhoc
, , ,
#1

Hello Team,

How can we call a PostgreSQL function from Helical Insight 6.1 GA, especially within metadata views? Is it supported, and what is the correct way to use it?

Thanks,
Vema.

#2

Hello,

Yes, Helical Insight supports calling PostgreSQL functions directly within metadata view queries, and it works as expected.

Tested Environment

  • PostgreSQL Version: 15.2

Sample Function

Below is a sample PostgreSQL function that was created and tested:

CREATE OR REPLACE FUNCTION get_sample_data()
RETURNS TABLE(id INT, name TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT * FROM (VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie')
) AS t(id, name);
END;
$$;

How to Call the Function

You can directly call the function inside your metadata view /canned report query like this:

SELECT * FROM get_sample_data();

  • PostgreSQL functions that return TABLE format behave like normal tables

  • Helical Insight treats them as a query result set

  • You can use them in:

    • Metadata creation
    • Reports
    • Dashboards

  • Function must return a table structure (RETURNS TABLE )

  • Ensure proper permissions for the database user

  • Avoid functions with side effects (read-only logic)

Result

  • Function executes successfully
  • Data is available for metadata and reporting
  • Works just like querying a normal table

This makes PostgreSQL functions a powerful way to encapsulate logic and reuse it within Helical Insight.

Thank You,
Helical Insight