Skip site navigation (1) Skip section navigation (2)

LIMITing number of results in a VIEW with global variables

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 (view raw or flat)
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

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2011-10-14 14:56:22
Subject: Re: patch for new feature: Buffer Cache Hibernation
Previous:From: Tom LaneDate: 2011-10-14 14:41:44
Subject: Re: patch for new feature: Buffer Cache Hibernation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group