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

[Tino Wildenhain]

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

smime.p7s
Description: S/MIME Cryptographic Signature