Re: [SQL] slow DELETE queries

On Thursday 20 June 2002 05:01 pm, you wrote: > On Thu, 20 Jun 2002 15:23:53 +0200, Denis <[EMAIL PROTECTED]> wrote: > >I traced the queries slowing it all down to this snippet in the debug log: > >DELETE FROM phpbb_search_wordlist WHERE word_id IN ( > >SELECT word_id

Re: [SQL] Porting from PL/SQL to PLPGSQL

pts, it prompts the user to enter its value. In Postgres, we do not have such option (so far i know). So, you can replace those with your desired values directly. Postgres supports Array type too. Although i haven't used it anyday.. you can try it out. HTH Denis - Original Message ---

Re: [SQL] how to preserve \n in select statement

Hi Richard.. If your users are required to fire only SELECT and no DML, you can do the following: BEGIN; execute the statements given by user ROLLBACK; This will not affect your SELECT and also if any malicious user gives DELETE statement, that will not have any impact too.. HTH Thanx Denis

[SQL] Initially Deffered - FK

constraint address_fk references contact(id) on delete cascade initially deferred, city text, pin text); Lets.. insert few data in it.. insert into contact values (1, 'Denis'); insert into contact values (2, 'Anand'); insert in

[SQL] Initially Deffered - FK

constraint address_fk references contact(id) on delete cascade initially deferred, city text, pin text); Lets.. insert few data in it.. insert into contact values (1, 'Denis'); insert into contact values (2, 'Anand'); insert into contact

Re: [SQL] Initially Deffered - FK

result. But, just issue commit and see, the result gets changed !! Is this behaviour rectified / changed in later release of PG (say 7.3 or 7.4) ? Any help will be appreciated. Thanx Denis - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Den

Re: [SQL] Left joins with multiple tables

Hi Colin, Try select id, name, a.field1, b.field2, c.field3 from people p left outer join a on (a.person_id = p id) left outer join b on (b.person_id = p.id) left outer join c on (c.person_id = p.id); HTH Denis - Original Message - From: "Coli

Re: [SQL] Initially Deffered - FK

Hi Stephan, Thanks for your reply. Will have to work on its workaround... Denis - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, January 19, 2004 4:03 PM Subject: Re: [SQL] Init

Re: [SQL] Database diagram

Hi Ganesan, You can try ERWin (by CA). It creates Logical data model of the database. (though.. i haven't used it..) HTH Thanx Denis - Original Message - From: "Ganesan Kanavathy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent

Re: [SQL] max timestamp

2-16 14:27:20.888205+05:30 (3 rows) HTH Thanx Denis - Original Message - From: "Michael Sterling" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, February 10, 2004 11:44 PM Subject: [SQL] max timestamp > i'm trying to get the max time st

Re: [SQL] Field list from table

To get the list.. use SELECT a.attname as "Columns" FROM pg_attribute a, pg_class c WHERE c.relname = '' AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum Thanx Denis - Original Message - From: "Jan Pips" <[EMAIL PROTECTED]>

Re: [SQL] transaction

: update tempxitag set qty = qty + nqty where ccod = cccod GET DIAGNOSTICS nFound = ROW_COUNT; If nFound = 0 then insert into tempxitag( ccod, qty) values (cccod, nqty ); End if; HTH Denis - Original Message - From: Bruno

Re: [SQL] Syntax for cmd to EXEC...how many quotes?

Try (to solve string terminating error ): sql_string := ''INSERT INTO temp_table ( view_name, row_count ) SELECT '' || r_rec.viewname || '', count(*) FROM '' || r_rec.viewname || '' ; '' ; BUT, you will be needing to

Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble

Hi, If you want to SORT descending considering multiple column, you need to spefify DESC after each column. Default is ASC. So, your present sorting is ASC, ASC and DESC You can specify 1 DESC, 2 DESC, 3 DESC HTH Denis - Original Message - From: <[EMAIL PROTECTED]> To: &

Re: [SQL] Can someone tell me why this statement is failing?

Hi.. Your END_TIME_MINUTES condition fails.. 1082377320 <= 1082375100 HTH. Denis - Original Message - From: P A <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, April 20, 2004 2:01 AM Subject: [SQL] Can someone tell me why this

Re: [SQL] ORDER BY TIMESTAMP_column ASC, NULL first

Hi Rod, Try this ace=> create table test(name text, age int ); CREATE ace=> insert into test values ('Denis',26); INSERT 1823531 1 ace=> insert into test values (null,26); INSERT 1823532 1 ace=> select * from test order by name; name | age ---+- Denis | 2

[SQL] Are circular REFERENCES possible ?

! Postgres complains like "ERROR: Relation 'customers' does not exist" when creating 'shops'. Someone told me I should create a third table, ok, but in this case I loose the total control about my logic... Do you have a suggestion ? Thanks a lot in advance

[SQL] URGENT ! Nouveau virus

x27;informations, mais en attendant FERMEZ INTERNET EXPLORER ET NE L'OUVREZ PLUS Bonne soirée ! Denis Bucher ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] URGENT ! Nouveau virus

Hello ! Ok... I wrote a small text against that virus just in case you need it ! http://www.horus.ch/virus180901.txt A bientôt ! Denis Bucher ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate

[SQL] Epoch extraction

Title: Message When i extract epoch from field (timestamp(0) without time zone) I get the time that is set in that field plus one hour... Is that a bug or am I just missing something     Regards, Denis Arh

Re: [SQL] default operator class (PostgreSQL's error?)

I've found a reason! It's some namespace problem - there are other tho name_ops operator classes exist. My becomes third. All are the default for (their) type. And somewhere there is the issue. Renaming my operator class into, say, name_t_ops resolves the problem. Thanks for the info. --

Re: [SQL] Delete duplicates

How to delete "real" duplicates? id | somthing --- 1 | aaa 1 | aaa 2 | bbb 2 | bbb (an accident with backup recovery...) Regards, Denis Arh - Original Message - From: "Franco Bruno Borghesi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]&

[SQL] Very strange 'now' behaviour in nested triggers.

In short, the idea this example is to test for is to split a comma-separated value of some text attribute (given to the INSERT operator) and then insert a row for each of the parts of that text value. I've tried to do this thru a nested triggers approach. create table xxx ( s text, t tim

Re: [SQL] Very strange 'now' behaviour in nested triggers.

On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote: > On Saturday 26 July 2003 14:39, Denis Zaitsev wrote: > > In short, the idea this example is to test for is to split a > > comma-separated value of some text attribute (given to the INSERT > > operator) and then

Re: [SQL] Very strange 'now' behaviour in nested triggers.

On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote: > Denis Zaitsev <[EMAIL PROTECTED]> writes: > > create table xxx ( > > s text, > > t timestamp > > default 'now' > > ); > > That's a dangerous way to define the

Re: [SQL] Very strange 'now' behaviour in nested triggers.

On Sun, Jul 27, 2003 at 08:47:16AM +0100, Richard Huxton wrote: > > No- 'now',now() and CURRENT_TIMESTAMP all stay fixed during a transaction. > The one that changes is timeofday() I think. See the "Functions and > Operators" section for details. Yes, indeed... Documentation describes this. And

[SQL] NEW and a subselect in a rule

So, I met such a problem: it's impossible to use NEW in a subselect used in a (non-select) rule. The error is: . Is this a way to do that newertheless (without using of a function, of course)? Thanks in advance. ---(end of broadcast)--- TIP 9: the

[SQL] Nonexistent NEW relation in some places of rules

In general, the task I'm trying to solve is to make a multiply inserts for a table on the one only insert for a view. It should be noted about the method of producing these multiply rows, that they depend on the VALUES given to that INSERT. So, the trivialized schema is: create function produc

Re: [SQL] Very strange 'now' behaviour in nested triggers.

On Sun, Jul 27, 2003 at 11:49:10AM -0400, Tom Lane wrote: > I put up a proposal in pgsql-hackers to change this behavior: > http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php > If we made that change then the "wrong" way of defining the default > would fail in an obvious fashion ---

Re: [SQL] Nonexistent NEW relation in some places of rules

On Tue, Jul 29, 2003 at 06:32:44PM -0400, Tom Lane wrote: > The problem is that the rule gets expanded into something rather like > > insert into a select ... from new, produce(new.b); > > and we can't support that. If we ever add support for SQL99's > LATERAL(), it might help improve matt

Re: [SQL] NEW and a subselect in a rule

On Thu, Jul 31, 2003 at 10:00:07AM +0200, Christoph Haller wrote: > > > > So, I met such a problem: it's impossible to use NEW in a subselect > > used in a (non-select) rule. The error is: > > exist>. Is this a way to do that newertheless (without using of a > > function, of course)? > > > Coul

Re: [SQL] not really SQL but I need info on BLOBs

p you should weigh the pros to the cons.. Regards - -- Denis -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-nr2 (Windows XP) iD8DBQFAnieIvsCA6eRGOOARAiwYAKCeaMfnq35nGoQRixKAsec/+k4kwwCdHy91 EyIqpTqWbZimUFdOjaFdpbI= =Uzfm -END PGP SIGNATURE- ---(end of broa

[SQL] Which SQL command creates ExclusiveLock?

Hi everyone!   I have a web application that uses Postgresql on backend. The application performs selects, updates, inserts, and deletes by using Hibernate. Tables contain indexed fields. When I run the following query, SELECT * FROM pg_locks, it shows that some transactions place Exclusive

Re: [SQL] mail alert

. Therefore, first you have to know the requirements... Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Question about encoding

Hello, I'm sure you already replied tons of questions like this, but I don't understand something. This is the situation : * I have COPY commands from a dump, encoded as UTF8 (special characters are encoded as 2-bytes). * My file contains SET client_encoding = 'UTF8'; * But when I do psql http://

Re: [SQL] Question about encoding

SET client_encoding = LATIN1. Anyway, finally everything is working again, I just had to add a SET NAMES LATIN1 to my application because it was ISO and not UTF8 aware. Therefore thanks a lot for your help, it solved all my problems ! Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Problem with return type of function ???

R $1; >row RECORD; > BEGIN > > FOR row IN > SELECT * FROM rma.serial_number WHERE sn=serialnumber > LOOP > RETURN NEXT row; > END LOOP; > > END; > $_$ > LANGUAGE plpgsql STRICT; Thanks a lot for any help ! Denis -- Sent via pgsql-sql mailing list (pg

Re: [SQL] Problem with return type of function ???

: the difference in name is just a mistake in my email but in fact it is the same function, I just renamed it wrong when doing the "cleaning" before posting my email... Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Problem with return type of function ??? (corrected)

;serialnumber ALIAS FOR $1; >row RECORD; > BEGIN > > FOR row IN > SELECT * FROM rma.serial_number WHERE sn=serialnumber > LOOP > RETURN NEXT row; > END LOOP; > > END; > $_$ > LANGUAGE plpgsql STRICT; Thanks a lot for any help ! Denis --

Re: [SQL] Problem with return type of function ???

sting my email... > > In that case - have you changed the definition of table > rma.serial_number since you defined the function? No, I just do the test just after the "CREATE OR REPLACE FUNCTION". I saw somwhere it could be the order of the fields ? Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Problem with return type of function ???

| > no_art_bw | character varying(11) | > sn_fc_date | date | > desc_fr | character varying(40) | > sn_cm_date | date | > no_facture | integer | > no_commande | integer | Denis -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Problem with return type of function ???

what you asked and I tested it myself before sending. And I think I've found the problem (but not the solution !) : When I dump the FUNCTION, I get this : > CREATE test(character varying) RETURNS SETOF serial_number instead of this : > CREATE test(character varying) RETURNS SETOF

Re: [SQL] Problem with return type of function ??? (corrected)

Hello Tom, Tom Lane a écrit : > Denis BUCHER writes: >> I have a strange problem, because it worked in a fonction for a table, >> and now I created the same (?) function for another table and it doesn't >> work... > >> The function is accepted but at runtime

Re: [SQL] Problem with return type of function ??? (corrected)

Hi Tom, Another question : Tom Lane a écrit : > Denis BUCHER writes: >> I have a strange problem, because it worked in a fonction for a table, >> and now I created the same (?) function for another table and it doesn't >> work... > >> The functi

Re: [SQL] Problem with return type of function ??? (corrected)

Tom Lane a écrit : > Denis BUCHER writes: >> To do this it will be a little complicated because of table >> dependencies... And it could bug again at the next DROP COLUMN... Is >> there a way to change my function (RETURN SETOF part) to specify the >> column names/

[SQL] EXISTS

: 42601 Character: 2 Thanks denis woodbury [EMAIL PROTECTED] 450-242-0249 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] EXISTS

Thanks to those who responded, I see PL/PgSQL is the way to go Regards, denis woodbury on 10/11/08 1:32 PM, [NAME] at [ADDRESS] wrote: > Denis Woodbury <[EMAIL PROTECTED]> writes: >> I would like to know if this this type of statement can be used in >> Postgresql > &g

Re: [SQL] staggered query?

Hi Try this.. SELECT Col1 , Col2 FROM yourtable WHERE to_number(to_char(col1, 'SS'),'99') / 10 ) in (10,20,30,40,50,00); HTH Denis > - Original Message - > From: Vincent Ladlad <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent:

[SQL] Record Lock details

row. >From which data dictionary, i should query. Any help would be appreciated. Thanx Denis ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere&qu