469,658 Members | 1,791 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,658 developers. It's quick & easy.

Changing from base type to inherited

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
sc********@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
2 1128
Scott Ribe <sc********@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

---------------------------(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

Nov 23 '05 #2
> 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
sc********@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

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Walt Zydhek | last post: by
13 posts views Thread by dragoncoder | last post: by
19 posts views Thread by jan.loucka | last post: by
12 posts views Thread by =?Utf-8?B?RXRoYW4gU3RyYXVzcw==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.