LIMITing number of results in a VIEW with global variables

Hello,

I am writing an extension to easily execute queries with conditions
expressing constraints in fuzzy logics.

I wrote some C functions that get or set global variables in C.
The variables are MU (FLOAT : degree of a fuzzy predicate), ALPHA
(FLOAT : threshold for filtering predicates) and K (INTEGER : limits
the number of results).
Here is an example for the variable ALPHA :
 /*--- sqlf.c ---*/ static float8 ALPHA; Datum get_alpha(PG_FUNCTION_ARGS); Datum get_alpha(PG_FUNCTION_ARGS){
PG_RETURN_FLOAT8(ALPHA);} Datum set_alpha(PG_FUNCTION_ARGS); Datum set_alpha(PG_FUNCTION_ARGS){     ALPHA =
PG_GETARG_FLOAT8(0);    PG_RETURN_FLOAT8(ALPHA); } /*--- sqlf.sql ---*/ CREATE OR REPLACE FUNCTION set_alpha(alpha
FLOAT)RETURNS FLOAT   AS '$libdir/sqlf', 'set_alpha'   LANGUAGE C STRICT;
 

These variables are parameters for filtering and sorting results.
The following cast operations are using MU and ALPHA.
 CREATE OR REPLACE FUNCTION fuzzy2bool(FLOAT) RETURNS BOOLEAN LANGUAGE SQL AS 'SELECT set_mu($1);SELECT $1 >
get_alpha()';
 CREATE CAST (FLOAT AS BOOLEAN) WITH FUNCTION fuzzy2bool(FLOAT) AS IMPLICIT;

With this implicit cast, the query SELECT age, young(age) FROM set_alpha(0.1), employees WHERE young(age);
is equivalent to SELECT age, young(age) FROM set_alpha(0.1), employees WHERE
fuzzy2bool(young(age));

Here, young(age) is a fuzzy predicate returning a float value in [0,1].
The queries keep results satisfying young(age) > alpha :
 age    young(age) 16    1 24    0.6 26    0.4 21    0.9 26    0.4


I can sort the results in the view 'sorted_employees' according to
value MU of a fuzzy predicate thanks to fuzzy2bool cast function.

CREATE OR REPLACE VIEW sorted_employees AS   SELECT *, get_mu() as mu   FROM employees   ORDER BY mu DESC;

The query SELECT age, mu FROM set_alpha(0.1), sorted_employees WHERE young(age);
gives the following results : age    mu 16    1 21    0.899999976158142 24    0.600000023841858 26    0.400000005960464
26   0.400000005960464
 

I am now trying to limit the number of results in the view according
to the global value K :

CREATE OR REPLACE VIEW filtered_employees AS   SELECT *, get_mu() as mu   FROM employees   ORDER BY mu DESC   LIMIT K;

The following query SELECT age, mu FROM set_k(5), set_alpha(0.1), filtered_employees
WHERE young(age);
gives the results : age    mu 24    1 16    1
instead of : age    mu 16    1 21    0.899999976158142 24    0.600000023841858 26    0.400000005960464 26
0.400000005960464

It seems that the 'LIMIT K' instruction have side effects on the MU value.

Why is it not working ? How to fix this issue ?


Thanks by advance,

Thomas Girault