Using Postgress Functions to create Helical Reports

Database Support
, , , ,
#1

Hi Team,

I am using Helical Insight 5.2. I have successfully connected to my PostgreSQL database and created metadata and reports using the tables available.

Now, I have a requirement where I need to create reports based on functions that are already defined in my PostgreSQL database.

I am familiar with creating metadata and reports using tables, but I need help with using PostgreSQL functions instead.

Is it possible to create reports from PostgreSQL functions in Helical Insight?

If yes, how can I do that?

Thanks in advance!

#2

Hi,
This is a great question.

In general, most BI tools (including Helical Insight) are designed to work with read-only operations (i.e., SELECT statements), and not with stored procedures or functions that perform write operations like INSERT, UPDATE, or DELETE. This is primarily for security and stability reasons.

However, in the case of PostgreSQL, there is good news! PostgreSQL supports calling functions using SELECT * FROM function_name() syntax, which behaves like a read operation — and Helical Insight supports this for creating reports.

So yes, it is definitely possible to create reports from PostgreSQL functions, provided those functions are performing read-only operations.

Steps to use PostgreSQL functions in Helical Insight:

  1. Ensure your function returns a TABLE with clearly defined column names and types.
  2. Use a custom SQL view in your Helical Insight metadata to call the function.
  3. You can now use that view like a regular table while creating reports.

Example:

We tested this with PostgreSQL version 15.2 on Ubuntu.

Function used at Postgress level:

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; 
$$;

Now go to Helical Insight. At the metadata level go to Query, and put this kind of below query:

SELECT * FROM get_sample_data()

You can add this SQL as a custom view inside the metadata, and then proceed to create reports using it just like any regular table.

Let us know if you need help integrating this into your specific metadata!

**Best regards, **
Helical Insight Support Team