I have a query that takes two tables (join) and does something on it.
Lets say these tables are A and B.
What I need is that everytime one of the tables (A) has its rows
selected, I want to update the count (which is a column in A) for that
row.

I am not sure what is the best way to do this.
1)I could create a dummy view and do a RULE on that (I am not sure how
to get access to the row's that have been selected).
2) I could create a storedprocedure. Get all the rows to be returned
and then, loop through the rows and update the column and then return
it.

Q1) Which way is better?
q2) How does one get access to the rows just selected in the CREATE
RULE computation?

thanks.
-assad

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Jan 17, 2006 at 09:55:42PM -0700, Assad Jarrahian wrote:
> I have a query that takes two tables (join) and does something on it.
> Lets say these tables are A and B.
> What I need is that everytime one of the tables (A) has its rows
> selected, I want to update the count (which is a column in A) for that
> row.
>
> I am not sure what is the best way to do this.
> 1)I could create a dummy view and do a RULE on that (I am not sure how
> to get access to the row's that have been selected).
> 2) I could create a storedprocedure. Get all the rows to be returned
> and then, loop through the rows and update the column and then return
> it.
>
> Q1) Which way is better?
> q2) How does one get access to the rows just selected in the CREATE
> RULE computation?

Via NEW and OLD. Read Chapter 34 of the documentation.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

http://archives.postgresql.org

On 2006-01-19, "Jim C. Nasby" <jnasby@pervasive.com> wrote:
> On Tue, Jan 17, 2006 at 09:55:42PM -0700, Assad Jarrahian wrote:
>> q2) How does one get access to the rows just selected in the CREATE
>> RULE computation?
>
> Via NEW and OLD. Read Chapter 34 of the documentation.

He's talking about a SELECT rule. A SELECT rule is no more and no less than
a view - you can do nothing with select rules that you can't do with a
normal view.

(You can only have one SELECT rule, it must be named _RETURN, and it must
be a DO INSTEAD SELECT rule. If you put such a rule on a plain table, you
will find that the table changes into a view.)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services