Обсуждение: Need some help converting MS SQL stored proc to postgres function

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

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

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