LIMITing number of results in a VIEW with global variables
От | Thomas Girault |
---|---|
Тема | LIMITing number of results in a VIEW with global variables |
Дата | |
Msg-id | CAMVHftSgk-MhdfAbfY+HrpQf8-_e-e6HTJDxRtHLRA3AjMROHA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: LIMITing number of results in a VIEW with global variables
(Robert Haas <robertmhaas@gmail.com>)
Re: LIMITing number of results in a VIEW with global variables (Florian Pflug <fgp@phlo.org>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: