Обсуждение: NEW + tableOID

Поиск
Список
Период
Сортировка

NEW + tableOID

От
Ricardo Bayley
Дата:
Hi Fellows,

I have a bunch of tables which I need to perform a Full Text Search. 
The approach I am using is to insert into another table (the searcheable table):  tsvector information, tableOID and the record Id

I do this with triggers. This issue I have is that the tableoid data cannot be used with the NEW keyword. I always get value 0 instead of the actual tableoid.

Insert statement looks something like this:
INSERT INTO fts.fdata(tbl_oid, id, vector_info)
VALUES (NEW.tableoid, NEW.id, NEW.vector_info)

So how can I get the tableoid in a trigger function ?


regards,


Ricardo

Re: NEW + tableOID

От
Ricardo Bayley
Дата:
By the way,

Another option to do this, is to do some table Inheritance. So Full Text Search is perform on each table.

So which scenario is better ? Triggers which insert data into a third table,  or Inheritance ?

Thanks in advanced !


Ricardo.

On Fri, Aug 13, 2010 at 6:08 PM, Ricardo Bayley <ricardo.bayley@gmail.com> wrote:
Hi Fellows,

I have a bunch of tables which I need to perform a Full Text Search. 
The approach I am using is to insert into another table (the searcheable table):  tsvector information, tableOID and the record Id

I do this with triggers. This issue I have is that the tableoid data cannot be used with the NEW keyword. I always get value 0 instead of the actual tableoid.

Insert statement looks something like this:
INSERT INTO fts.fdata(tbl_oid, id, vector_info)
VALUES (NEW.tableoid, NEW.id, NEW.vector_info)

So how can I get the tableoid in a trigger function ?


regards,


Ricardo


Re: NEW + tableOID

От
Tom Lane
Дата:
Ricardo Bayley <ricardo.bayley@gmail.com> writes:
> I do this with triggers. This issue I have is that the tableoid data cannot
> be used with the NEW keyword. I always get value 0 instead of the actual
> tableoid.

That might work in an AFTER trigger, but it definitely won't work in a
BEFORE trigger, because the NEW row isn't actually part of the table at
that point.  You'd probably be better off to rely on the trigger
function TG_RELID parameter instead, anyway.

            regards, tom lane

Re: NEW + tableOID

От
Ricardo Bayley
Дата:
Thanks Tom that was exactly what I was looking for.

Do you think that having just 1 table to perform the Full Text Search is a better approach than having 1 master table with child tables, and always querying the Master one ? Which do you believe is faster ?

regards

On Fri, Aug 13, 2010 at 6:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ricardo Bayley <ricardo.bayley@gmail.com> writes:
> I do this with triggers. This issue I have is that the tableoid data cannot
> be used with the NEW keyword. I always get value 0 instead of the actual
> tableoid.

That might work in an AFTER trigger, but it definitely won't work in a
BEFORE trigger, because the NEW row isn't actually part of the table at
that point.  You'd probably be better off to rely on the trigger
function TG_RELID parameter instead, anyway.

                       regards, tom lane