Re: Assigning ROW variable having NULL value to RECORD type variable doesn't give any structure to the RECORD variable.

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.

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com

On Wed, Jan 1, 2020 at 9:29 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> st 1. 1. 2020 v 16:50 odesílatel Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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?
>>
>
> +1
>
> Pavel
>
>
>> --
>> With Regards,
>> Ashutosh Sharma
>> EnterpriseDB:http://www.enterprisedb.com
>>
>>
>>