Search code examples
postgresqlfunction-callpgadmin-4

PostgreSQL pgAdmin4 " window function X requires an OVER clause LINE 1: select * from X"

Above error is very common but I still couldn't find any solution that helps me. Being new to PostgreSQL I am stuck at function calling where I have defined an easy function that return a column from a table on executing that function but instead its throwing me above error. Here is my function definition and its call:

CREATE OR REPLACE FUNCTION public.X()
RETURNS SETOF character varying 
LANGUAGE 'sql'

COST 100
VOLATILE LEAKPROOF STRICT SECURITY DEFINER WINDOW 
ROWS 1000
AS $BODY$

Select id from change where id>= '274'

$BODY$;

ALTER FUNCTION public.X(character varying, character varying)
OWNER TO lke_new;
COMMENT ON FUNCTION public.X(character varying, character 
varying)
IS 'The function returns the set of X ids';

--function call
select * from X('274','275')

Any leads would be really helpful!

Solution

  • You have declared this function as window function by specifying WINDOW in the CREATE FUNCTION statement.

    Then you try to call it like a normal function. That won't work.

    Moreover, the only language you can use to define a window function is C. Like the documentation says:

    WINDOW indicates that the function is a window function rather than a plain function. This is currently only useful for functions written in C.

    I suspect that this is by mistake and all you have to do is to remove the WINDOW clause.