Postgresql function, result set into variable

Question

I have procedure:

CREATE OR REPLACE FUNCTION func()
RETURNS SETOF bigint AS
$BODY$
DECLARE
    rowsQuantity bigint;
BEGIN
    return query select p.id from product p where...;
    GET DIAGNOSTICS rowsQuantity = ROW_COUNT;
    if(rowsQuantity < 8) then
        return query select p.id from product p where p.id not in (ids from prev query) limit 8 - rowsQuantity;
    end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

My question is, how to get id's from first query to use them in second query, or maybe i can somehow declare variable and select into this variable id's from first query and then use this variable in second query? I can't find solution for my task...help me please

I use Postgresql version 9.3.6

What exactly are you trying to do? The whole function seems overly complicated. This could probably be done with just a single query if you tell us the underlying problem you are trying to solve. Edit your question add some sample data and the expected output based on that data. – a_horse_with_no_name 3 mins ago

My procedure must return 8 records, first query has many conditions(and because of this conditions, result set ROW COUNT can be less then 8 records) that's why i need check if ROW COUNT of first query less then 8 records, i must added records to result set from another query, but i need to avoid duplicates, the second query must return 0...8 rows(depends on first query), but without duplicates from first query, that's why i need id's from first query

sounds a simple select distinct p.id from product p where... limit 8; would do the job

Nope, second query contains records(with ids) which i received from first query, DISTINCT in second query doesn't helps

in this case rowsQuantity will contain only one id, but i need more then one, if i'm not mistaken, if not, can you show how can i get all of them? — user2870934, Dec 12 '15 at 16:24
What exactly are you trying to do? The whole function seems overly complicated. This could probably be done with just a single query if you tell us the underlying problem you are trying to solve. **Edit** your question add some sample data and the expected output based on that data. — , Dec 12 '15 at 16:27
sounds a simple `select distinct p.id from product p where... limit 8;` would do the job — , Dec 12 '15 at 16:49

score 2 · Accepted Answer · answered Dec 12 '15 at 17:59

You can use a temporary table:

begin
    create temp table tt as
        select id 
        from product
        where...
    row_ct = (select count(*) from tt);

    return query select * from tt;

    if row_ct < 8 then
        return query select id 
        from product
        where id not in (select id from tt)
        limit 8 - row_ct;
    end if;
    drop table tt;
end;

Postgresql function, result set into variable

1 Answers1