CODEWITHSUNDEEP
postgresql
aAWnSD
aAWnSD

Reputation: 134

%rowtype in nested `declare...begin...end`

inside an anonymous block, I have nested a declare...begin...end. Inner block cannot "see" a temp table created in outer block. What is best way to solve, still maintaining creating of temp table inside?

The below FAILs with: CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 152

do
$$
declare
 xyz...
being
 create temp table x ... <depending on xyz>
...
 declare
  r x%rowtype -> FAIL
 begin
...
 end;
end;
$$

Upvotes: 0

Views: 452

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45795

It cannot to work. This error is not related to block scopes, but it is related to timing. The temp table is created in runtime, but the x%rowtype is evaluated in validation time (before runtime). There is very simple and robust solution in Postgres. Use record type:

declare r record;
begin

The variable of record type takes real composite type in assign time.

Although PL/pgSQL is similar to PL/SQL, inside it is absolutely different technology, and not all patterns from Oracle are possible or are effective. And sometimes there are different (and sometimes much more comfortable) ways.

Upvotes: 1

Related Questions