Thread: pgsql-sql@postgresql.org
I've a stored procedure I'd like to migrate from MS SqlServer to Postgresql 7.2.1. How can I do ? here is the stored procedure CREATE PROCEDURE ws_doc_tree_new @parent_node_id int, @label varchar(50), @publication varchar(32) AS DECLARE @doc_exists int, @new_node_id int, @parent_doc_id int, @parent_node_path varchar(250), @parent_tree_levelint /* check if label does not already exists */ SELECT @doc_exists = count(*) FROM ws_doc_tree WHERE ws_doc_tree.parent_node_id= @parent_node_id AND ws_doc_tree.label = @label IF (@doc_exists > 0) BEGIN RAISERROR ('Could not create child node, a folder with the same name already exists',16,1) RETURN 0 END SELECT @parent_node_path = ws_doc_tree.node_path + ws_doc_tree.label + '/', @parent_tree_level = ws_doc_tree.tree_levelFROM ws_doc_tree WHERE ws_doc_tree.node_id = @parent_node_id BEGIN TRANSACTION SELECT @new_node_id = max(ws_doc_tree.node_id) + 1 FROM ws_doc_tree INSERT INTO ws_doc_tree (node_id, label, node_path, parent_node_id, tree_level, publication, creation_dt) VALUES (@new_node_id, @label, @parent_node_path, @parent_node_id, @parent_tree_level+1, @publication, current_timestamp) COMMIT TRANSACTION RETURN @new_node_id
Why not spend 5 minutes reading the documentation and trying to do it yourself before asking us to do your job for you? http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql.html Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of philip johnson > Sent: Wednesday, 14 August 2002 3:29 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] pgsql-sql@postgresql.org > Importance: High > > > I've a stored procedure I'd like to migrate from MS SqlServer > to Postgresql 7.2.1. How can I do ? > here is the stored procedure > CREATE PROCEDURE ws_doc_tree_new > @parent_node_id int, > @label varchar(50), > @publication varchar(32) > AS > > DECLARE > @doc_exists int, > @new_node_id int, > @parent_doc_id int, > @parent_node_path varchar(250), > @parent_tree_level int > > /* check if label does not already exists */ > SELECT > @doc_exists = count(*) > FROM > ws_doc_tree > WHERE > ws_doc_tree.parent_node_id = @parent_node_id > AND ws_doc_tree.label = @label > > IF (@doc_exists > 0) > BEGIN > RAISERROR ('Could not create child node, a folder with the same name > already exists',16,1) > RETURN 0 > END > > SELECT > @parent_node_path = ws_doc_tree.node_path + > ws_doc_tree.label + '/', > @parent_tree_level = ws_doc_tree.tree_level > FROM > ws_doc_tree > WHERE > ws_doc_tree.node_id = @parent_node_id > > BEGIN TRANSACTION > SELECT @new_node_id = max(ws_doc_tree.node_id) + 1 FROM ws_doc_tree > > INSERT INTO ws_doc_tree > (node_id, > label, > node_path, > parent_node_id, > tree_level, > publication, > creation_dt) > VALUES > (@new_node_id, > @label, > @parent_node_path, > @parent_node_id, > @parent_tree_level +1, > @publication, > current_timestamp) COMMIT TRANSACTION > > RETURN @new_node_id > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
because I'm a new to stored procedure I gave only one store procedure, and I've 10 more to convert an example would help me -----Message d'origine----- De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]De la part de Christopher Kings-Lynne Envoyé : mercredi 14 août 2002 10:43 À : philip johnson; pgsql-sql@postgresql.org Objet : Re: [SQL] pgsql-sql@postgresql.org Why not spend 5 minutes reading the documentation and trying to do it yourself before asking us to do your job for you? http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql.html Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of philip johnson > Sent: Wednesday, 14 August 2002 3:29 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] pgsql-sql@postgresql.org > Importance: High > > > I've a stored procedure I'd like to migrate from MS SqlServer > to Postgresql 7.2.1. How can I do ? > here is the stored procedure > CREATE PROCEDURE ws_doc_tree_new > @parent_node_id int, > @label varchar(50), > @publication varchar(32) > AS > > DECLARE > @doc_exists int, > @new_node_id int, > @parent_doc_id int, > @parent_node_path varchar(250), > @parent_tree_level int > > /* check if label does not already exists */ > SELECT > @doc_exists = count(*) > FROM > ws_doc_tree > WHERE > ws_doc_tree.parent_node_id = @parent_node_id > AND ws_doc_tree.label = @label > > IF (@doc_exists > 0) > BEGIN > RAISERROR ('Could not create child node, a folder with the same name > already exists',16,1) > RETURN 0 > END > > SELECT > @parent_node_path = ws_doc_tree.node_path + > ws_doc_tree.label + '/', > @parent_tree_level = ws_doc_tree.tree_level > FROM > ws_doc_tree > WHERE > ws_doc_tree.node_id = @parent_node_id > > BEGIN TRANSACTION > SELECT @new_node_id = max(ws_doc_tree.node_id) + 1 FROM ws_doc_tree > > INSERT INTO ws_doc_tree > (node_id, > label, > node_path, > parent_node_id, > tree_level, > publication, > creation_dt) > VALUES > (@new_node_id, > @label, > @parent_node_path, > @parent_node_id, > @parent_tree_level +1, > @publication, > current_timestamp) COMMIT TRANSACTION > > RETURN @new_node_id > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
On Wed, 14 Aug 2002, philip johnson wrote: > I've a stored procedure I'd like to migrate from MS SqlServer > to Postgresql 7.2.1. How can I do ? > here is the stored procedure > CREATE PROCEDURE ws_doc_tree_new > @parent_node_id int, > @label varchar(50), > @publication varchar(32) Here, you'll need to do something like create function ws_doc_tree_new(int, varchar(50), varchar(32)) returns int as ' To make the labels you'll need to declare aliases in the declare section. > DECLARE > @doc_exists int, > @new_node_id int, > @parent_doc_id int, > @parent_node_path varchar(250), > @parent_tree_level int It's probably best to remove the @'s on all the variables. Also, I think you'll need to use a record type for the select that gets parent_node_path and parent_tree_level since I'm not sure that you can otherwise select into multiple values. I'm not sure if any of your variable names end up being duplicates of columns you use without the tablename anywhere, if so it'll give an error and you'll need to rename them. And you'll need semicolons for each of those lines (rather than commas or nothing). And a BEGIN. > /* check if label does not already exists */ > SELECT > @doc_exists = count(*) > FROM > ws_doc_tree > WHERE > ws_doc_tree.parent_node_id = @parent_node_id > AND ws_doc_tree.label = @label to do the assignment, SELECT INTO doc_exists count(*) ... Again you'll need a semicolon I believe as well. > IF (@doc_exists > 0) IF ... THEN > RAISERROR ('Could not create child node, a folder with the same name > already exists',16,1) RAISE EXCEPTION ''...''; > RETURN 0 > END END IF; > > SELECT > @parent_node_path = ws_doc_tree.node_path + ws_doc_tree.label + '/', > @parent_tree_level = ws_doc_tree.tree_level > FROM > ws_doc_tree > WHERE > ws_doc_tree.node_id = @parent_node_id > > BEGIN TRANSACTION Can't start new transactions in here currently, just remove this and the commit. > SELECT @new_node_id = max(ws_doc_tree.node_id) + 1 FROM ws_doc_tree > > INSERT INTO ws_doc_tree > (node_id, > label, > node_path, > parent_node_id, > tree_level, > publication, > creation_dt) > VALUES > (@new_node_id, > @label, > @parent_node_path, > @parent_node_id, > @parent_tree_level +1, > @publication, > current_timestamp) COMMIT TRANSACTION > > RETURN @new_node_id END;' language 'plpgsql';
thanks very much -----Message d'origine----- De : Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] Envoye : mercredi 14 aout 2002 16:36 A : philip johnson Cc : pgsql-sql@postgresql.org Objet : Re: [SQL] pgsql-sql@postgresql.org On Wed, 14 Aug 2002, philip johnson wrote: > I've a stored procedure I'd like to migrate from MS SqlServer > to Postgresql 7.2.1. How can I do ? > here is the stored procedure > CREATE PROCEDURE ws_doc_tree_new > @parent_node_id int, > @label varchar(50), > @publication varchar(32) Here, you'll need to do something like create function ws_doc_tree_new(int, varchar(50), varchar(32)) returns int as ' To make the labels you'll need to declare aliases in the declare section. > DECLARE > @doc_exists int, > @new_node_id int, > @parent_doc_id int, > @parent_node_path varchar(250), > @parent_tree_level int It's probably best to remove the @'s on all the variables. Also, I think you'll need to use a record type for the select that gets parent_node_path and parent_tree_level since I'm not sure that you can otherwise select into multiple values. I'm not sure if any of your variable names end up being duplicates of columns you use without the tablename anywhere, if so it'll give an error and you'll need to rename them. And you'll need semicolons for each of those lines (rather than commas or nothing). And a BEGIN. > /* check if label does not already exists */ > SELECT > @doc_exists = count(*) > FROM > ws_doc_tree > WHERE > ws_doc_tree.parent_node_id = @parent_node_id > AND ws_doc_tree.label = @label to do the assignment, SELECT INTO doc_exists count(*) ... Again you'll need a semicolon I believe as well. > IF (@doc_exists > 0) IF ... THEN > RAISERROR ('Could not create child node, a folder with the same name > already exists',16,1) RAISE EXCEPTION ''...''; > RETURN 0 > END END IF; > > SELECT > @parent_node_path = ws_doc_tree.node_path + ws_doc_tree.label + '/', > @parent_tree_level = ws_doc_tree.tree_level > FROM > ws_doc_tree > WHERE > ws_doc_tree.node_id = @parent_node_id > > BEGIN TRANSACTION Can't start new transactions in here currently, just remove this and the commit. > SELECT @new_node_id = max(ws_doc_tree.node_id) + 1 FROM ws_doc_tree > > INSERT INTO ws_doc_tree > (node_id, > label, > node_path, > parent_node_id, > tree_level, > publication, > creation_dt) > VALUES > (@new_node_id, > @label, > @parent_node_path, > @parent_node_id, > @parent_tree_level +1, > @publication, > current_timestamp) COMMIT TRANSACTION > > RETURN @new_node_id END;' language 'plpgsql';