I am using Postgresql and have the simple function that will return a refcursor to the client:
CREATE OR REPLACE FUNCTION select_user(cur refcursor, m_id text)
RETURNS setof refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM public.users WHERE id=m_id;
RETURN NEXT $1;
END;
$$ LANGUAGE plpgsql;
I could fetch the data like this:
SELECT select_user('cur','999999'); FETCH ALL in cur;
Return 1 Row like this:
username email id
999999 [email protected] 999999
But the problem now, I'm using pl/proxy with Postgresql In pl/proxy server I wrote this function:
CREATE OR REPLACE FUNCTION select_user(cur refcursor, m_type integer, m_hid text)
RETURNS setof refcursor AS $$
CLUSTER 'sqlcluster';
RUN ON select_db(m_hid);
$$ LANGUAGE plproxy;
From the above function it return refcursor, but I couldn't fetch the refcursor Using this command:
SELECT select_user('cur','999999'); FETCH ALL in cur;
Return error:
SQL error:
ERROR: cursor "cur" does not exist
Could anyone explain what's the problem?
The PL/Proxy function is running in a different database, and the refcursor is not valid there. You are going to have to redesign that part of the code.