Re: query has no destination for result data - Mailing list pgsql-general

On 3/5/19 11:45 PM, Rob Sargent wrote:
> I’m using 10.7. Does an empty result set generate this error by any chance.
> 
> One of my plpgsql functions is now throwing this error:
> 
>     select * from genome_threshold_mono('11-O3C.pbs','1-O3C_chr',1.96,
>     1000000);

The above is the function you started with.
See below for more.

>     NOTICE:  group id is 5eed8d65-d39a-4f72-97a3-ca391b84880d
>     NOTICE:  New threshold: 66128154-d128-4e66-bb8e-e9c9ee5ae89d
>     NOTICE:  doing chrom 11
>     NOTICE:  2019-03-06 00:21:17.253375-07: markerset id is
>     9a8f7487-bd64-4d43-9adf-5ae1c6744e60(1-O3C_chr11.Loci.ld), people
>     (5eed8d65-d39a-4f72-97a3-ca391b84880d) id is 11-O3C.pbs
>     NOTICE:  table "collected" does not exist, skipping
>     NOTICE:  table "mrkidx" does not exist, skipping
>     NOTICE:  2019-03-06 00:21:17.295142-07: working with 28607 markers
>     NOTICE:  2019-03-06 00:21:17.383835-07: added 3514 segments to
>     imputed_pvalue_t
>     ERROR:  query has no destination for result data
>     HINT:  If you want to discard the results of a SELECT, use PERFORM
>     instead.

Below you have two other functions in play:

optimal_pvalue_mono()
genome_threshold_mono()

If I am following correctly it is  line 30 in genome_threshold_mono() 
you want to take a look at.



>     CONTEXT:  PL/pgSQL function
>     optimal_pvalue_mono(text,text,integer,double precision,integer) line
>     65 at SQL statement
>     SQL statement "insert into goptset
>              select * from optimal_pvalue_mono(people_name, mvec.name,
>     mvec.chrom, conf, maxi)"
>     PL/pgSQL function genome_pvalue_mono(text,text,double
>     precision,integer) line 19 at SQL statement
>     SQL statement "insert into threshold_segment(id,threshold_id,
>     segment_id, smooth_pvalue)
>               select uuid_generate_v4(), tid, f.segment_id, f.pval
>               from genome_pvalue_mono(pbs_name, markers_rx, conf, maxi)
>     as f"
>     PL/pgSQL function genome_threshold_mono(text,text,double
>     precision,integer) line 30 at SQL statement
> 
> 
> The code referenced at line 65 is the last line in a "for row in query” 
> construct as follows:
> 
>          54 for segp in
>          55   select s.id <http://s.id>, s.firstmarker, s.lastmarker,
>          56          v.ipv,
>          57          array_length(p.probands,1) as pbs,
>          58          s.lastmarker - s.firstmarker as mks
>          59   from segment s
>          60        join imputed_pvalue_t v on s.id <http://s.id> =
>     v.segment_id
>          61        join probandset p on s.probandset_id = p.id <http://p.id>
>          62        join probandset_group_member m on p.id <http://p.id>
>     = m.member_id
>          63   where s.markerset_id = mkset
>          64         and m.group_id = pbsgid
>          65   order by ipv, pbs, mks
>          66 LOOP
> 
> Plugging in the appropriate values for an example run generates a proper 
> dataset (~1300 rows)as far as I can tell.
> This construct had been working until recent changes but I cannot relate 
> the message to any deformity in the current schema or code.
> Any pointers appreciated.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com