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
>