pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql-sql@postgresql.org
6+ messages / 4 participants
[nested] [flat]
* pgsql-sql@postgresql.org
@ 1999-03-18 09:30 Frederic Nourry <nourry@ensinfo.univ-nantes.fr>
0 siblings, 0 replies; 6+ messages in thread
From: Frederic Nourry @ 1999-03-18 09:30 UTC (permalink / raw)
To: pgsql-sql
Hi ,
is it possible to write a query that give me the type of each column
in a class .
Thanks ,
Frederic Nourry .
^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql-sql@postgresql.org
@ 2002-08-14 07:28 philip johnson <philip.johnson@atempo.com>
2002-08-14 08:42 ` Re: pgsql-sql@postgresql.org Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-14 14:36 ` Re: pgsql-sql@postgresql.org Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 6+ messages in thread
From: philip johnson @ 2002-08-14 07:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql-sql@postgresql.org
2002-08-14 07:28 pgsql-sql@postgresql.org philip johnson <philip.johnson@atempo.com>
@ 2002-08-14 08:42 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-14 08:00 ` Re: pgsql-sql@postgresql.org philip johnson <philip.johnson@atempo.com>
1 sibling, 1 reply; 6+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-14 08:42 UTC (permalink / raw)
To: philip johnson <philip.johnson@atempo.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql-sql@postgresql.org
2002-08-14 07:28 pgsql-sql@postgresql.org philip johnson <philip.johnson@atempo.com>
2002-08-14 08:42 ` Re: pgsql-sql@postgresql.org Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-08-14 08:00 ` philip johnson <philip.johnson@atempo.com>
0 siblings, 0 replies; 6+ messages in thread
From: philip johnson @ 2002-08-14 08:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql-sql@postgresql.org
2002-08-14 07:28 pgsql-sql@postgresql.org philip johnson <philip.johnson@atempo.com>
@ 2002-08-14 14:36 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-14 14:45 ` Re: pgsql-sql@postgresql.org philip johnson <philip.johnson@atempo.com>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2002-08-14 14:36 UTC (permalink / raw)
To: philip johnson <philip.johnson@atempo.com>; +Cc: pgsql-sql
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';
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pgsql-sql@postgresql.org
2002-08-14 07:28 pgsql-sql@postgresql.org philip johnson <philip.johnson@atempo.com>
2002-08-14 14:36 ` Re: pgsql-sql@postgresql.org Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-08-14 14:45 ` philip johnson <philip.johnson@atempo.com>
0 siblings, 0 replies; 6+ messages in thread
From: philip johnson @ 2002-08-14 14:45 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: 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';
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-08-14 14:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-18 09:30 pgsql-sql@postgresql.org Frederic Nourry <nourry@ensinfo.univ-nantes.fr>
2002-08-14 07:28 pgsql-sql@postgresql.org philip johnson <philip.johnson@atempo.com>
2002-08-14 08:42 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-14 08:00 ` philip johnson <philip.johnson@atempo.com>
2002-08-14 14:36 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-14 14:45 ` philip johnson <philip.johnson@atempo.com>
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