PostgreSQL function returning TABLE

[Ildar Ov]

Hi Lukas!

I have a postgresql function 

CREATE or replace FUNCTION chasdb.getAll(_chr_name_intervals text, _min_ovelap real, _min_coverage real, _bio_domain text, _state text, _aed_category text)

  RETURNS TABLE(chrom text, mn int, mx int, category text, overlap bigint, coverage bigint, overlap_coverage bigint) AS $$

BEGIN 

    RETURN QUERY EXECUTE 

'SELECT ....

'

I used JOOQ 3.5

here http://blog.jooq.org/2014/07/07/postgresqls-table-valued-functions/ seems to work... 

 

Result<GetallRecord> result2 = createDSLContext().
        selectFrom(Routines.getall("", 0.0F, 0.0F, "experiment", "active", "copynumber/gain" )).fetch();

Those code generated by JOOQ 

select 

  "chasdb"."getall"."chrom", 
  "chasdb"."getall"."mn", 
  "chasdb"."getall"."mx", 
  "chasdb"."getall"."category", 
  "chasdb"."getall"."overlap", 
  "chasdb"."getall"."coverage", 
  "chasdb"."getall"."overlap_coverage"
from "chasdb"."getall"(
  '', 
  0.0, 
  0.0, 
  'experiment', 
  'active', 
  'copynumber/gain')

And I got an exception 
  ERROR: invalid reference to FROM-clause entry for table "getall"
  Hint: There is an entry for table "getall", but it cannot be referenced from this part of the query.
  Position: 8 
Probably issue with schema name?

How I could get 
 select *
from "chasdb"."getall"(
  '', 
  0.0, 
  0.0, 
  'experiment', 
  'active', 
  'copynumber/gain')

?


Thanks in advance

[Ildar Ov]

The same with 

Result<Record> result2 = createDSLContext().
        select().from(GETALL.call("('chr5', 12017264, 17602802),('chr16', 45099457,46383335)",

[Lukas Eder]

It appears that this has been fixed in 3.5.1:

- http://www.jooq.org/notes#3.5.1

- https://github.com/jOOQ/jOOQ/issues/3378

What jOOQ version are you using?

--

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.