I'm not sure why you're doing it the exact way you are, but you basically
just call the same commands within a pg_query as you would on the psql
command line to make it work:
begin
declare mycurs cursor as select * from table
fetch 10
rollback / commit