sql-query-identifier icon indicating copy to clipboard operation
sql-query-identifier copied to clipboard

psql queries with functions not properly separated

Open MasterOdin opened this issue 4 years ago • 0 comments

Test query:

CREATE OR REPLACE FUNCTION f_grp_prod(text)
  RETURNS TABLE (
    name text
  , result1 double precision
  , result2 double precision)
LANGUAGE plpgsql STABLE
AS
$$
DECLARE
    r      mytable%ROWTYPE;
    _round integer;
BEGIN
    -- init vars
    name    := $1;
    result2 := 1;       -- abuse result2 as temp var for convenience
FOR r IN
    SELECT *
    FROM   mytable m
    WHERE  m.name = name
    ORDER  BY m.round
LOOP
    IF r.round <> _round THEN   -- save result1 before 2nd round
        result1 := result2;
        result2 := 1;
    END IF;
    result2 := result2 * (1 - r.val/100);
    _round  := r.round;
END LOOP;
RETURN NEXT;
END;
$$;

SELECT * FROM foo;

The identifier only returns one function instead of two.

MasterOdin avatar Oct 29 '21 22:10 MasterOdin