--0016e659f4d89e6593048dbae1e6
Content-Type: text/plain; charset=UTF-8

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

--0016e659f4d89e6593048dbae1e6
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi Fellows,<div><br></div><div>I have a bunch of tables which I need to per=
form a Full Text Search.=C2=A0</div><div>The approach I am using is to inse=
rt into another table (the searcheable table): =C2=A0tsvector information, =
tableOID and the record Id</div>
<div><br></div><div>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 ins=
tead of the actual tableoid.</div><div><br></div><div>Insert statement look=
s something like this:</div>
<div><div>INSERT INTO fts.fdata(tbl_oid, id, vector_info)</div><div>VALUES =
(NEW.tableoid, NEW.id, NEW.vector_info)</div></div><div><br></div><div>So h=
ow can I get the tableoid in a trigger function ?</div><div><br></div><div>
<br></div><div>regards,</div><div><br></div><div><br></div><div>Ricardo</di=
v><div><br></div>

--0016e659f4d89e6593048dbae1e6--

--0016364ef6102d8057048dbb3a51
Content-Type: text/plain; charset=UTF-8

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 [at] 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
>
>

--0016364ef6102d8057048dbb3a51
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

By the way,<div><br></div><div>Another option to do this, is to do some tab=
le Inheritance. So Full Text Search is perform on each table.</div><div><br=
></div><div>So which scenario is better ? Triggers which insert data into a=
third table, =C2=A0or Inheritance ?</div>
<div><br></div><div>Thanks in advanced !</div><div><br></div><div><br></div=
><div>Ricardo.</div><div><br><div class=3D"gmail_quote">On Fri, Aug 13, 201=
0 at 6:08 PM, Ricardo Bayley <span dir=3D"ltr"><<a href=3D"mailto:ricard=
o.bayley [at] gmail.com">ricardo.bayley [at] gmail.com</a>></span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex;">Hi Fellows,<div><br></div><div>I have a bun=
ch of tables which I need to perform a Full Text Search.=C2=A0</div><div>Th=
e approach I am using is to insert into another table (the searcheable tabl=
e): =C2=A0tsvector information, tableOID and the record Id</div>

<div><br></div><div>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 ins=
tead of the actual tableoid.</div><div><br></div><div>Insert statement look=
s something like this:</div>

<div><div>INSERT INTO fts.fdata(tbl_oid, id, vector_info)</div><div>VALUES =
(NEW.tableoid, NEW.id, NEW.vector_info)</div></div><div><br></div><div>So h=
ow can I get the tableoid in a trigger function ?</div><div><br></div>
<div>
<br></div><div>regards,</div><div><br></div><font color=3D"#888888"><div><b=
r></div><div>Ricardo</div><div><br></div>
</font></blockquote></div><br></div>

--0016364ef6102d8057048dbb3a51--

Ricardo Bayley <ricardo.bayley [at] 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

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--001636416ae3e26973048dbbae95
Content-Type: text/plain; charset=UTF-8

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 [at] sss.pgh.pa.us> wrote:

> Ricardo Bayley <ricardo.bayley [at] 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
>

--001636416ae3e26973048dbbae95
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Thanks Tom that was exactly what I was looking for.<div><br></div><div>Do y=
ou think that having just 1 table to perform the Full Text Search is a bett=
er approach than having 1 master table with child tables, and always queryi=
ng the Master one ? Which do you believe is faster ?</div>
<div><br></div><div>regards</div><div><br><div class=3D"gmail_quote">On Fri=
, Aug 13, 2010 at 6:45 PM, Tom Lane <span dir=3D"ltr"><<a href=3D"mailto=
:tgl [at] sss.pgh.pa.us">tgl [at] sss.pgh.pa.us</a>></span> wrote:<br><blockquote =
class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid=
;padding-left:1ex;">
<div class=3D"im">Ricardo Bayley <<a href=3D"mailto:ricardo.bayley [at] gmail=
..com">ricardo.bayley [at] gmail.com</a>> writes:<br>
> I do this with triggers. This issue I have is that the tableoid data c=
annot<br>
> be used with the NEW keyword. I always get value 0 instead of the actu=
al<br>
> tableoid.<br>
<br>
</div>That might work in an AFTER trigger, but it definitely won't work=
in a<br>
BEFORE trigger, because the NEW row isn't actually part of the table at=
<br>
that point. =C2=A0You'd probably be better off to rely on the trigger<b=
r>
function TG_RELID parameter instead, anyway.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane<br>
</blockquote></div><br></div>

--001636416ae3e26973048dbbae95--