Thread: 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 >
Re: Need some help converting MS SQL stored proc to postgres function
From
Ivan Sergio Borgonovo
Date:
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 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] 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