[SQL] [email protected]
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 [EMAIL PROTECTED]
Re: [SQL] [email protected]
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: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of philip johnson
> Sent: Wednesday, 14 August 2002 3:29 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] [EMAIL PROTECTED]
> 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 [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [email protected]
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 : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]De la part de Christopher
Kings-Lynne
Envoyé : mercredi 14 août 2002 10:43
À : philip johnson; [EMAIL PROTECTED]
Objet : Re: [SQL] [EMAIL PROTECTED]
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: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of philip johnson
> Sent: Wednesday, 14 August 2002 3:29 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] [EMAIL PROTECTED]
> 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 [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Explicite typecasting of functions
Hello, I want to insert new data into a table with increasing data set ids. The table has two separate "regions" of data: Those with Ids below 100 and other. If I want to create a new Id in the "lower region" I tried the following (simplified example): CREATE TABLE Items ( Idint DEFAULT NextItem() ) ; /* ERROR: Function 'nextitem()' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts */ CREATE FUNCTION NextItem() RETURNS INT4 AS 'select max(Id)+1 from Items where Id < 100;' LANGUAGE 'sql'; I did not found any trace of documentation how to do an explicit typecast for the function. Defining the function first fails because: ERROR: Relation "items" does not exist Any hint to solve this kind of chicken-egg-problem? Kind regards Andreas. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] concurrent connections is worse than serialization?
On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote: > I have a testing program that uses 30 concurrent connections > (max_connections = 32 in my postgresql.conf) and each does 100 > insertions to a simple table with index. > > It took me approximately 2 minutes to finish all of them. > > But under the same environment(after "delete From test_table, and vacuum > analyze"), I then queue up all those 30 connections one after another > one (serialize) and it took only 30 seconds to finish. > > Why is it that the performance of concurrent connections is worse than > serializing them into one? What was the limiting factor during the test? Was the CPU maxed, memory, disk I/O? I take it the insert really *is* simple - no dependencies etc. > I was testing them using our own (proprietary) scripting engine and the > extension library that supports postgresql serializes the queries by > simply locking when a query manipulates a PGconn object and unlocking > when it is done. (And similiarly, it creates a PGconn object on the > stack for each concurrent queries.) I assume you've ruled the application end of things out. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Explicite typecasting of functions
On Wednesday 14 Aug 2002 10:30 am, Andreas Tille wrote:
> Hello,
>
> I want to insert new data into a table with increasing data set ids.
> The table has two separate "regions" of data: Those with Ids below
> 100 and other. If I want to create a new Id in the "lower region"
> I tried the following (simplified example):
>
>
> CREATE TABLE Items (
> Idint DEFAULT NextItem()
> CREATE FUNCTION NextItem() RETURNS INT4
> AS 'select max(Id)+1 from Items where Id < 100;'
> LANGUAGE 'sql';
>ERROR: Relation "items" does not exist
>
> Any hint to solve this kind of chicken-egg-problem?
Your solution is not safe anyway - you could end up with two processes trying
to insert the next value.
Can I suggest two sequences: item_low_seq and item_high_seq? Set the initial
value of each to 1,000,000 and 99,000,000 (or whatever) and then use
whichever sequence is appropriate.
In the example above you'd want something like:
id int not null default nextval('item_low_seq')
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] parser: parse error at or near "$1"
Hi , I'm getting this error : Error occurred while executing PL/pgSQL function ins_schedule_status line 42 at SQL statementparser: parse error at or near "$1" Unable to locate the cause. Please any hint or clue will be of great help. Below is the function CREATE FUNCTION ins_schedule_status(int4,time without time zone,varchar(256),int2,int2,int2) RETURNS int4 AS 'DECLARE schedule_id ALIAS FOR $1; var_current_time ALIAS FOR $2; status ALIAS FOR $3; status_code ALIAS FOR $4; module ALIAS FOR $5; var_level ALIAS FOR $6 ; last_status varchar(256) ; last_code int2; level_value int2 ;BEGIN IF ( var_level != 0 )THEN --select level_value =var_level; level_value :=var_level; ELSE IF ( status_code = 0 or status_code < 0 )THEN --select level_value = 1 ; level_value := 1 ; ELSE --select level_value = 3 ; level_value := 3 ; END IF; END IF; -- dont report server control values, just positive values IF ( status_code < 0 ) THEN --select status_code = null; --status_code := null; -- check for the exact same status string reported -- previously and skip it if found select into last_status top 1 status, last_code status_code from status_log where schedule_id =schedule_id order by complete_time desc ; if ( last_status = status and ( ( last_code is null and status_code is null ) or last_code = status_code ) ) THEN return ; END IF; END IF; insert into status_log ( log_id, computer_id,schedule_id,status, status_code, module, level, user_id, schedule_time, complete_time ) select 1, computer_id, schedule_id, status, status_code, module, level_value, user_id, start_time, var_current_time from event_schedule where schedule_id =schedule_id; return status_code; END;'LANGUAGE 'plpgsql' ; Original MS _SQL stored procedure create procedure ins_schedule_status @schedule_id int, @current_time datetime, @status varchar(254), @status_code smallint, @module smallint, @level smallint = 0as declare @level_value smallint if ( @level != 0 ) select @level_value = @level else begin if ( @status_code = 0 or @status_code < 0 ) select @level_value = 1 else select @level_value = 3 end -- don't report server control values, just positive values if ( @status_code < 0 ) select @status_code = null -- check for the exact same status string reported -- previously and skip it if found declare @last_status varchar(256) declare @last_code smallint select top 1 @last_status = status, @last_code = status_code from status_log where schedule_id = @schedule_id order by complete_time desc if ( @last_status = @status and ( ( @last_code is null and @status_code is null ) or @last_code = @status_code ) ) return declare @tran int set @tran = @@trancount if (@tran = 0) begin transaction insert into status_log ( computer_id, schedule_id, status, status_code, [module], [level], [user_id], schedule_time, complete_time ) select computer_id, schedule_id, @status, @status_code, @module, @level_value, user_id, start_time, @current_time from event_schedule where schedule_id = @schedule_id if (@@error != 0) begin if (@tran = 0) rollback transaction end if (@tran = 0) commit transaction GO Regards, -Sugandha
Re: [SQL] Few Queries
Hi ,
No luck . Even with Select Into . Please if
any body has faced similar problem and knows a solution.
CREATE FUNCTION del_old_history() RETURNS int4 AS
'declare var_history_age_limit int4;
set_time datetime;
BEGIN select into
var_history_age_limit history_age_limit from database_info;IF
(var_history_age_limit is not null) THEN
--set_time :=select current_date()+ INTERVAL
'1 day'); select into set_time
current_date()+ INTERVAL ''$var_history_age_limit days
'';
delete from history
where complete_time <= set_time;END IF;
return
true;
END;'LANGUAGE 'plpgsql';
Regards,
-Sugandha
- Original Message -
From: "Janning Vygen" <[EMAIL PROTECTED]>
To: "Sugandha Shah" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Wednesday, August 14, 2002 12:32
PM
Subject: Re: [SQL] Few Queries
> Am Mittwoch, 14. August 2002 07:05 schrieb Sugandha Shah:>
> 1. I 'm firing a query and it returns the value in variable which I>
> need to pass to other query . Is this a right way to pass the>
> value ? I'm newbie to this Database and hence
facing lot of> > syntax problems.> >> > CREATE
FUNCTION del_old_history() RETURNS bool AS '> > declare>
> var_history_age_limit int4;>
> set_time datetime;> >
BEGIN> > select into var_history_age_limit history_age_limit
from> > database_info; IF (var_history_age_limit is not
null) THEN> > set_time
:= select current_date()+ INTERVAL ' '> > If you do a aselect you
need select into. Normal assignment is only > possible with simple
expression. Try:> > SELECT INTO set_time current_date()+ INTERVAL
' ';> > > 2. Is there any equiavlent of MS -SQLServer
'trancount ' in> > postgres ?> > you should only post
one question per mail and i dont know waht > trancount is. >
> do you mean something like getting the affected rows?> Look at
this file in the postgresdocs (7.2.1)>
plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS> > > 3.
if object_id ('database_info') is null> > how is
above statement verified in postgres . I tried looking> > for OID
.> > same as answer to question number 2. > something
like > GET DIAGNOSTICS var_oid = RESULT_OID;> IF var_oid IS
NULL THEN> > janning> > -- > PLANWERK 6
/websolutions> Herzogstraße 86> 40215 Düsseldorf> >
fon 0211-6015919> fax 0211-6015917> http://www.planwerk6.de>
> ---(end of
broadcast)---> TIP 1: subscribe and unsubscribe
commands go to [EMAIL PROTECTED]>
Re: [SQL] Need Help for select
On Wed, 14 Aug 2002 09:10:53 +0200 Andre Schubert <[EMAIL PROTECTED]> wrote: > The ride side as follows. > Table d contains information about subitems. > Table c holds information about subitems and items of type bar. > Each subitem can have 0..n items bar selected. > > What i want is that a subitem is only activated for a foo item if > the foo-item has exactly selected the same bar items selected as > as the relation between table c and d. > > Example 1: > The foo-item A_Name1 has selected the bar-items 1 and 2. > The subitem D_Name1 is only activated for a foo-item if that foo-item > has selected the bar-items 1 and 2, this happens for A_Name1. > If this mention implies that the tuple of (1, 1) in the c is supposed to take precedence over that of (2, 1), SELECT a.name, d.name FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id FROM b, c WHERE b.c_id = c.b_id GROUP BY b.c_id, c.d_id ) AS t, a, d WHERE a.id = t.a_id AND d.id = t.d_id Regards, Masaru Sugawara ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] parser: parse error at or near "$1"
On Wed, 2002-08-14 at 13:26, Sugandha Shah wrote: > Hi , > I'm getting this error : > > Error occurred while executing PL/pgSQL function ins_schedule_status > line 42 at SQL statement > parser: parse error at or near "$1" > > Unable to locate the cause. Please any hint or clue will be of great help. > > Below is the function > > > CREATE FUNCTION ins_schedule_status(int4,time without time >zone,varchar(256),int2,int2,int2) RETURNS int4 AS ' > DECLARE > schedule_id ALIAS FOR $1; ... > insert into status_log ( log_id, computer_id,schedule_id,status, schedule_id in the insert is a column name; the alias subsitutes $1 at that point. You had better choose variable names that don't conflict with anything else. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let us therefore come boldly unto the throne of grace, that we may obtain mercy, and find grace to help in time of need." Hebrews 4:16 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Few Queries
On Wed, 2002-08-14 at 13:29, Sugandha Shah wrote: > Hi , > > No luck . Even with Select Into . Please if any body has faced similar problem and >knows a solution. > > CREATE FUNCTION del_old_history() RETURNS int4 AS ' ^ > declare >var_history_age_limit int4; >set_timedatetime; > > BEGIN > select into var_history_age_limit history_age_limit from database_info; > IF (var_history_age_limit is not null) THEN > > --set_time :=select current_date()+ INTERVAL '1 day'); > select into set_time current_date()+ INTERVAL ''$var_history_age_limit days >''; > delete from history where complete_time <= set_time; > END IF; >return true; ^^^ > END;' > LANGUAGE 'plpgsql'; You don't seem to be returning what you have selected. I would expect "RETURN true;" to give you an error. Shouldn't that be "RETURN var_history_age_limit;"? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let us therefore come boldly unto the throne of grace, that we may obtain mercy, and find grace to help in time of need." Hebrews 4:16 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] parser: parse error at or near "$1"
On Wed, 14 Aug 2002 17:56:16 +0530 "Sugandha Shah" <[EMAIL PROTECTED]> wrote: > -- check for the exact same status string reported > -- previously and skip it if found > > select into last_status top 1 status, last_code status_code from >status_log where schedule_id =schedule_id order by complete_time desc ; PostgreSQL doesn't have a TOP keyword. You'll need to use a LIMIT keyword. See docs. http://www.postgresql.org/idocs/index.php?sql-select.html Regards, Masaru Sugawara ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Few Queries
On Wednesday 14 Aug 2002 1:29 pm, Sugandha Shah wrote: > Hi , > > No luck . Even with Select Into . Please if any body has faced similar > problem and knows a solution. You don't need select into here - the sample below deletes everything older than one day. You should be able to adapt it to your needs. Your example had a + rather than a - which would delete things in the future, that might have been what you intended, or it might not. The RAISE NOTICE line prints a useful debug value. I like to scatter these around while I'm testing. CREATE FUNCTION del_old_history() RETURNS int4 AS ' DECLARE var_history_age_limit int4; set_timetimestamp; BEGIN var_history_age_limit:=1; set_time := current_date() - (var_history_age_limit::text || '' days '')::interval; RAISE NOTICE ''set time = %'',set_time; delete from history where complete_time <= set_time; return var_history_age_limit; END;' LANGUAGE 'plpgsql'; -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need Help for select
On Wed, 14 Aug 2002 21:57:02 +0900 "Masaru Sugawara" <[EMAIL PROTECTED]> wrote: > On Wed, 14 Aug 2002 09:10:53 +0200 > Andre Schubert <[EMAIL PROTECTED]> wrote: > > > The ride side as follows. > > Table d contains information about subitems. > > Table c holds information about subitems and items of type bar. > > Each subitem can have 0..n items bar selected. > > > > What i want is that a subitem is only activated for a foo item if > > the foo-item has exactly selected the same bar items selected as > > as the relation between table c and d. > > > > Example 1: > > The foo-item A_Name1 has selected the bar-items 1 and 2. > > The subitem D_Name1 is only activated for a foo-item if that foo-item > > has selected the bar-items 1 and 2, this happens for A_Name1. > > > > > If this mention implies that the tuple of (1, 1) in the c is supposed > to take precedence over that of (2, 1), I want to compare if a tuple in c exist in b for each c.d_id and b.a_id. In c exists 3 tuples: (1,2), (3,4), (5) and want to find these tuples in b. select a.name,d.name from a,b,c,d where "tuple found in b" = "tuple found in c" and b.a_id = a.id and c.d_id = d.id My english is not so good, but i hope you understand what i want to do. Thanks in advance > > > SELECT a.name, d.name > FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id > FROM b, c >WHERE b.c_id = c.b_id > GROUP BY b.c_id, c.d_id > ) AS t, > a, > d > WHERE a.id = t.a_id > AND d.id = t.d_id > > > > Regards, > Masaru Sugawara > > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] concurrent connections is worse than serialization?
On Wed, 2002-08-14 at 05:18, Richard Huxton wrote: > On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote: > > I have a testing program that uses 30 concurrent connections > > (max_connections = 32 in my postgresql.conf) and each does 100 > > insertions to a simple table with index. > > > > It took me approximately 2 minutes to finish all of them. > > > > But under the same environment(after "delete From test_table, and vacuum > > analyze"), I then queue up all those 30 connections one after another > > one (serialize) and it took only 30 seconds to finish. > > > > Why is it that the performance of concurrent connections is worse than > > serializing them into one? > > What was the limiting factor during the test? Was the CPU maxed, memory, disk > I/O? No, none of the above was maxed. CPU usage that I paid attention to was at most a 48%. > > I take it the insert really *is* simple - no dependencies etc. > > > I was testing them using our own (proprietary) scripting engine and the > > extension library that supports postgresql serializes the queries by > > simply locking when a query manipulates a PGconn object and unlocking > > when it is done. (And similiarly, it creates a PGconn object on the > > stack for each concurrent queries.) > > I assume you've ruled the application end of things out. What does this mean? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Few Queries
"Sugandha Shah" <[EMAIL PROTECTED]> writes: > CREATE FUNCTION del_old_history() RETURNS bool AS ' > declare >var_history_age_limit int4; >set_timedatetime; > BEGIN > select into var_history_age_limit history_age_limit from database_info; > IF (var_history_age_limit is not null) THEN > set_time := select current_date()+ INTERVAL ' ' $var_history_age_limit day' '; You don't use a $ to refer to plpgsql variables (except for parameters, and there the *name* of the parameter is actually $n). Also ":= select" is redundant; I believe the correct syntax would be set_time := current_date() + ... or at least it would be if SQL expected current_date to come with parentheses, but it doesn't, so the next bug is that you need set_time := current_date + ... Another problem is that "INTERVAL ''var_history_age_limit day''" isn't going to work because plpgsql doesn't do textual interpolation of variables into queries. (If you want a language where that's how it's done, try pltcl or plperl.) While you could hack around with something like "CAST(text(var_history_age_limit) || ' day' as interval)", this actually is very much the hard way to do it --- adding an integer to a date already does what you want. So this statement should just be set_time := current_date + var_history_age_limit; although given the logic used later I wonder whether what you are after isn't really set_time := current_date - var_history_age_limit; BTW I'd declare set_time as timestamp or timestamptz if I were you; datetime is an obsolete datatype name that's not going to be accepted anymore as of 7.3. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [email protected]
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';
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] [email protected]
thanks very much
-Message d'origine-
De : Stephan Szabo [mailto:[EMAIL PROTECTED]]
Envoye : mercredi 14 aout 2002 16:36
A : philip johnson
Cc : [EMAIL PROTECTED]
Objet : Re: [SQL] [EMAIL PROTECTED]
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';
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] concurrent connections is worse than serialization?
On Wednesday 14 Aug 2002 3:20 pm, Wei Weng wrote: > On Wed, 2002-08-14 at 05:18, Richard Huxton wrote: > > On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote: [30 connections is much slower than 1 connection 30 times] > > What was the limiting factor during the test? Was the CPU maxed, memory, > > disk I/O? > > No, none of the above was maxed. CPU usage that I paid attention to was > at most a 48%. Something must be the limiting factor. One of - CPU - Memory - Disk I/O - Database (configuration, or design) - Application If it's not CPU, is the system going into swap or are you seeing a lot of disk activity? > > I assume you've ruled the application end of things out. > > What does this mean? I mean if you don't actually run the queries, then 30 separate processes is fine? If you can provide us with an EXPLAIN of the query and the relevant schema definitions, we can rule out database design. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Explicite typecasting of functions
Richard Huxton <[EMAIL PROTECTED]> writes: > On Wednesday 14 Aug 2002 10:30 am, Andreas Tille wrote: >> Any hint to solve this kind of chicken-egg-problem? > Your solution is not safe anyway - you could end up with two processes trying > to insert the next value. I concur with Richard that Andreas needs to solve a different problem, but just for the record, the way you could do it is CREATE TABLE without mentioning the default CREATE FUNCTION ALTER TABLE ... SET DEFAULT Note however that pg_dump is not bright enough to deduce that you did this. It will dump the table definition first, with the DEFAULT clause, and so you'll have to do manual surgery on the dump file if you ever need to reload. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] concurrent connections is worse than serialization?
On Wed, 2002-08-14 at 10:49, Richard Huxton wrote:
> On Wednesday 14 Aug 2002 3:20 pm, Wei Weng wrote:
> > On Wed, 2002-08-14 at 05:18, Richard Huxton wrote:
> > > On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote:
>
> [30 connections is much slower than 1 connection 30 times]
Yeah, but the problem is, say I have 20 users using select on the
database at the same time, and each select takes 10 seconds to finish. I
really can't queue them up (or the last user will reall have to wait for
a long time), can I?
>
> > > What was the limiting factor during the test? Was the CPU maxed, memory,
> > > disk I/O?
> >
> > No, none of the above was maxed. CPU usage that I paid attention to was
> > at most a 48%.
>
> Something must be the limiting factor. One of
> - CPU
> - Memory
> - Disk I/O
> - Database (configuration, or design)
> - Application
>
> If it's not CPU, is the system going into swap or are you seeing a lot of disk
> activity?
I did hear a lot of disk noise when I ran the test. How do I tell if the
"system is going into swap"?
Is there any system settings I can/should change to make this a little
faster?
>
> > > I assume you've ruled the application end of things out.
> >
> > What does this mean?
>
> I mean if you don't actually run the queries, then 30 separate processes is
> fine?
>
> If you can provide us with an EXPLAIN of the query and the relevant schema
> definitions, we can rule out database design.
>
This is actually really simple.
A table like
| foo |
|IDVARCHAR(40) | --> primary key
|Name VARCHAR(100)|
And I did an INSERT INTO foo ('some-unique-guid-here', 'Test Name');
So I don't think it is any matter of the database.
Thanks
--
Wei Weng
Network Software Engineer
KenCast Inc.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] sql function examples requested (! select)
I would like examples of functions using update, insert and delete, where LANGUAGE 'SQL', both how to write them and how to use them. I've got examples of functions as selects coming out of my ears, and PL/pgsql examples of more complex needs, but I just need to run a simple UPDATE. And I forsee an immediate need to do deletes the same way. Please feel free to either respond to the list, or in email, as I subscribe to both. Sue __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] concurrent connections is worse than serialization?
Wei Weng <[EMAIL PROTECTED]> writes: > On Wed, 2002-08-14 at 10:49, Richard Huxton wrote: >> If it's not CPU, is the system going into swap or are you seeing a >> lot of disk activity? > I did hear a lot of disk noise when I ran the test. How do I tell if the > "system is going into swap"? Try running "vmstat 1" while doing the test. If the "swap" columns show any activity then you're swapping. Watching free memory with "top" may also be insightful. I tend to agree with what I think Richard suspects: you haven't got enough RAM for thirty concurrent server processes, and so the system is wasting a lot of time swapping processes to disk. What are the hardware parameters, anyway? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sql function examples requested (! select)
Sue Humphrey <[EMAIL PROTECTED]> writes: > I would like examples of functions using update, insert and > delete, where LANGUAGE 'SQL', both how to write them and how to > use them. Have you read the documentation? http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/xfunc-sql.html The first couple of examples seem to cover the territory ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] concurrent connections is worse than serialization?
On Wednesday 14 Aug 2002 4:29 pm, Wei Weng wrote:
> > [30 connections is much slower than 1 connection 30 times]
>
> Yeah, but the problem is, say I have 20 users using select on the
> database at the same time, and each select takes 10 seconds to finish. I
> really can't queue them up (or the last user will reall have to wait for
> a long time), can I?
No, and you shouldn't have to - Postgresql can handle hundreds of concurrent
connections if needs be.
> > If it's not CPU, is the system going into swap or are you seeing a lot of
> > disk activity?
>
> I did hear a lot of disk noise when I ran the test. How do I tell if the
> "system is going into swap"?
You'd expect disk activity to a degree, it's a database after all. To see how
much and what is happening try one of : vmstat, iostat, top. You should have
at least one of these on whatever system you are using.
> Is there any system settings I can/should change to make this a little
> faster?
There are a number of settings in postgresql.conf - see the online docs (I
think there's a tuning document on techdocs.postgresql.org). Bear in mind
that the default settings for postgresql are very small, so you might want to
look at the sort memory/shared memory settings. Increase values a bit at a
time and see where you get a good balance. See the list archives for lots of
discussion of this.
> |IDVARCHAR(40) | --> primary key
> |Name VARCHAR(100)|
> And I did an INSERT INTO foo ('some-unique-guid-here', 'Test Name');
Try using the script multicli_ins.sh below - it creates 10 perl clients each
of which inserts 100 rows in separate transactions. This is about as
inefficient as you can get.
Have a play with different numbers of connections and see if there's a
pattern.
=> select max(ts) - min(ts) from foo;
?column?
-
00:00:06.448863
So - I can insert 1000 using 10 clients in under 6.5 seconds. That's on an
untuned system on a development server (400MHz, 256MB, IDE disk).
=== table definition ===
CREATE TABLE "foo" (
"id" character varying(40),
"name" character varying(100),
"ts" timestamp with time zone DEFAULT now()
);
CREATE UNIQUE INDEX foo_key ON foo (id);
=== Perl script: pg_ins_test.pl ===
#!/usr/bin/perl -w
my $id = shift;
open CMD,"| psql -q";
my $key = "${id}AAA";
for (my $i=0; $i<100; $i++) {
print CMD "INSERT INTO foo (id,name) VALUES ('$key','Test name');\n";
$key++;
}
close CMD;
print "Done $id\n";
=== Shellscript: multicli_ins.sh ===
#!/bin/sh
psql -c 'DELETE FROM foo'
for i in A B C D E F G H I J
do
./pg_ins_test.pl $i 2>/dev/null &
done
=== End shellscript ===
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Need Help for select
On Wed, 14 Aug 2002 16:04:21 +0200 Andre Schubert <[EMAIL PROTECTED]> wrote: > > > > > > If this mention implies that the tuple of (1, 1) in the c is supposed ^^ b > > to take precedence over that of (2, 1), > > I want to compare if a tuple in c exist in b for each c.d_id and b.a_id. > In c exists 3 tuples: (1,2), (3,4), (5) > and want to find these tuples in b. I were able to catch what conditions join b to c. I try to think again. > > select a.name,d.name from a,b,c,d where "tuple found in b" = "tuple found in c" and >b.a_id = a.id and c.d_id = d.id > > My english is not so good, but i hope you understand what i want to do. > > Thanks in advance > > > > > > > SELECT a.name, d.name > > FROM (SELECT DISTINCT min(b.a_id) AS a_id, c.d_id > > FROM b, c > >WHERE b.c_id = c.b_id > > GROUP BY b.c_id, c.d_id > > ) AS t, > > a, > > d > > WHERE a.id = t.a_id > > AND d.id = t.d_id > > > > Regards, Masaru Sugawara ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] DISTINCT peformance differences
If art_id is the primary key of the la table, is the latter faster? SELECT DISTINCT la.* FROM ... or SELECT DISTINCT ON (la.art_id) la.* FROM ... ie. Does this offer a performance improvement by only doing the distinct on the primary key or not? Thanks, Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need Help for select
On Wed, 14 Aug 2002 16:04:21 +0200 Andre Schubert <[EMAIL PROTECTED]> wrote: > I want to compare if a tuple in c exist in b for each c.d_id and b.a_id. > In c exists 3 tuples: (1,2), (3,4), (5) > and want to find these tuples in b. Probably I would think I have reached the correct query. Table b and c, however, must have unique indices like the following in order to get the result by using it, because it pays no attention to the duplicate keys. If there are no primary keys, it will become more complicated for eliminating duplicate keys. create table b ( a_id int, c_id int, constraint p_key_b primary key(a_id, c_id) ); create table c ( b_id int, d_id int, constraint p_key_c primary key(b_id, d_id) ); SELECT a.name, d.name FROM (SELECT t2.a_id, t2.d_id FROM (SELECT b.a_id, t1.d_id, t1.n FROM (SELECT c.b_id, c.d_id, t0.n FROM c, (SELECT d_id, COUNT(*) AS n FROM c GROUP BY d_id) AS t0 WHERE c.d_id = t0.d_id ) AS t1 LEFT OUTER JOIN b ON (t1.b_id = b.c_id) WHERE b.a_id IS NOT NULL ) AS t2 GROUP BY t2.a_id, t2.d_id, t2.n HAVING COUNT(*) = t2.n ) AS t3, a, d WHERE a.id = t3.a_id AND d.id = t3.d_id Regards, Masaru Sugawara ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Explicite typecasting of functions
On Wed, 14 Aug 2002 Richard Huxton wrote:
> > CREATE TABLE Items (
> > Idint DEFAULT NextItem()
>
> > CREATE FUNCTION NextItem() RETURNS INT4
> > AS 'select max(Id)+1 from Items where Id < 100;'
> > LANGUAGE 'sql';
>
> >ERROR: Relation "items" does not exist
> >
> > Any hint to solve this kind of chicken-egg-problem?
>
> Your solution is not safe anyway - you could end up with two processes trying
> to insert the next value.
While you are perfectly right in principle I can be sure that this can not
happen in this application.
> Can I suggest two sequences: item_low_seq and item_high_seq? Set the initial
> value of each to 1,000,000 and 99,000,000 (or whatever) and then use
> whichever sequence is appropriate.
>
> In the example above you'd want something like:
> id int not null default nextval('item_low_seq')
In fact I want to let PostgreSQL manage only the Ids of the 'lower region'
via sequences/functions whatever. The 'higher region' is imported from
an external source and contains explicite Ids.
But anyway for academic reasons: What means adding 'explicit typecast'
of a func???
I was not able to find this term in the docs.
Kind regards
Andreas.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Explicite typecasting of functions
On Wed, 14 Aug 2002, Tom Lane wrote: > I concur with Richard that Andreas needs to solve a different problem, > but just for the record, the way you could do it is > > CREATE TABLE without mentioning the default > > CREATE FUNCTION > > ALTER TABLE ... SET DEFAULT > > Note however that pg_dump is not bright enough to deduce that you did > this. It will dump the table definition first, with the DEFAULT clause, > and so you'll have to do manual surgery on the dump file if you ever > need to reload. Hmmm, the original problem I wanted to solve using this function is that pg_dump is not bright enough to store sequences. I use a development machine to build the database do a pg_dump and after this I try to put this dump to the production (readonly) server. This procedure every time requires manual setting of the sequences. I tried to avoid this by the function. Any better way to do this? Kind regards Andreas. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
