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
