CMDBuild Forum

SQL Views (adding a data source)

Old Versions
#1

I'm trying to add a data source to be used when creating SQL Views.

 

For this I have created, directly in the database, the following function:

create or replace FUNCTION cmf_certs_expiration_watch(OUT "Code" character varying, OUT 

 

"ExpirationDate" date)

RETURNS SETOF record AS

$BODY$

BEGIN

 RETURN QUERY EXECUTE

'select "Code", "ExpirationDate"

 from "SSLCertificates" where "ExpirationDate" - integer' ||

quote_literal (180) ||

' > current_date';

END

$BODY$

 LANGUAGE plpgsql VOLATILE

 COST 100;

COMMENT ON FUNCTION cmf_certs_expiration_watch() IS 'TYPE: function';

 

I have tested teh function from pslq and it works perfectly well with the class I want, but I cannot get it to appear in the list of data source. Iread about a bug in 2.1, but I'm using 2.3.

Could somebody please point to me where do I have to look? Thanks,

mari

#2

I suggest you to go in the Administration Module and click on the “Clear Cache” button in the Setup menu - Server Management.
I suggest you also to logout and login in the application, or to clean your browser’s cache.
CMDBuild Team

#3

Thank you very much. I had done the clear cache with no results. I tried with an incognito window and it did the trick.

 
ciao,
mari
#4
I have another problem related to this. I would like to create a view, based on SQL since the where condition includes date manipulation (it's a query which result will change depending on current date).
Since I'm not able to create it using Views based on filters, I used a function created in PostgreSQL. However, I have lost the ability of  navigating all the "features"of the underlying class. Is it related with the type of the output? How could I be able to navigate from a SQL View to the class that generates it? Thanks you!

mari

 

#5

Yes, it is related with the SQL type of the view.

You can use the quick search function, you can export the data in CSV format but you can not navigate from a SQL View to the class that generates it.

CMDBuild Team