Need to define a cube based on a Postgre sql User defined function with string parameter

[midhunjohn11]

Problem

I have a requirement to create a cube that is based on an existing PostgreSQL user-defined function with a string parameter as input. And the parameters will be supplied from the Rest API call to Cube.js. Is it possible to build such a cube? Or what's the best way to implement it?

Below is a sample code I tried but it gives me an error "Referencev_value is not defined."

cube(New1, { sql: => SELECT * from test.ufn_function(${v_value}) ,
dimensions: { is_total: { sql: id, type: number, primary_key: true, shown:true }, "name": { sql: "name", type: number } },
});

Related Cube.js-generated SQL

This is not getting generated due to an error.

Can someone help me here? Or guide me with proper way to create a cube using user-defined functions with parameters

[midhunjohn11]

@paveltiunov Can you help me on this?

[midhunjohn11]

I found a way to do this, but not sure if this is the right approach. I have changed the cube definition like below and send the params as payload in API call

cube('New1', { sql: () => SELECT * FROM test.ufn_function(${SECURITY_CONTEXT.type.unsafeValue()}), dimensions: { is_total: { sql: 'id', type: 'number', primary_key: true, shown: true }, "name": { sql: '"name"', type: 'number' } }, });

[igorlukanin]

Hi @midhunjohn11 👋

Could you please zoom out and provide more context here? What is that user-defined function doing? Why would you want to pass parameters to it?

I found a way to do this

Please see the docs: https://cube.dev/docs/reference/data-model/context-variables#security_context. As mentioned there, using SECURITY_CONTEXT in cube definitions is deprecated. It would basically stop working in future versions of Cube.

[midhunjohn11]

hi @igorlukanin Thanks for the response. The PoC we are trying to achieve is to deploy Cube.Dev as a semantic layer between high charts and postgre DB. And the above user defined function is at the Postgresql DB which does some complex logic. So I'm trying to create a cube in the Cube.Dev on top of this user defined function, with the value being passed dynamically. I am not sure whether this is a right approach or not. I might be wrong here. Can you suggest anything on this? And keep in mind that we need to pass the user defined function logic results to the High charts to build reports.

[igorlukanin]

Could you please elaborate on that value that is passed as an argument to the function? What is the range of values, how many unique values are out there, etc.

If you really want to use the approach that you outlined above, please switch to COMPILE_CONTEXT and configure multitenancy accordingly: https://cube.dev/docs/reference/data-model/context-variables#compile_context