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

[Tino Wildenhain <tino@xxxxxxxxxxxxx>]

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

<<attachment: smime.p7s>>