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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Scott Ribe <scott_ribe@killerbytes.com> writes:[color=blue]
> 1) Is there any way to change a row of table A into one of the inherited
> types, other than deleting and inserting?[/color]

Nope.
[color=blue]
> 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?[/color]

Can't you do something like

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

> Can't you do something like[color=blue]
>
> INSERT INTO B SELECT *, some-more-values FROM A WHERE ...[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org