Re: 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(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of philip johnson
> Sent: Wednesday, 14 August 2002 3:29 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org
>