From: | Thomas Girault <toma(dot)girault(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | LIMITing number of results in a VIEW with global variables |
Date: | 2011-10-14 14:43:01 |
Message-ID: | CAMVHftSgk-MhdfAbfY+HrpQf8-_e-e6HTJDxRtHLRA3AjMROHA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2011-10-14 14:56:22 | Re: patch for new feature: Buffer Cache Hibernation |
Previous Message | Tom Lane | 2011-10-14 14:41:44 | Re: patch for new feature: Buffer Cache Hibernation |