Thread: Changing from base type to inherited

Changing from base type to inherited

From
Scott Ribe
Date:
Here's my issue:

Data comes in and is inserted into the database automatically, into a base
table A. Later a human looks at the records, fills in some data, and now the
records are assigned to some derived table, B or C. In some cases there is
no way for the software to know at import time which derived type the data
actually is (that determination requires someone to examine an image).

1) Is there any way to change a row of table A into one of the inherited
types, other than deleting and inserting?

2) If I must delete & insert, is there any shortcut to somehow say rec2.A =
rec1 rather than referencing the columns of the base table individually?

In other words, is there a more concise or elegant way to write the
following stored procedure? "Document" is the base table,
"EventMonitorReport" is the derived table.

create or replace function
    "EventMonitorReport_Assign" (int8, int8, int8, int2, timestamp)
returns int8 as '
declare docid alias for $1; ptid alias for $2; drid alias for $3;
        testnum alias for $4; testwhen alias for $5;
declare docrec record;
begin
select into docrec * from "Document" where id = docid;
if found then
    delete from "Document" where id = docid;
    insert into "EventMonitorReport"
        (id, "PatientId", "OriginatedWhen", "ReceivedWhen",
            "CreatedWhen", "CurVersFileId", "Source", "Type", "Status",
            "OurDrId", "TestNum", "TestedWhen", "AssignedWhen")
        values
        (docid, ptid, docrec."OriginatedWhen", docrec."ReceivedWhen",
docrec."CreatedWhen",
            docrec."CurVersFileId", docrec."Source", docrec."Type",
docrec."Status",
            drid, testnum, testwhen, now());
end if;
return docrec.id;
end;
' language 'plpgsql';


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice


Re: Changing from base type to inherited

From
Tom Lane
Date:
Scott Ribe <scott_ribe@killerbytes.com> writes:
> 1) Is there any way to change a row of table A into one of the inherited
> types, other than deleting and inserting?

Nope.

> 2) If I must delete & insert, is there any shortcut to somehow say rec2.A =
> rec1 rather than referencing the columns of the base table individually?

Can't you do something like

    INSERT INTO B SELECT *, some-more-values FROM A WHERE ...

            regards, tom lane

Re: Changing from base type to inherited

From
Scott Ribe
Date:
> Can't you do something like
>
> INSERT INTO B SELECT *, some-more-values FROM A WHERE ...

Yes, that works. I didn't think of trying that because I also need to check
for existence of A and change some values in it if it exists, so I select it
into a record type, and

    insert into B docrec.*, some-more-values;

does not work.

Not using the record type, instead inserting into B as per your suggestion
and immediately updating B, is a lot less code, which was what I asked for.
And in this case there couldn't possibly be a material difference in
performance, so good enough.

Would it be a reasonable feature request to ask for the ability to use
myrecord.* on a record type as a value list?


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice