Thread: Trigger on select :-(

What I want is to have a trigger on select, but since I have searched the
archives I know that is not possible.  I also read that view rules is the
technology is whats available.  Having never used views before I can't seem
to get my mind to figure out what I need to do to solve my problem.

I have a table that defines a key that I will use in another table in a list
of items defined in the previous table.

The tables are

CREATE TABLE Foo ( INTEGER key not null primary key,
                                  ...  characteristics for the Foo item );

CREATE TABLE Catalog ( INTEGER FooKey FOREIGN KEY,
                                        ... other catalog data );

The catalog will have rows that reference the elements in the Foo table.

I also have another table that references the Foo table

CREATE TABLE ToDo ( INTEGER FooKey FOREIGN KEY,
                                    ... other ToDo information.

What I want to do is when I do a SELECT on the Catalog and deliver the
result to the user, I want to check to see if the FooKey is in the users
ToDo table and set the value of a column isToDo to true or false depending
on if the FooKey exists in the ToDo table for the user.  If I were building
a table I would use a trigger and select on the row in the ToDo table.

Please, all I want is an idea where to start, what I should read and I will
figure out how to do it.

Thanks in advance.

<p dir="LTR"><span lang="en-us"><font color="#1F497D" face="Calibri">Are you able to set up a SELECT query to
get</font></span><spanlang="en-us"> <font color="#1F497D" face="Calibri">the</font></span><span lang="en-us"><font
color="#1F497D"face="Calibri"> result you want?  If you are, it</font></span><span lang="en-us"><font color="#1F497D"
face="Calibri">’</font></span><spanlang="en-us"><font color="#1F497D" face="Calibri">s as simple as
putting</font></span><spanlang="en-us"> <font color="#1F497D" face="Calibri">“</font></span><span lang="en-us"><font
color="#1F497D"face="Calibri">CREATE VIEW</font></span><span lang="en-us"> <font color="#1F497D"
face="Calibri">viewnameAS</font></span><span lang="en-us"> <font color="#1F497D" face="Calibri">“</font></span><span
lang="en-us"><fontcolor="#1F497D" face="Calibri">, followed by your query.</font></span><p dir="LTR"><span
lang="en-us"><fontcolor="#1F497D" face="Calibri">Garrett Murphy</font></span><span lang="en-us"></span><p
dir="LTR"><spanlang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span lang="en-us"></span><span
lang="en-us"><fontface="Tahoma" size="2">_____________________________________________<br /></font></span><span
lang="en-us"><b></b></span><spanlang="en-us"><b></b></span><span lang="en-us"><b><font face="Tahoma"
size="2">From:</font></b></span><spanlang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font
face="Tahoma"size="2"> pgsql-sql-owner@postgresql.org [<a
href="mailto:pgsql-sql-owner@postgresql.org">mailto:pgsql-sql-owner@postgresql.org</a>]</font></span><span
lang="en-us"><b></b></span><spanlang="en-us"><b></b></span><span lang="en-us"><b> <font face="Tahoma" size="2">On
BehalfOf</font></b></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"> <font face="Tahoma"
size="2">RayMadigan<br /></font></span><span lang="en-us"><b></b></span><span lang="en-us"><b></b></span><span
lang="en-us"><b><fontface="Tahoma" size="2">Sent:</font></b></span><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Tahoma" size="2"> Monday, March 01, 2010 7:11 PM<br
/></font></span><spanlang="en-us"><b></b></span><span lang="en-us"><b></b></span><span lang="en-us"><b><font
face="Tahoma"size="2">To:</font></b></span><span lang="en-us"></span><span lang="en-us"></span><span lang="en-us"><font
face="Tahoma"size="2"> pgsql-sql@postgresql.org<br /></font></span><span lang="en-us"><b></b></span><span
lang="en-us"><b></b></span><spanlang="en-us"><b><font face="Tahoma" size="2">Subject:</font></b></span><span
lang="en-us"></span><spanlang="en-us"></span><span lang="en-us"><font face="Tahoma" size="2"> [SQL] Trigger on select
:-(</font></span><pdir="LTR"><span lang="en-us"></span><p dir="LTR"><span lang="en-us"></span><span
lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2">What I want is to have a trigger on select, but since
Ihave searched the archives I know that is not possible.  I also read that view rules is the technology is whats
available. Having never used views before I can't seem to get my mind to figure out what I need to do to solve my
problem.</font></span><pdir="LTR"><span lang="en-us"><font face="Arial" size="2">I have a table that defines a key that
Iwill use in another table in a list of items defined in the previous table.</font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">The tables are</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">CREATETABLE Foo ( INTEGER key not null primary key,</font></span><p dir="LTR"><span lang="en-us"><font
face="Arial"size="2">                                  ...  characteristics for the Foo item );</font></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">CREATE TABLE Catalog ( INTEGER FooKey FOREIGN
KEY,</font></span><pdir="LTR"><span lang="en-us"><font face="Arial" size="2">                                       
...other catalog data );</font></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">The catalog will have
rowsthat reference the elements in the Foo table.</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">Ialso have another table that references the Foo table</font></span><p dir="LTR"><span lang="en-us"><font
face="Arial"size="2">CREATE TABLE ToDo ( INTEGER FooKey FOREIGN KEY,</font></span><p dir="LTR"><span lang="en-us"><font
face="Arial"size="2">                                    ... other ToDo information.</font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">What I want to do is when I do a SELECT on the Catalog and deliver the result
tothe user, I want to check to see if the FooKey is in the users ToDo table and set the value of a column isToDo to
trueor false depending on if the FooKey exists in the ToDo table for the user.  If I were building a table I would use
atrigger and select on the row in the ToDo table.</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">Please,all I want is an idea where to start, what I should read and I will figure out how to do
it.</font></span><pdir="LTR"><span lang="en-us"><font face="Arial" size="2">Thanks in advance. << File:
ATT4311246.txt>> </font></span> 

On Mon, Mar 1, 2010 at 9:10 PM, Ray Madigan <ray@madigans.org> wrote:
>
> What I want to do is when I do a SELECT on the Catalog and deliver the
> result to the user, I want to check to see if the FooKey is in the users
> ToDo table and set the value of a column isToDo to true or false depending
> on if the FooKey exists in the ToDo table for the user.

you should do this when inserting data (with triggers of course)

you can't use rules because a RULE ON SELECT you only can use one
SELECT, no INSERT, UPDATE nor DELETE

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157