Re: Assigning ROW variable having NULL value to RECORD type variabledoesn't give any structure to the RECORD variable.
| От | Ashutosh Sharma |
|---|---|
| Тема | Re: Assigning ROW variable having NULL value to RECORD type variabledoesn't give any structure to the RECORD variable. |
| Дата | |
| Msg-id | CAE9k0P=Z=hKwKjy19G+61nqYtot+cKs902QLQb1hfvadRdDbxA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Assigning ROW variable having NULL value to RECORD type variabledoesn't give any structure to the RECORD variable. (Pavel Stehule <pavel.stehule@gmail.com>) |
| Список | pgsql-hackers |
Further, if a table type (a.k.a. composite type or row type) having null value or holding no data in it is assigned to a record variable there is no structure provided to the record variable. However when the same table having no data in it is assigned to the record variable, it does provide structure to the record variable. I mean in both the cases we are assigning null value to the record type so it looks a bit weird to see that in one case we end up providing a proper structure to the record variable but not in the other case. Here is an example illustrating this scenario,
create table t1(a int, b text);
do $$
declare
x t1;
y record;
begin
-- y := x; -- as mentioned earlier this doesn't provide any structure to variable 'y'.
--select * into y from t1; -- this does provide a structure to the variable 'y'.
raise info 'y.a = %', y.a; -- this errors out for 1st statement (y := x) but not for the later one (select ... into)
end;
$$ language plpgsql;
Investigating this revealed that in later case i.e. in case of into clause, although there is no tuple returned by the select query still a tuple descriptor is set by the query which provides the structure to the record variable being written because having non-null tuple descriptor allows the creation of an expanded object for the record variable eventually giving it a structure.
create table t1(a int, b text);
do $$
declare
x t1;
y record;
begin
-- y := x; -- as mentioned earlier this doesn't provide any structure to variable 'y'.
--select * into y from t1; -- this does provide a structure to the variable 'y'.
raise info 'y.a = %', y.a; -- this errors out for 1st statement (y := x) but not for the later one (select ... into)
end;
$$ language plpgsql;
Investigating this revealed that in later case i.e. in case of into clause, although there is no tuple returned by the select query still a tuple descriptor is set by the query which provides the structure to the record variable being written because having non-null tuple descriptor allows the creation of an expanded object for the record variable eventually giving it a structure.
On Wed, Jan 1, 2020 at 9:29 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
st 1. 1. 2020 v 16:50 odesílatel Ashutosh Sharma <ashu.coek88@gmail.com> napsal:Hi All,
When a ROW variable having NULL value is assigned to a RECORD
variable, it gives no structure to the RECORD type variable. Let's
consider the following example.
create table t1(a int, b text);
insert into t1 values(1, 'str1');
create or replace function f1() returns void as
$$
declare
row t1%ROWTYPE;
rec RECORD;
begin
row := NULL;
rec := row;
raise info 'rec.a = %, rec.b = %', rec.a, rec.b;
end;
$$ language plpgsql;
In above example as 'row' variable is having NULL value, assigning
this to 'rec' didn't give any structure to it although 'row' is having
a predefined structure. Here is the error observed when above function
is executed.
select f1();
ERROR: record "rec" is not assigned yet
This started happening from the following git commit onwards,
commit 4b93f57999a2ca9b9c9e573ea32ab1aeaa8bf496
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue Feb 13 18:52:21 2018 -0500
Make plpgsql use its DTYPE_REC code paths for composite-type variables.
I know this is expected to happen considering the changes done in
above commit because from this commit onwards, NULL value assigned to
any row variable represents a true NULL composite value before this
commit it used to be a tuple with each column having null value in it.
But, the point is, even if the row variable is having a NULL value it
still has a structure associated with it. Shouldn't that structure be
transferred to RECORD variable when it is assigned with a ROW type
variable ? Can we consider this behaviour change as a side effect of
the improvement done in the RECORD type of variable?+1Pavel
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
В списке pgsql-hackers по дате отправления: