[GENERAL] Inheritance and such

[John Hughes]

Hi everyone,

I ran into a brick wall when I realized that inheritance in postgres isnt 
really there...

Here's a description of the relevant part of my schema.

CREATE TABLE base (
id serial not null primary key,

);

CREATE TABLE specialized (

) INHERITS base;

CREATE TABLE specialized2 (

) INHERITS base;

CREATE TABLE events (
id serial not null primary key,
baseid int references base(id)

);

This does not work if I load a bunch of records into specialized and then try 
to refer to the id's through the event's table because Postgres doesn't do 
N-table indexes where N is greater than 1 :-P

This whole part of my schema, if it worked, would be so incredibly useful, as 
I would be able to add as many specialized tables as needed, and still use 
the same sql to create events for them, and access their base table's data, 
etc.

So I have 3 questions: Is any work being done currently to fix our 
implementation of inheritance? How much work would it take to do this? and 
lastly, how can I implement my schema to do something similar, emulated, or 
otherwise that will work with postgres as it is now?

Thanks,

John Hughes
Wetleads.com

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

   http://archives.postgresql.org

[GENERAL] Major Performance issue

[John Hughes]

Hi, I've been working the past couple of days to import a bunch of csv data 
into my system.

I have a temp_holding table which basically just matches the csv format.

I have a trigger before insert on each row on the temp_holding table, below.

The problem is that inserting into temp_holding takes more than a SECOND PER 
ROW!

What can I do to speed this up, because importing 106 records is going to take 
a day and a half???

Kind regards,

John Hughes

CREATE FUNCTION data_import() RETURNS "trigger"
AS '
DECLARE
  User RECORD;
  check RECORD;
BEGIN
  SELECT INTO check * FROM core_contacts WHERE primaryphone = NEW.number; -- 
Checks for duplicate entries
  IF NOT FOUND THEN
SELECT INTO User id FROM core_users WHERE username = NEW.username;

IF User.id IS NULL THEN
  User.id := 37;
END IF;

INSERT INTO core_contacts (
  primaryphone,
  zip,
  regionid,
  city,
  address,
  firstname,
  lastname)
VALUES (
  NEW.number,
  NEW.zip,
  (SELECT id FROM core_regions WHERE name = NEW.state),
  NEW.city,
  NEW.street,
  NEW.first_name,
  NEW.last_name);

INSERT INTO core_leads (contactid, leadstatustypeid, createdbyuserid, 
leadtypeid, notes)
  VALUES ( max(core_contacts.id),
   1,
   User.id,
   1,
   NEW.agent_comments );
IF NEW.lead_date = '''' THEN
  NEW.lead_date := ''now'';
END IF;
INSERT INTO core_leadevents ( leadid, leadeventtypeid, userid, created, 
notes )
  VALUES ( max(core_leads.id),
   4,
   User.id,
   date(NEW.lead_date),
   ''Imported on ''+''now'' );

INSERT INTO core_leadattributes (value, attributetypeid, leadid)
  VALUES ( NEW.loan_amount, 18, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
  VALUES ( NEW.property_value, 1, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
  VALUES ( NEW.interest_rate, 14, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
  VALUES ( NEW.interest_type, 13, max(core_leads.id));
  ELSE
NEW.duplicate = true;
  END IF;
  RETURN NEW;
END;
'
LANGUAGE plpgsql;

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly