Re: pgsql-sql@postgresql.org - Mailing list pgsql-sql

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';