CMDBuild Forum

DataSource CMDBuild

Old Versions
#1

Hi, i have this query to set in a dataSource ( PostgresFunction )

Select
       ( Select Count(*) from "Compte" where "ArchiPle" = 23 and "Status" = 'A') AS "CS",
       ( Select Count(*) From "Compte" Where "ArchiPle" = 22 and "Status" = 'A') AS "NG", Count(*) AS "E5"
From  
    "Compte"
Where 
    "ArchiPle" = 25 and "Status" = 'A'

I tryied to follow the example in the manual but it seems to be a little complicated


Can i have the core synthax to interact the PostgresFunction with CMDBuild


Thanks

Paul


#2

Hi, the sample functions in the Administrator Manual are written in a more complicated way (as “EXECUTE” of a SQL string) in order to make parametric the name of the class from which to extract the data.
If you do not have this need you can write the function more easily.

For example, this query extracts the list of different brands with the number of assets for each brand:
    SELECT “LookUp”.“Description”::character varying AS “Brand”, COUNT(*)::integer AS “CardCount”   
    FROM “Asset”    
    LEFT OUTER JOIN “LookUp” on “LookUp”.“Id” = “Asset”.“Brand” and “LookUp”.“Status” = ‘A’    
    WHERE “Asset”.“Status” = ‘A’    
    GROUP BY “LookUp”.“Description”   
    ORDER BY “LookUp”.“Description”;
Remember to add the comment:
    COMMENT ON FUNCTION xy() IS ‘TYPE: function’;
Using this query as a data source, you can configure an histogram graph.

We suggest that you do not use numeric "Id"s in queries, but to put in join the decoding table (like “LookUp” in the query above).

CMDBuild Team


 

#3

Previously Tecnoteca wrote:

Hi, the sample functions in the Administrator Manual are written in a more complicated way (as "EXECUTE" of a SQL string) in order to make parametric the name of the class from which to extract the data.
If you do not have this need you can write the function more easily.

For example, this query extracts the list of different brands with the number of assets for each brand:
    SELECT "LookUp"."Description"::character varying AS "Brand", COUNT(*)::integer AS "CardCount"   
    FROM "Asset"    
    LEFT OUTER JOIN "LookUp" on "LookUp"."Id" = "Asset"."Brand" and "LookUp"."Status" = 'A'    
    WHERE "Asset"."Status" = 'A'    
    GROUP BY "LookUp"."Description"   
    ORDER BY "LookUp"."Description";
Remember to add the comment:
    COMMENT ON FUNCTION xy() IS 'TYPE: function';
Using this query as a data source, you can configure an histogram graph.

We suggest that you do not use numeric "Id"s in queries, but to put in join the decoding table (like "LookUp" in the query above).

CMDBuild Team


Thanks for the responce, i solved my problem, I do not know the syntax for postgresql functions. and here is the query

CREATE OR REPLACE FUNCTION cmf_count_active_comptes(OUT "Architecture" character varying, OUT "Number" integer)

RETURNS SETOF record AS

$BODY$
    BEGIN
    RETURN QUERY EXECUTE   
    'SELECT '||quote_literal('CS')||'::character varying AS "Arcitecture" ,Count(*)::integer AS "Number" from "Compte" where "ArchiPle" = 23 and "Status" =  '||quote_literal('A')||'
    UNION ( SELECT '||quote_literal('NG')||'::character varying AS "Arcitecture",Count(*)::integer AS "Number" from "Compte" where "ArchiPle" = 22 and "Status" =  '||quote_literal('A')||' )    
    UNION  SELECT '||quote_literal('E5')||'::character varying AS "Arcitecture",Count(*)::integer AS "Number" from "Compte" where "ArchiPle" = 25 and "Status" =  '||quote_literal('A') ;
    END
$BODY$

LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION cmf_count_active_comptes() OWNER TO postgres;
COMMENT ON FUNCTION cmf_count_active_comptes() IS 'TYPE: function';

Select Count(*) from "Solution" where "IdClass" = '"PleiadesCS"'::regclass and "Status" = 'A'

 

Regards,