Example procedure definitions, _describe_extfn API function code fragment, and SQL queries for getting EXTFNAPIV4_DESCRIBE_PARM_CAN_BE_NULL values.
Sample procedure definition used by the example queries in this topic:
CREATE PROCEDURE my_udf(IN p INT) RESULT (x INT) EXTERNAL NAME ‘my_udf@myudflib’;
Sample _describe_extfn API function code fragment used by the example queries in this topic:
my_udf_describe(a_v4_extfn_proc_context *cntxt) { if( cntxt->current_state > EXTFNAPIV4_STATE_OPTIMIZATION ) { a_sql_byte can_be_null = 0; a_sql_int32 ret = 0; ret = cntxt->describe_parameter_get( cntxt, 1, EXTFNAPIV4_DESCRIBE_PARM_CAN_BE_NULL, &can_be_null, sizeof(a_sql_byte) ); if( ret <= 0 ) { // Handle the error. } } }
This example creates a table with a single integer column without the NOT NULL modifier specified. The correlated subquery passes in column c1 from the table has_nulls. When the procedure my_udf_describe is called during the Execution state, the call to describe_parameter_get populates can_be_null with a value of 1.
CREATE TABLE has_nulls ( c1 INT ); INSERT INTO has_nulls VALUES(1); INSERT INTO has_nulls VALUES(NULL); SELECT * from has_nulls WHERE (SELECT sum(my_udf.x) FROM my_udf(has_nulls.c1)) > 0;
This example creates a table with a single integer column with the NOT NULL modifier specified. The correlated subquery passes in column c1 from the table no_nulls. When the procedure my_udf_describe is called during the Execution state, the call to describe_parameter_get populates can_be_null with a value of 0.
CREATE TABLE no_nulls ( c1 INT NOT NULL); INSERT INTO no_nulls VALUES(1); INSERT INTO no_nulls VALUES(2); SELECT * from no_nulls WHERE (SELECT sum(my_udf.x) FROM my_udf(no_nulls.c1)) > 0;
This example calls the procedure my_udf with a constant. When the procedure my_udf_describe is called, during the Execution state, the call to describe_parameter_get populates can_be_null with a value of 0.
SELECT * from my_udf(5);
This example calls the procedure my_udf with a NULL. When the procedure my_udf_describe is called, during the Execution state, the call to describe_parameter_get populates can_be_null with a value of 1.
SELECT * from my_udf(NULL);