Postgresql plpgsql multiple row loop

Question

I'm busy trying to rewrite an Informix stored procedure for a PostgreSQL database and I am stuck on something that is probably quite obvious to everyone who know PostgreSQL.

I have my sql script as follows

-- ensure type and function get created
drop type if exists tp_users cascade;
drop function if exists sp_cmplist();

-- create type
create type tp_users as (
    us_id       char(30),
    us_status   char(1)
);

create function sp_cmplist()
    returns tp_users as $$
declare
    lr_users   tp_users;
begin

    for lr_users in
        select users.us_id, users.us_status
        from users
    loop
        return lr_users;
    end loop;

end

$$ language 'plpgsql';

select sp_cmplist();

this is just a dummy script to select from an imaginary users table but how would I use this script with a cursor or loop to make sure all results are returned?

I've tried the database administrators community before but they've shut me down with questions like this saying that it's not professional enough or something like that — TheLovelySausage, Oct 13 '15 at 12:53
The stackoverflow is good place - plpgsql is a developer topic — Pavel Stehule, Oct 13 '15 at 13:17

score 0 · Answer 1 · answered Oct 13 '15 at 12:23

This code works:

CREATE TABLE foo(a int);
INSERT INTO foo VALUES(10),(20);

CREATE OR REPLACE FUNCTION retfoo()
RETURNS SETOF foo AS $$
BEGIN
  RETURN QUERY SELECT * FROM foo;
  RETURN;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT * FROM retfoo();
┌────┐
│ a  │
├────┤
│ 10 │
│ 20 │
└────┘
(2 rows)

Time: 1.143 ms

score 0 · Answer 2 · answered Oct 13 '15 at 12:29

I may have answered my own question with the following

drop type if exists tp_users cascade;
drop function if exists sp_cmplist();

create type tp_users as (
    us_id        text,
    us_status    text,
    lv_nothing   text,
    lv_cnt       int
);

create function sp_cmplist()
    returns setof tp_users as $$
declare
    lr_users   tp_users;
    lv_cnt     int;
begin

    lv_cnt := 0;

    for lr_users in
        select users.us_id, users.us_status
        from users
    loop

        -- increment this counter for testing purposes
        lv_cnt              := lv_cnt + 1;

        lr_users.lv_nothing := 'yupy';
        lr_users.lv_cnt     := lv_cnt;

        return next lr_users;

    end loop;

    return;

end

$$ language 'plpgsql';

select * from sp_cmplist();

this seems to work perfectly

for this request you don't need `for` cycle. It is slower than `RETURN QUERY SELECT use_id, us_status, 'yupy', row_number() over () FROM users;`. What is possible to solve by simple query should be solved by simple query. — Pavel Stehule, Oct 14 '15 at 04:26
Normally this is the way I would do it with the returned variables being a table but this example is just a dummy query, the real query is using multiple separate selects and comparing results and building up the data for the return in the loop, I don't think I'd be able to do it easily with one select — TheLovelySausage, Oct 14 '15 at 07:03

Postgresql plpgsql multiple row loop

2 Answers2