From: philip johnson <philip.johnson@atempo.com>
To: pgsql-sql@postgresql.org
Subject: Re: pgsql-sql@postgresql.org
Date: Wed, 14 Aug 2002 10:00:20 +0200
Message-ID: <NDBBJLHHAKJFNNCGFBHLIEENDOAA.philip.johnson@atempo.com> (raw)
In-Reply-To: <GNELIHDDFBOCMGBFGEFOMELDCDAA.chriskl@familyhealth.com.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: philip.johnson@atempo.com
Subject: Re: pgsql-sql@postgresql.org
In-Reply-To: <NDBBJLHHAKJFNNCGFBHLIEENDOAA.philip.johnson@atempo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox