How do we combine and return results from multiple queries in a loop?

Поиск
Список
Период
Сортировка
От Bernardo Telles
Тема How do we combine and return results from multiple queries in a loop?
Дата
Msg-id BANLkTi=kyPfpDV4H2isoHPX0VWxRSREYzw@mail.gmail.com
обсуждение исходный текст
Ответы Re: How do we combine and return results from multiple queries in a loop?
Список pgsql-general
Hi there,
We'd like to use a plpgsql function to use results from query A to execute several queries B, C, etc., and return the results of all B, C, etc queries as one result set. Would placing 'RETURN QUERY' inside a loop automatically concatenate all 'return query' results in the function's return? If not, how would we go about getting this result?

Here's an example. The function finds all zip_code records with the given code, and then searches all locations for a state of that original zip_code. (I know the current query could be done via a join, but my ACTUAL query would require this functionality.)

    begin;
    create OR REPLACE function t() returns setof locations as
    $$
    declare z zip_codes%rowtype;
    begin
      for z in select * from zip_codes where code like '%32301%'
      LOOP
        return query select * from locations where locations.state like z.state; #query B
            # All I want to do is return the results from all of the above queries as one
            # result set.
      END LOOP;
      return;
    end
    $$
    language 'plpgsql';
    commit;

Any idea how I do that?

В списке pgsql-general по дате отправления:

Предыдущее
От: Szymon Guz
Дата:
Сообщение: Re: Column names getting lower-case in SELECT statements when issued via JDBC
Следующее
От: John R Pierce
Дата:
Сообщение: Re: How do we combine and return results from multiple queries in a loop?