Skip to main content

Functions and Procedures

Devii supports calling database functions or stored procedures (referred to collectively as 'routines' hereafter) through the query endpoint. Routine exports are subject to policy like any other API object exported by Devii. By creating routines and then exporting them, you can have complex logic internal to the database exposed as API calls, without sacrificing security.

Before you can export it in Devii, the routine must already exist in your database. The database user used to connect must have permission to call the routine, as well.

Caveats and Restrictions​

Not all database routines are suitable for exporting to call in this way. For instance, aggregate functions cannot be exported; to use aggregates in queries, use the Aggregate Functions facility.

For stored procedures, the procedure must either use at least one OUT (or INOUT) parameter to return a value, or issue a query that returns rows.

The exact restrictions on what routines can be exported vary by RDBMS platform.

PostgreSQL​

Aggregate and window functions cannot be exported, but there are no other restrictions. User-defined functions are the preferred means of returning result sets in PostgreSQL. If you use OUT or INOUT parameters in a PostgreSQL function, make sure that the parameter names match those used in output_fields.

Because stored procedures must generate a value for returning, PostgreSQL stored procedures need at least one output parameter to receive that value.

MySQL​

MySQL functions may only return a scalar value. Keeping this in mind, output_fields must only have one key-value pair for a MySQL function.

Stored procedures in MySQL may return result sets. In this case, the output_fields keys must match the columns in the result set. If a stored procedure uses OUT parameters, output_fields keys must match the OUT parameter names.

MS SQL Server/Azure SQL​

When setting arguments and output_fields, omit the "@" from the name. So, if a function had a parameter named "@radius" of type float, the arguments should be set as such: {"radius": "Float!"}.

MSSQL functions may be scalar or table-valued. A scalar function's output_fields must have one key-value pair. The key, in this case, is not required to match the name of the function, but may be any meaningful label. For table-valued functions, the keys in output_fields must match the column names.

For stored procedures, the main caveat is that T-SQL cursors may not be used with output parameters. T-SQL cursors are not compatible with Devii, so if you have a stored procedure that outputs a cursor, create a wrapper procedure.

Export Routine to Devii​

To use your database routine in the API endpoint, you must first export it into Devii using the roles_pbac endpoint and the create_function_export mutation.

Export Fields​

  • callable_name - Required. Must match the exact routine name in your database, including the schema. (e.g. public.function_name). If the routine is built-in, however, it must not be schema-qualified.
  • callable_type - Required. Must be Function for functions, or Procedure for stored procedures.
  • description - Short description of the database routine that will appear in generated GraphQL schema documentation under the Query Functions.
  • associated_table - Required if you wish to use policy with filters to gate access to this routine. Table associated with the routine that will be used to create policy rules. This table will be queried using policy filters, and allow the call to proceed only if the filter matches a record.
    • Policy filter expressions may use $_ARGS to reference the routine parameters.
      • Examples:
        • If your routine includes a parameter named roleid that should match their tenant roleid, you can write this expression: $_ARGS.roleid = $_PRINCIPAL.roleid.
        • If your routine includes a parameter named status, which must match a column on the associated table, use an expression like this: status = $_ARGS.status.
  • arguments- Required. The parameters of the routine. JSON object, where the keys are the parameter names, and values are the parameter types.
    • Parameter names MUST match the parameter names in the routine definition.
    • The type MUST be a GraphQL type (e.g. String, Int, etc.). If the parameter is required, the type MUST append "!" to indicate this in the GraphQL API.
    • Parameters MUST be ordered in the same order as in the routine definition.
  • output_fields - Required. JSON object that describes the return value for the routine.
    • If the routine returns a single scalar value, the JSON object must be a single key-value pair: key is the name of the returned field (see platform-specific info for requirements), value is the GraphQL type of the returned output (e.g. String, Int, etc.).
    • For routines that return a record, the JSON object must reflect the record structure: keys must be the names of the output columns, with values being the GraphQL types.

Policy Rules for Routines​

Devii uses the same Policy-Based Access Control security engine to control access to routines as it does for tables. There are only three special considerations when writing policy for routine calls.

  • The only supported operation is select, even for routines with side-effects.
  • The target of the policy rule must be the routine name, schema-qualified if necessary (depending on your RDBMS).
  • A routine must have an associated table in order to use filter expressions in policy. The table in question will be queried using the expression, and access will be permitted if any rows are returned.

Calling a Routine​

Routines are called via GraphQL queries through the query endpoint. All exported routines will appear in the GraphQL schema under the query object, in the subobject named Functions. Stored procedures are not "functions" per se, but still appear under the Functions subobject, as they are called identically from the GraphQL API.

When calling the routine, the parameters must be provided in the same order as defined in the database routine. The arguments may be supplied inline in the query text, or as variables, though variables are always best practice.

Examples​

PostgreSQL​

Simple Function​

This simple function, square, computes the square of the input number. Useful mostly as an example; you would not want to export a function like this for API calls, when a client could easily compute it locally.

With this function definition:

CREATE OR REPLACE FUNCTION public.square(
	x numeric)
    RETURNS numeric
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE PARALLEL SAFE
AS $BODY$
select x * x;
$BODY$;

The resulting fields in the export object would be:


{
  "callable_name": "public.square",
  "callable_type": "function",
  "arguments": {"x": "Float!"},
  "output_fields": {"square": "Float"},
  "description": "Computes and returns the square of the given number.",
  "associated_table": null
}

In this case, with no associated_table, policy could not use a filter to gate this function's arguments. For a simple mathematical function, this is not an issue.

The resulting GraphQL schema fragment:

type Functions {
  ...

  """
  Computes and returns the square of the given number.
  """
  square(x: Float!): GenericScalar

  ...
}

A call to this function, and its result:

GraphQLJSON Response
{
  Functions {
    square(x: 5)
  }
}
{
  "data": {
    "Functions": {
      "square": {
        "square": 25
      }
    }
  }
}

Function with IN and OUT parameters​

This function, sum_and_product, demonstrates the use of IN and OUT parameters in a PostgreSQL function.

Function definition:

CREATE OR REPLACE FUNCTION public.sum_and_product(
	x numeric,
	y numeric,
	OUT sum numeric,
	OUT product numeric)
    RETURNS record
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE PARALLEL SAFE
AS $BODY$
select x + y, x * y;
$BODY$;

Export object:

{
  "callable_name": "public.sum_and_product",
  "callable_type": "function",
  "arguments": {"x": "Float!", "y": "Float!"},
  "output_fields": {"sum": "Float", "product": "Float"},
  "description": "Computes the sum and product of the input numbers and returns them."
}

GraphQL schema fragment:

type Functions {
  ...
  """
  Computes the sum and product of the input numbers and returns them.
  """
  sum_and_product(x: Float!, y: Float!): GenericScalar
  ...
}

Call and its result:

GraphQLJSON Response
{
  Functions {
    sum_and_product(x: 2, y: 3)
  }
}
{
  "data": {
    "Functions": {
      "sum_and_product": {
        "product": 6,
        "sum": 5
      }
    }
  }
}

Function returning a result set​

Defined in the chinook test database, this function returns a list of artists (from the public.artist table) who have songs in a particular genre, identified by the gid parameter.

Function definition:

CREATE OR REPLACE FUNCTION public.get_artists_by_genre(
	gid integer)
    RETURNS TABLE(artistid integer, name text, genreid integer, genre text) 
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL SAFE 
    ROWS 1000

AS $BODY$
select distinct artistid, artist.name, genreid, genre.name as genre 
    from public.artist  
    join public.album using (artistid) 
    join public.track using (albumid)
    join public.genre using (genreid)
    where track.genreid = gid
    order by name;
$BODY$;

Export object:

{
"callable_name": "public.get_artists_by_genre",
"callable_type": "function",
"arguments": {
	"gid": "Int!"
	},
"output_fields": {
	"artistid": "Int", 
	"name": "String", 
	"genreid": "Int", 
	"genre": "String"
	},
"associated_table": "public.artist",
"description": "Returns a set of artists with tracks in a given genre."
}

GraphQL schema fragment:

type Functions {
  ...
  """
  Returns a set of artists with tracks in a given genre.
  """
  get_artists_by_genre(gid: Int!): GenericScalar
  ...
}

Call and its result:

GraphQLJSON Response
{
  Functions {
    get_artists_by_genre(gid: 8)
  }
}
{
  "data": {
    "Functions": {
      "get_artists_by_genre": [
        {
          "artistid": 19,
          "genre": "Reggae",
          "genreid": 8,
          "name": "Cidade Negra"
        },
        {
          "artistid": 100,
          "genre": "Reggae",
          "genreid": 8,
          "name": "Lenny Kravitz"
        },
        {
          "artistid": 151,
          "genre": "Reggae",
          "genreid": 8,
          "name": "UB40"
        }
      ]
    }
  }
}

Stored procedure​

This stored procedure computes and returns the factorial of a number, using an INOUT parameter. Again, this is only useful as an example.

Procedure definition:

CREATE OR REPLACE PROCEDURE public.do_factorial(
	INOUT n numeric)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
	n := factorial(n::bigint);
END;
$BODY$;

Export object:

{
  "callable_name": "public.do_factorial",
  "callable_type": "procedure",
  "arguments": {"n": "Int!"},
  "output_fields": {"n": "Float"},
  "description": "Computes and returns the factorial of the given number."
}

GraphQL schema fragment:

type Functions {
  ...
  """
  Computes and returns the factorial of the given number.
  """
  do_factorial(n: Int!): GenericScalar
  ...
}

Call and its result:

GraphQLJSON Response
{
  Functions {
	do_factorial(n: 10)
  }
}
{
  "data": {
    "Functions": {
      "do_factorial": {
        "n": 3628800
      }
    }
  }
}

MySQL​

Function export​

MySQL functions may only have IN parameters, and may only return scalar values. Here is the square example, as provided above, in MySQL.

Function definition:

CREATE FUNCTION `testing`.`square`(x numeric) RETURNS decimal(10,0)
BEGIN
	return x * x;
END

This example otherwise works the same as for the PostgreSQL square, except that the name of the callable is testing.square. The GraphQL schema fragment, call syntax, and response are identical.

Stored procedure​

Defined in the chinook test database, this procedure is a MySQL implementation of the PostgreSQL get_artists_by_genre function provided in the previous section. MySQL must define routines that return result sets as procedures, not functions.

Procedure definition:

CREATE PROCEDURE `chinook`.`get_artists_by_genre`(in gid int)
begin
    select distinct ArtistId, artist.Name, GenreId, genre.Name as Genre from chinook.artist 
    join chinook.album using (ArtistId) 
    join chinook.track using (AlbumId)
    join chinook.genre using (GenreId)
    where GenreId = gid
    order by Name;
END

Export object:


"i": {
	"callable_name": "chinook.get_artists_by_genre",
	"callable_type": "procedure",
	"arguments": {
		"gid": "Int!"
	},
	"output_fields": {
		"artistid": "Int", 
		"name": "String", 
		"genreid": "Int", 
		"genre": "String"
	},
	"associated_table": "chinook.artist",
	"description": "Returns a set of artists with tracks in a given genre."
}

This example otherwise works the same as for the PostgreSQL example.

MS SQL Server/Azure SQL​

All of the examples for MS SQL Server are defined using the AdventureWorks sample database provided by Microsoft for testing and examples.

Scalar-valued function​

This function calculates the area of a circle given its radius.

Function definition:

CREATE FUNCTION dbo.calculate_circle_area (@radius FLOAT)
RETURNS FLOAT
AS
BEGIN
    DECLARE @Area FLOAT
    SET @Area = PI() * POWER(@radius, 2)
    RETURN @Area
END

Export object:

{
  "callable_name": "dbo.calculate_circle_area",
  "callable_type": "function",
  "arguments": {"radius": "Float!"},
  "output_fields": {"area": "Float"},
  "description": "Calculates the area of a circle with given radius."
}

GraphQL schema fragment:

type Functions {
  ...
  """
  Calculates the area of a circle with given radius.
  """
  calculate_circle_area(radius: Float!): GenericScalar
  ...
}

Call and its result:

GraphQLJSON Response
{
  Functions {
    calculate_circle_area(radius: 1)
  }
}
{
  "data": {
    "Functions": {
      "calculate_circle_area": {
        "area": "3.141592653589793"
      }
    }
  }
}

Table-valued function​

This function returns the cities from Application.Cities within the state in Application.StateProvinces with the given two-letter abbreviation. It uses the LIKE operator for case-insensitive matching.

Function definition:

CREATE FUNCTION dbo.GetCitiesForStateProv (@statecode nvarchar(5))
RETURNS TABLE
AS 
RETURN (SELECT 
CityID, CityName, c.StateProvinceID, sp.StateProvinceCode, StateProvinceName
FROM Application.Cities c 
join Application.StateProvinces sp 
on (c.StateProvinceID = sp.StateProvinceID)
WHERE sp.StateProvinceCode like @statecode)

Export object:

{
  "callable_name": "dbo.GetCitiesForStateProv",
  "callable_type": "function",
  "arguments": {
	  "statecode": "String!"
	},
  "output_fields": {
	  "CityId": "Int", 
	  "CityName": "String", 
	  "StateProvinceId": "Int", 
	  "StateProvinceCode": "String", 
	  "StateProvinceName": "String"
	},
  "description": "Returns the city records for the given two-letter state code in the Applications.Cities table.",
  "associated_table": "Application.StateProvinces"
}

GraphQL schema fragment:

type Functions {
  ...
  """
  Returns the city records for the given two-letter state code in the Applications.Cities table.
  """
  GetCitiesForStateProv(statecode: String!): GenericScalar
  ...
}

Call and its result:

GraphQLJSON Response
{
  Functions {
    GetCitiesForStateProv(statecode: "DC")
  }
}
{
  "data": {
    "Functions": {
      "GetCitiesForStateProv": {
        "CityId": 35980,
        "CityName": "Washington",
        "StateProvinceCode": "DC",
        "StateProvinceId": 9,
        "StateProvinceName": "District of Columbia"
      }
    }
  }
}

Stored procedure​

This procedure computes the sum population of the countries within the given region (Africa, Americas, Asia, Europe, or Oceania), using the table Application.Countries. It uses LIKE for case-insensitive matching. This would more usefully be done with a function, but it illustrates the use of OUT parameters in a stored procedure.

Procedure definition:

CREATE PROCEDURE dbo.PopByRegion
   @region nvarchar(30),
   @pop bigint out
AS 
SET @pop = (SELECT sum(LatestRecordedPopulation)
            FROM Application.Countries c
            WHERE c.Region like @region);

Export object:

{
  "callable_name": "dbo.PopByRegion",
  "callable_type": "procedure",
  "arguments": {"region": "String!"},
  "output_fields": {"population": "Bigint"},
  "description": "Sums and returns the population of a given region of countries.",
  "associated_table": "Application.Countries"
}

GraphQL schema fragment:

type Functions {
  ...
  """
  Sums and returns the population of a given region of countries.
  """
  PopByRegion(region: String!): GenericScalar
  ...
}

Call and its result:

GraphQLJSON Response
{
  Functions {
    PopByRegion(region: "Asia")
  }
}
{
  "data": {
    "Functions": {
      "PopByRegion": {
        "pop": 4119726508
      }
    }
  }
}