Error: query has no destination for result data while using a cursor

Question

I have a function which I have written to automate the execution of a group of functions for my project. I am taking a refcursor where I am storing my required data which I will be passing as an argument to each of my functions being called and based on the argument will get executed. I am giving my code here:

CREATE OR REPLACE FUNCTION ccdb.fn_automation()
RETURNS void AS
$BODY$

DECLARE 
sec_col refcursor;
cnt integer;
sec_code ccdb.update_qtable%ROWTYPE;
new_cnt numeric;

BEGIN

SELECT COUNT(*) INTO cnt FROM ccdb.update_qtable WHERE status_flag IN (-1,1);

OPEN sec_col FOR SELECT DISTINCT section_code FROM ccdb.update_qtable WHERE status_flag IN (-1,1);

FOR i IN 1..cnt
LOOP

        FETCH sec_col INTO sec_code;
        SELECT ccdb.o_dtr_update(sec_code.section_code);
        SELECT ccdb.o_consumer_update_for_update(sec_code.section_code);
        SELECT ccdb.o_bills_update_for_update(sec_code.section_code);
        SELECT ccdb.o_payments_update_for_update_new(sec_code.section_code);
        SELECT ccdb.o_payments_map_update_for_update(sec_code.section_code);

        SELECT COUNT(*) INTO new_cnt FROM ccdb.update_qtable WHERE status_flag IN (-1,1);

        IF new_cnt > cnt 
        THEN 
            CLOSE sec_col;

            OPEN sec_col FOR SELECT DISTINCT section_code FROM ccdb.update_qtable WHERE status_flag IN (-1,1);

            cnt := new_cnt;
        END IF;

END LOOP;

CLOSE sec_col;

END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Now here the problem that I am facing is, whenever I try to execute this function, i get an error saying,

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 ccdb.fn_automation() line 20 at SQL statement

I don't know where am I supposed to use PERFORM in this function. The the context says the error is in line 20, i.e., the SELECT statement which I am using while opening my cursor. So I don't how can I resolve this issue.

Erwin Brandstetter · Accepted Answer · 2014-03-27 00:33:04Z

3

As per instructions in the manual:

Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement:
PERFORM query;

answered Mar 27, 2014 at 0:33

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yousuf Sultan · Accepted Answer · 2014-03-26 10:10:59Z

0

I got the problem with my query. I was able to solve my problem. In the function where I am using SELECT while calling the function, I just replaced the keyword SELECT with the keyword with PERFORM.

I changed it and made it something like this:

    PERFORM ccdb.o_dtr_update(sec_code.section_code);
    PERFORM ccdb.o_consumer_update_for_update(sec_code.section_code);
    PERFORM ccdb.o_bills_update_for_update(sec_code.section_code);
    PERFORM ccdb.o_payments_update_for_update_new(sec_code.section_code);
    PERFORM ccdb.o_payments_map_update_for_update(sec_code.section_code);

After making this change my function worked perfectly.

Thanks.

answered Mar 26, 2014 at 10:10

Yousuf Sultan

3,30711 gold badges40 silver badges63 bronze badges

Collectives™ on Stack Overflow

Error: query has no destination for result data while using a cursor

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related