CODEWITHSUNDEEP
postgresql
Kannan
Kannan

Reputation: 59

PostgreSQL database stored procedure

Create stored procedure getoperatordetails in postgresql database.

Getting the three parameters in another stored procedure, but it is causing error, avoid the error and will get result True (or) False 

 CREATE OR REPLACE FUNCTION getoperatordetails(value character varying, operator     character varying, fieldinputvalue character varying, retvalue boolean)
      RETURNS SETOF boolean AS
    $BODY$
    declare
    iVal numeric;
    gVal numeric;
    begin
    IF fieldInputValue != ' ' AND operator!='equal' THEN

    gVal := value :: numeric;
    iVal := fieldInputValue:: numeric;

    IF (operator = ('lesserThan') AND iVal < gVal) THEN
    retValue= true;

    ELSIF (operator = ('greaterThan') AND iVal > gVal) THEN
    retValue= true;

    ELSIF (operator = ('lessOrEqual') AND iVal <= gVal) THEN
    retValue= true;

    ELSIF (operator = ('greaterOrEqual') AND iVal >= gVal) THEN
    retValue= true;

    ELSIF (operator = ('equal') AND value = (fieldInputValue)) THEN
    retValue= true;

    ELSE
    retValue= false;

    END IF;

    ELSIF (operator=('equal') AND value=(fieldInputValue)) THEN
    retValue= true;

    ELSE
    retValue= false;

    END IF;
    return next retValue;
    end;

    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100
      ROWS 1000;
    ALTER FUNCTION getoperatordetails(character varying, character varying, character varying, boolean)
      OWNER TO postgres;

I need to get the result True (or) False

ERROR: argument of AND must not return a set
LINE 1: SELECT retValue AND getoperatordetails(value, operator, fiel...)

Upvotes: 1

Views: 950

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45940

You are wrong in declaration of this function. It is declared as "RETURNS SETOF boolean", what is nonsense. Probably you would to write a scalar function, so declaration should be "RETURNS boolean" - you have to use "RETURN" instead "RETURN NEXT" then.

Second issue: you use a C style of programming - plpgsql is interpreted language and you should to minimize a number of instructions - so using a auxiliary value for result is not good - your code will be 2x slower. You can use a CASE construct instead nested IF - or better, you can use a just SQL function instead PL/pgSQL function.

CREATE OR REPLACE FUNCTION foo(op text, a int, b int)
RETURNS bool AS $$
SELECT CASE op 
         WHEN '=' THEN a = b
         WHEN '<' THEN a < b
         WHEN '>' THEN a > b
       END;       
$$ LANGUAGE sql;

Personally, It looks like you try to create new interpret language - create interpret in interpreted language is not best idea - really.

Upvotes: 1

Related Questions