How to print multiple row values in postgres when query executed using EXECUTE?

Question

I have below program , My 2nd select query returns multiple records, How should I print all the values.. Currently its printing only 1st value. Can you please suggest?

Ex. Actual Output:

123

890

4599

Current Output: 123

DO $$
<<script>>
DECLARE
  student_rec RECORD;
  sqlquery text;
  v_nk RECORD;

BEGIN
FOR student_rec IN 
select nk from course_table;
-- open loop for cursor above
LOOP
sqlquery := 'SELECT col FROM section where rec_nk = ' || '''' ||student_rec.nk ||'''' ;
EXECUTE sqlquery into v_nk; 
raise notice 'Value: %', v_nk;  
END LOOP; 

EXCEPTION when others then    
    raise notice 'Script execution failed.'
                 'Transaction was rolled back.';
    raise notice '% %', SQLERRM, SQLSTATE;

END script $$;

Marc Bannout · Accepted Answer · 2023-01-16 03:24:09Z

3

Even if you have declared v_nk as RECORD, SELECT INTO indeed selects a value into a scalar value, when used in the context of PL/pgSQL. See SELECT INTO documentation for more details.

If you want to print all records with RAISE NOTICE you can loop like this:

sqlquery := 'SELECT col FROM section WHERE rec_nk = ' || '''' || student_rec.nk || '''' ;

FOR v_nk IN EXECUTE sqlquery
    LOOP
        RAISE NOTICE 'Value: %', v_nk;
    END LOOP;

edited Jan 16, 2023 at 3:24

Marc Bannout

4511 gold badge6 silver badges17 bronze badges

answered Oct 30, 2017 at 20:57

Norbert Poellmann

563 bronze badges

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

Comments

Marc Bannout · Accepted Answer · 2023-01-16 18:42:11Z

0

You already are looping through query result:

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

There is also FOR-IN-EXECUTE:

[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];

See documentation on Looping Through Query Results.

In your case it would be:

FOR v_nk IN EXECUTE sqlquery LOOP
    RAISE NOTICE 'Value: %', v_nk;
END LOOP;

edited Jan 16, 2023 at 18:42

Marc Bannout

4511 gold badge6 silver badges17 bronze badges

answered Oct 30, 2017 at 20:39

Adam

5,6296 gold badges35 silver badges41 bronze badges

Collectives™ on Stack Overflow

How to print multiple row values in postgres when query executed using EXECUTE?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related