pgSQL function for: SELECT ... WHERE EXISTS ( SELECT true myFunc() ) - Mailing list pgsql-general

From tamir@imp.univie.ac.at (Ido Tamir)
Subject pgSQL function for: SELECT ... WHERE EXISTS ( SELECT true myFunc() )
Date
Msg-id b2ee0f2c.0301310217.594bc221@posting.google.com
Whole thread Raw
List pgsql-general
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 );

pgsql-general by date:

Previous
From: Jeff
Date:
Subject: Re: [PERFORM] One large v. many small
Next
From: "Berend Tober"
Date:
Subject: What is the benefit of schemas?