Re: Do blocks support transaction control?

[Laurenz Albe]

On Fri, 2022-09-23 at 13:33 +, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/14/sql-do.html
> Description:
> 
> Apparently now DO blocks support COMMIT; - which make them more like
> procedures than functions.
> 
> Tried it with:
> 
> create table z (a int4, b int4);
> insert into z (a,b) select i, i from generate_Series(1,10) i;
> do $$
> declare
> begin
> update z set b = 2;
> commit;
> perform pg_sleep(120);
> end;
> $$ language plpgsql;
> 
> And while it was running, in another psql sessions, I:
> 
> 1. could see b= 2
> 2. could update any of the rows in z.
> 
> Is it documented anywhere? DO docs say that do is like function, which it
> doesn't seem to be?

The documentation says:

  If DO is executed in a transaction block, then the procedure code cannot 
execute
  transaction control statements. Transaction control statements are only 
allowed
  if DO is executed in its own transaction.

That sentence would not make sense if COMMIT were not allowed in a DO statement.
So it is not spelled out, but implicitly clear.

Yours,
Laurenz Albe

sql-select.html ordinal number of an output column.

[jian he]

quote from https://www.postgresql.org/docs/current/sql-select.html

The elements of the PARTITION BY list are interpreted in much the same
> fashion as elements of a GROUP BY
> 
> clause, except that they are always simple expressions and never the name
> or number of an output column.
>


I think "number of an output column" refers to "ordinal number of an output
column".


-- 
 I recommend David Deutsch's <>

  Jian