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:

On Sun, 01 Feb 2009 00:10:52 -0800
Mike Christensen <imaudi@comcast.net> wrote:

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

You can have a similarly coincise form using

insert into temp table

http://www.postgresql.org/docs/8.3/interactive/sql-selectinto.html

check what temporary table really means regarding transactions,
functions and connections.

[1]: http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html

If specified, the table is created as a temporary table. Temporary
tables are automatically dropped at the end of a session, or
optionally at the end of the current transaction (see ON COMMIT
below). Existing permanent tables with the same name are not visible
to the current session while the temporary table exists, unless they
are referenced with schema-qualified names. Any indexes created on a
temporary table are automatically temporary as well. Optionally,
GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This makes
no difference in PostgreSQL, but see Compatibility.

of course depending on the context it may be useful to use "on
commit" that seems to be only supported by the "more verbose" create
path.
Still the create path offer some shortcut to avoid to specify the
schema of the temp table.

create table like [1]http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html
and create table as that seems the most promising for your needs
http://www.postgresql.org/docs/8.3/interactive/sql-createtableas.html

not everything is yet as we dream it, but there is still a lot of
syntactic sugar available to exploit.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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: