Re: plpgsql - DECLARE - cannot to use %TYPE or %ROWTYPE for composite types - Mailing list pgsql-hackers

From David G. Johnston
Subject Re: plpgsql - DECLARE - cannot to use %TYPE or %ROWTYPE for composite types
Date
Msg-id CAKFQuwbthufj8SZK329pnZtQ_9hXY=kMEnhea+SWbTeKEwPViA@mail.gmail.com
Whole thread Raw
In response to Re: plpgsql - DECLARE - cannot to use %TYPE or %ROWTYPE for composite types  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: plpgsql - DECLARE - cannot to use %TYPE or %ROWTYPE for composite types
List pgsql-hackers
On Wed, Mar 16, 2016 at 4:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 3/3/16 4:51 AM, Pavel Stehule wrote:
>> CREATE TABLE a(a int);
>> CREATE TABLE b(a a.a%TYPE)
>>
>> And the people expecting the living relation between table a and table
>> b. So when I do ALTER a.a, then b.a should be changed. What if I drop
>> a.a or drop a?
>>
>> So this is reason, why I don't would this feature in SQL side.

> I don't buy that. plpgsql doesn't work that way, so why would this?
> *especially* with the %TYPE decorator.

Yeah.  The %TYPE decorator doesn't work like that in the core parser
either: when you use it, the referenced type is determined immediately
and then it's just as if you'd written that type name to begin with.

I'm missing something here...%TYPE ends up getting parsed repeatedly and so appears to be change if the variable upon which it is based changes - even if once parsed it remains constant for the lifetime of the function's evaluation.​

I guess what is being said is that the "constant" behavior in SQL ends up being permanent because a given statement is only ever conceptually parsed and executed a single time - unlike a function body.  The nature of any solution would still have the same characteristics within a function because the inherent re-parsing nature and not because of any direct capability of %TYPE itself.

I do not see a reason for any of these "type operators" to work
differently.

Another analogy that might help make the point is

        set search_path = a;
        create table myschema.tab(f1 mytype);
        set search_path = b;

If there are types "mytype" in both schemas a and b, is myschema.tab.f1
now of type b.mytype?  No.  The meaning of the type reference is
determined when the command executes, and then you're done.
And its no different than our treatment of "*"

CREATE VIEW test_view
SELECT *
FROM temp_table;

Adding columns to temp_table doesn't impact which columns the view returns.

David J.​
 


pgsql-hackers by date:

Previous
From: David Steele
Date:
Subject: Re: proposal: make NOTIFY list de-duplication optional
Next
From: Tom Lane
Date:
Subject: Re: Re: [PATCH] Integer overflow in timestamp[tz]_part() and date/time boundaries check