Re: Need some help converting MS SQL stored proc to postgres function

Thanks!  You might be onto something, I see two potential problems though:

1) If the nested select returns no rows (no one has rated the recipe
before), it would try to set the value to null.  The Rating column is
non-nullable which is the way I want it.

2) I'm not exactly 100% sure on this, but I think this query will end up
locking every row in the recipes table which could be tens of thousands,
and create some perf issues or deadlocks.  Even though I run this query
once per day to update ratings, I'd like to keep it as streamlined as
possible..

Mike

Tino Wildenhain wrote:
> Hi,
>
> Mike Christensen wrote:
>> Hi guys, I'm in the process of migrating my database from MS SQL 2005
>> to PostgreSQL and there's one final stored proc that's giving me some
>> problems..  Perhaps someone can give me some help?  Here's the sproc:
>>
>>  SELECT
>>    RecipeId, Avg(Rating) as Rating
>>  INTO #ratings
>>  FROM RecipeRatings GROUP BY RecipeId
>>
>>  UPDATE Recipes
>>    SET Rating = #ratings.Rating FROM Recipes INNER JOIN #ratings ON
>> (#ratings.RecipeId = Recipes.RecipeId AND #ratings.Rating <>
>> Recipes.Rating)
>
> would not
>
> UPDATE receipes
>    SET rating = r.rating
>   FROM (SELECT recipeid,avg(rating) as rating
>         GROUP BY recipeid) r
>   WHERE recipeid=r.recipeid
>     AND rating <> r.rating
>
> work too w/o temp table?
> (untested, can contain errors)
>
> Tino