PostgreSQL database stored procedure

Question

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...)

Is this the Way what u want ? or else edit ur query and forward it back. . .Check This — 09Q71AO534
– 09Q71AO534, Commented Aug 2, 2013 at 6:37
Hi, I need to avoid the error and will return type True (or) False — Kannan
– Kannan, Commented Aug 2, 2013 at 6:41
when did this procedure raised a error! what sort of query evaluation did u gave.. — 09Q71AO534
– 09Q71AO534, Commented Aug 2, 2013 at 6:43
Hi, I am passing three parameters: retValue = retValue AND getoperatordetails(value, operator, fieldinputvalue, retvalue); — Kannan
– Kannan, Commented Aug 2, 2013 at 6:47
I cant get u clear enough can u keep ur code into a SQL FIDDLE and please give the format in the which u r trying to call the function and use it — 09Q71AO534
– 09Q71AO534, Commented Aug 2, 2013 at 6:51

Pavel Stehule · Accepted Answer · 2013-08-02 07:07:29Z

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.

Collectives™ on Stack Overflow

PostgreSQL database stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related