pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNEW + tableOID
4+ messages / 2 participants
[nested] [flat]
* NEW + tableOID
@ 2010-08-13 21:08 Ricardo Bayley <ricardo.bayley@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ricardo Bayley @ 2010-08-13 21:08 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: NEW + tableOID
@ 2010-08-13 21:33 Ricardo Bayley <ricardo.bayley@gmail.com>
parent: Ricardo Bayley <ricardo.bayley@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Ricardo Bayley @ 2010-08-13 21:33 UTC (permalink / raw)
To: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: NEW + tableOID
@ 2010-08-13 21:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ricardo Bayley <ricardo.bayley@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-08-13 21:45 UTC (permalink / raw)
To: Ricardo Bayley <ricardo.bayley@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: NEW + tableOID
@ 2010-08-13 22:05 Ricardo Bayley <ricardo.bayley@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Ricardo Bayley @ 2010-08-13 22:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-08-13 22:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-13 21:08 NEW + tableOID Ricardo Bayley <ricardo.bayley@gmail.com>
2010-08-13 21:33 ` Ricardo Bayley <ricardo.bayley@gmail.com>
2010-08-13 21:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-13 22:05 ` Ricardo Bayley <ricardo.bayley@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox