Hi,
I have a table where I need to select probes if at least three of
their
values (experimental data) are above a certain treshhold
Table:
probes val1 val2 val3 val4
one 2 -2 3 1
...
This works with the function below.
However I would like also to be able to integrate this function
somehow into
a bigger SQL SELECT statement with muliple exists (simplified):
SELECT * FROM probes WHERE EXISTS ( SELECT true FROM data WHERE
data.probe = probes.probe AND val1 > 2 );
val1 > 2 should be replaced with some pgSQL function.
How do I get the current probe postgresql evaluates into the function
and return that it passed?
If it is possible, how big is the performace hit for calling these
functions?
Thank you very much for your answers
Ido M. Tamir
CREATE OR REPLACE FUNCTION getMin3Above( INTEGER ) RETURNS Boolean AS
'
DECLARE
minVal ALIAS FOR $1;
values RECORD;
fit INTEGER;
passed BOOLEAN;
BEGIN
FOR values IN SELECT * FROM data LOOP
fit := 0;
passed := false;
IF abs(values.val1) > minVal THEN fit := fit + 1; END IF;
IF abs(values.val2) > minVal THEN fit := fit + 1; END IF;
IF abs(values.val3) > minVal THEN fit := fit + 1; END IF;
IF abs(values.val4) > minVal THEN fit := fit + 1; END IF;
IF fit > 2 THEN passed := true; END IF;
RAISE NOTICE ''probe: % passed: %'', values.probe, passed ;
END LOOP;
RETURN passed;
END;
' LANGUAGE 'plpgsql';
CREATE TABLE probes(
probe VARCHAR(10) primary key
);
CREATE TABLE data(
probe VARCHAR(10) references probes( probe),
val1 INT,
val2 INT,
val3 INT,
val4 INT
);
INSERT INTO probes VALUES( 'one' );
INSERT INTO probes VALUES( 'two' );
INSERT INTO probes VALUES( 'three' );
INSERT INTO probes VALUES( 'four');
INSERT INTO probes VALUES( 'five' );
INSERT INTO data VALUES( 'one', 2, 2, 2, 2 );
INSERT INTO data VALUES( 'two', 3, 3, 3, 1 );
INSERT INTO data VALUES( 'three', 1, 3, 3, 1 );
INSERT INTO data VALUES( 'four', 1, 3, 1, 1 );
INSERT INTO data VALUES( 'five', 3, 3, 3, 3 );
