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

Figured out one way to do it, perhaps I can get some feedback on if this
is the best way..  Thanks!

CREATE TEMP TABLE temp_ratings
(
  RecipeId uuid,
  Rating smallint,
  CONSTRAINT id_pk PRIMARY KEY (RecipeId)
);

INSERT INTO temp_ratings(RecipeId, Rating)
  SELECT RecipeId, Avg(Rating) as Rating FROM RecipeRatings GROUP BY
RecipeId;

UPDATE Recipes
  SET Rating = tr.Rating
  FROM temp_ratings as tr
  WHERE Recipes.RecipeId = tr.RecipeId AND Recipes.Rating <> tr.Rating

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)
>
>  DROP TABLE #ratings
>
> The error is:
>
> ERROR:  syntax error at or near "#"
> LINE 3:   INTO #ratings
>               ^
>
> ********** Error **********
>
> ERROR: syntax error at or near "#"
> SQL state: 42601
> Character: 53
>
> Perhaps there's a different way to create temp tables?  Even better is
> if someone can re-write the query to not use the temp table, I'm far
> from a SQL expert.  Thanks!!
>
> Mike
>