CMDBuild Forum

Filter Class by Function with paramaters

Programming and Web Services
#1

I’m attempting to use a function to filter a class, however I do not want to make 10 different functions when the difference would only be a string.
I’d like to pass that string while setting the function in the row privileges. However my attempts were met with failure. Can anyone assist?

image

#2

Good day,

CREATE OR REPLACE FUNCTION grant_role_classname(IN “UserId” integer,IN “GroupId” integer, IN “ClassName” character varying, OUT “Number” integer)
RETURNS SETOF integer AS
$BODY$
BEGIN
RETURN QUERY EXECUTE
‘SELECT “Id”’ ||
’ FROM ’ || quote_ident($3) ||
’ WHERE’ ||
’ “User” = ’ ||
‘(Select “Username”’ ||
’ FROM “User”’ ||
’ WHERE’ ||
’ “Id” = ’ ||quote_literal($1) ||
‘)’;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
COMMENT ON FUNCTION grant_role_classname(integer, integer, character varying) IS ‘TYPE: function’;

filters by the classes the current user created.

However, im getting an error “java.lang.IllegalArgumentException: there must be at least one where clause” when selecting the “Relations” Tab.

#3

Unfortunately that does not let me pass the string I want. This is an example of what I did:

CREATE OR REPLACE FUNCTION _1test_function(IN "UserId" integer,IN "GroupId" integer, "ClassName" character varying, "MyString" character varying)
	RETURNS SETOF bigint AS
$BODY$
	SELECT T1."Id" FROM "Table1" AS T1 
	INNER JOIN "Table2" AS T2 ON T1."Attr1" = T2."Id" 
	LEFT OUTER JOIN "Table2" AS T3 ON T1."Attr2" = T3."Id"
	WHERE T2."Code" = $4 OR T3."Code" = $4;
$BODY$
	LANGUAGE sql VOLATILE;
COMMENT ON FUNCTION _1test_function(integer, integer, character varying, "MyString" character varying) IS 'TYPE: function';

I can execute:

select _1test_function(0,0, '1', 'Test');

And get the results I want, however using that in the UI does not work.