From: "philip johnson" <philip.johnson@atempo.com>
To: <pgsql-sql@postgresql.org>
Subject: pgsql-sql@postgresql.org
Date: Wed, 14 Aug 2002 09:28:51 +0200
Message-ID: <NDBBJLHHAKJFNNCGFBHLEEEMDOAA.philip.johnson@atempo.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
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