PostgreSQL: Query has no destination for result data in a query with variables

Question

ERROR: query has no destination for result data

HINT: If you want to discard the results of a SELECT, use PERFORM instead.

CONTEXT: PL/pgSQL function inline_code_block line 11 at SQL statement


DO $$
DECLARE
id_renovacion integer := (select Id_renovacionContrato 
from BEA.DIM_RENOVACION_CONTRATO where upper(Desc_renovacionContrato) = 'UNDEFINED'); 

id_renovacionpending integer := (select Id_renovacionContrato 
from BEA.DIM_RENOVACION_CONTRATO where upper(Desc_renovacionContrato) = 'PENDING');


BEGIN

SELECT count(id_contrato) as id_cantidad,
                CASE    when count(id_contrato) = 0 THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) is null THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) = 1 THEN 'ImagesTITIhappymarycontratos-off-1_145.png'
                             when count(id_contrato) = 2 THEN 'ImagesTITIhappymarycontratos-off-2_145.png'
                             when count(id_contrato) = 3 THEN 'ImagesTITIhappymarycontratos-off-3_145.png'
                             when count(id_contrato) = 4 THEN 'ImagesTITIhappymarycontratos-off-4_145.png'
                             when count(id_contrato) = 5 THEN 'ImagesTITIhappymarycontratos-off-5_145.png'
                             when count(id_contrato) = 6 THEN 'ImagesTITIhappymarycontratos-off-6_145.png'
                             when count(id_contrato) = 7 THEN 'ImagesTITIhappymarycontratos-off-7_145.png'
                             when count(id_contrato) = 8 THEN 'ImagesTITIhappymarycontratos-off-8_145.png'
                             when count(id_contrato) = 9 THEN 'ImagesTITIhappymarycontratos-off-9_145.png'
                             when count(id_contrato) > 9 THEN 'ImagesTITIhappymarycontratos-off-9+_145.png'
                END
 FROM BEA.FACT_CONTRATOS
WHERE cantDiasDif > 0 AND Id_rangoContrato IN (id_renovacion, id_renovacionpending);

END 
$$

LANGUAGE plpgsql; ```

The error message is quite descriptive. Do you understand what is says? What did you plan to do with the selected data? — Bergi
– Bergi, Commented Apr 28, 2020 at 20:51
what is your question? "How do you avoid: ERROR: query has no destination for result data" ? — azbarcea
– azbarcea, Commented May 11, 2020 at 1:11

Vibhor Kumar · Accepted Answer · 2020-04-28 20:46:59Z

Two problems I can see in the posted code.

create in the code. I am not sure why do you have create in your anonymous block.
You have the following SELECT in your DO block:

SELECT count(id_contrato) as id_cantidad,
                CASE    when count(id_contrato) = 0 THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) is null THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) = 1 THEN 'ImagesTITIhappymarycontratos-off-1_145.png'
                             when count(id_contrato) = 2 THEN 'ImagesTITIhappymarycontratos-off-2_145.png'
                             when count(id_contrato) = 3 THEN 'ImagesTITIhappymarycontratos-off-3_145.png'
                             when count(id_contrato) = 4 THEN 'ImagesTITIhappymarycontratos-off-4_145.png'
                             when count(id_contrato) = 5 THEN 'ImagesTITIhappymarycontratos-off-5_145.png'
                             when count(id_contrato) = 6 THEN 'ImagesTITIhappymarycontratos-off-6_145.png'
                             when count(id_contrato) = 7 THEN 'ImagesTITIhappymarycontratos-off-7_145.png'
                             when count(id_contrato) = 8 THEN 'ImagesTITIhappymarycontratos-off-8_145.png'
                             when count(id_contrato) = 9 THEN 'ImagesTITIhappymarycontratos-off-9_145.png'
                             when count(id_contrato) > 9 THEN 'ImagesTITIhappymarycontratos-off-9+_145.png'
                END
 FROM BEA.FACT_CONTRATOS
WHERE cantDiasDif > 0 AND Id_rangoContrato IN (id_renovacion, id_renovacionpending);

And you are not storing the returned result of the SELECT into any variable. If you don't care of the SELECT result, then use PERFORM as given below:

PERFORM count(id_contrato) as id_cantidad,
                CASE    when count(id_contrato) = 0 THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) is null THEN 'ImagesHappyMaritransparente1.png'
                             when count(id_contrato) = 1 THEN 'ImagesTITIhappymarycontratos-off-1_145.png'
                             when count(id_contrato) = 2 THEN 'ImagesTITIhappymarycontratos-off-2_145.png'
                             when count(id_contrato) = 3 THEN 'ImagesTITIhappymarycontratos-off-3_145.png'
                             when count(id_contrato) = 4 THEN 'ImagesTITIhappymarycontratos-off-4_145.png'
                             when count(id_contrato) = 5 THEN 'ImagesTITIhappymarycontratos-off-5_145.png'
                             when count(id_contrato) = 6 THEN 'ImagesTITIhappymarycontratos-off-6_145.png'
                             when count(id_contrato) = 7 THEN 'ImagesTITIhappymarycontratos-off-7_145.png'
                             when count(id_contrato) = 8 THEN 'ImagesTITIhappymarycontratos-off-8_145.png'
                             when count(id_contrato) = 9 THEN 'ImagesTITIhappymarycontratos-off-9_145.png'
                             when count(id_contrato) > 9 THEN 'ImagesTITIhappymarycontratos-off-9+_145.png'
                END
 FROM BEA.FACT_CONTRATOS
WHERE cantDiasDif > 0 AND Id_rangoContrato IN (id_renovacion, id_renovacionpending);

However, question why do you want to do that? Let us know the reason for not storing the result of the SELECT or your intention.

Hi Vibhor, yes you are right, that "create" is a mistake in the code, i'm trying to solve it creating a table and I solve it. additionally I applied your recommendation adding a PERFORM instead of SELECT in the code, but I need the result to be shown, which with CREATE was solved. Answering your question I am using this to display it in a BI application: MicroStrategy. Thanks for your answer

Mario Cotrina · Accepted Answer · 2020-04-29 02:22:57Z

This code solve the problem, use a CREATE table to show results.

DO $$
DECLARE
id_renovacion integer := (select Id_renovacionContrato 
from BEA.DIM_RENOVACION_CONTRATO where upper(Desc_renovacionContrato) = 'UNDEFINED'); 

id_renovacionpending integer := (select Id_renovacionContrato 
from BEA.DIM_RENOVACION_CONTRATO where upper(Desc_renovacionContrato) = 'PENDING');


BEGIN

DROP TABLE if EXISTS  tbcontratos;
CREATE TABLE tbcontratos (id_cantidad integer, images varchar(200));

insert into tbcontratos
SELECT count(id_contrato) as id_cantidad,
                CASE    when count(id_contrato) = 0 THEN 'Images\HappyMari\transparente1.png'
                             when count(id_contrato) is null THEN 'Images\HappyMari\transparente1.png'
                             when count(id_contrato) = 1 THEN 'Images\TITI\happymary\contratos-off-1_145.png'
                             when count(id_contrato) = 2 THEN 'Images\TITI\happymary\contratos-off-2_145.png'
                             when count(id_contrato) = 3 THEN 'Images\TITI\happymary\contratos-off-3_145.png'
                             when count(id_contrato) = 4 THEN 'Images\TITI\happymary\contratos-off-4_145.png'
                             when count(id_contrato) = 5 THEN 'Images\TITI\happymary\contratos-off-5_145.png'
                             when count(id_contrato) = 6 THEN 'Images\TITI\happymary\contratos-off-6_145.png'
                             when count(id_contrato) = 7 THEN 'Images\TITI\happymary\contratos-off-7_145.png'
                             when count(id_contrato) = 8 THEN 'Images\TITI\happymary\contratos-off-8_145.png'
                             when count(id_contrato) = 9 THEN 'Images\TITI\happymary\contratos-off-9_145.png'
                             when count(id_contrato) > 9 THEN 'Images\TITI\happymary\contratos-off-9+_145.png'
                END
 FROM BEA.FACT_CONTRATOS
WHERE cantDiasDif > 0 AND Id_rangoContrato IN (id_renovacion, id_renovacionpending);



END 
$$

LANGUAGE plpgsql;

select id_cantidad, images from tbcontratos; ```

Collectives™ on Stack Overflow

PostgreSQL: Query has no destination for result data in a query with variables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related