PostgreSQL function does not generated

[Ildar Ov]

Hi Lukas!

I have function in PostgrteSQL , using JOOQ 3.5.0. Maven plugin runs for generating functions. This function doesn't, but table with the same name generates. Is it my mistake or bug?

Thanks in advance.

CREATE OR REPLACE FUNCTION chasdb.gettest(vals text[] )

  RETURNS SETOF text AS

$BODY$

  DECLARE

    txt         TEXT;

    result      TEXT;

  BEGIN

    FOREACH txt IN ARRAY vals LOOP

      

      RETURN NEXT txt;

    END LOOP;

    EXCEPTION WHEN OTHERS THEN

    BEGIN

    

    END;

  END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

  ROWS 1000;

[Lukas Eder]

This (setof functions) will be supported in jOOQ 3.6 only:

https://github.com/jOOQ/jOOQ/issues/3376

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ildar Ov]

Thanks a lot!

[Ildar Ov]

One more question.

I've created function converting setof to refcursor

 

CREATE OR REPLACE FUNCTION chasdb.getresult(...)

  RETURNS refcursor AS

$BODY$

  DECLARE

    cur1 refcursor := 'result';

  BEGIN

    

    RAISE NOTICE 'string=%',biospanval_txt;

    OPEN cur1 FOR execute('select * from chasdb.getoverlapcounts('||....)||')'); -- returns setof 

    RETURN cur1;

  END;

$BODY$

in pgAdmin everything is ok :

 

 select  chasdb.getresult(ARRAY['chr5:12017264,17602802','chr5:45099457,46383335'], 'loh', 'loh', 'experiment', 'hg19');

 fetch all in result;

returns suitable results;

In Java I've got an error  

Caused by: org.jooq.exception.DataAccessException: SQL [select * from chasdb.getresult(ARRAY['chr5:12017264,17602802','chr5:45099457,46383335'], 

'loh', 'loh', 'experiment', 'hg19')]; ERROR: cursor "result" does not exist

Is it autocommit issue or I should fetch in some special case?

 

 

[Lukas Eder]

Hello,

This reminds me of an issue related to autocommit, indeed:

https://github.com/jOOQ/jOOQ/issues/2324

There's a JDBC-level issue in PostgreSQL's driver that prevents you from using a cursor after the commit. You should turn autocommit to off in this case.

Another option would be to use TABLE as a result type, rather than SETOF, or you can return TEXT[], which is also supported by jOOQ 3.5

Hope this helps,

Lukas

--

[Ildar Ov]

Thanks. Implemented with array.

On Monday, April 6, 2015 at 7:32:25 PM UTC+3, Ildar Ov wrote: