Postgres Pro
Downloads
Home   >   mailing lists

Re: Re: Support plpgsql multi-range in conditional control - Mailing list pgsql-hackers

From songjinzhou
Subject Re: Re: Support plpgsql multi-range in conditional control
Date
Msg-id tencent_726221581234ACEC66F2994D84774D435605@qq.com
Whole thread Raw
In response to Support plpgsql multi-range in conditional control  ("2903807914@qq.com" <2903807914@qq.com>)
Responses Re: Re: Support plpgsql multi-range in conditional control
List pgsql-hackers
Tree view
Hello, Pavel Stehule:

Thank you very much for your verification. The test cases you provided work well here:



For your second example, we can easily merge, as follows:



For scenarios that can be merged, we can choose to use this function to reduce code redundancy; If the operations performed in the loop are different, you can still select the previous use method, as follows:



In response to Tom's question about cursor and the case of in select: I don't actually allow such syntax here. The goal is simple: we only expand the range of integers after in, and other cases remain the same.
Thank you again for your ideas. Such a discussion is very meaningful!


songjinzhou(2903807914@qq.com)
 
Date: 2023-01-20 00:17
Subject: Re: Re: Support plpgsql multi-range in conditional control


čt 19. 1. 2023 v 16:54 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:


čt 19. 1. 2023 v 15:20 odesílatel 2903807914@qq.com <2903807914@qq.com> napsal:
Hello, thank you very much for your reply. But I think you may have misunderstood what we have done. 

What we do this time is that we can use multiple range ranges (condition_iterator) after in. Previously, we can only use such an interval [lower, upper] after in, but in some scenarios, we may need a list: condition_ iterator[,condition_iterator ...]

condition_iterator:
[ REVERSE ] expression .. expression [ BY expression ] 

then you can use second outer for over an array or just while cycle

I wrote simple example:

create type range_expr as (r int4range, s int);

do
$$
declare re range_expr;
begin
  foreach re in array ARRAY[('[10, 20]', 1), ('[100, 200]', 10)]
  loop
    for i in lower(re.r) .. upper(re.r) by re.s
    loop
      raise notice '%', i;
    end loop;
  end loop;
end;
$$;

But just I don't know what is wrong on

begin
  for i in 10..20
  loop
    raise notice '%', i;
  end loop;

  for i in 100 .. 200 by 10
  loop
    raise notice '%', i;
  end loop;
end;

and if there are some longer bodies you should use function or procedure. Any different cycle is separated. PLpgSQL (like PL/SQL or ADA) are verbose languages. There is no goal to have short, heavy code.

Regards

Pavel



Attachment

pgsql-hackers by date:

Previous
From: Amit Langote
Date:
Subject: Re: generic plans and "initial" pruning
Next
From: Tom Lane
Date:
Subject: Re: generic plans and "initial" pruning