Changing from base type to inherited

From: Scott Ribe <scott_ribe(at)killerbytes(dot)com>
To: postgres list <pgsql-general(at)postgresql(dot)org>
Subject: Changing from base type to inherited
Date: 2004-08-12 01:28:18
Message-ID: BD4023D2.4DA13%scott_ribe@killerbytes.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 665-7007 voice

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-08-12 01:57:58 Re: Coming soon: PG 7.4.4, 7.3.7, 7.2.5
Previous Message Richard Welty 2004-08-12 01:03:30 Re: PostgreSQL 8.0 Feature List?