[SQL] How to generate object DDL of the database objects
Dear Friends, Any body could pls share their idea on creating object DDL for the postgres data objects from the Postgres Server 7.3.4 running on RH Linux 7.2. Regards Kumar
Re: [SQL] RFC: i18n2ascii(TEXT) stored procedure
Michael A Nachbaur writes: > a) am I missing any characters that need to be converted? In Unicode, any character can be dynamically combined with any number of accent characters, so an enumerated list will never do. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Date interval
On Friday 26 September 2003 09:18, you wrote: > I am looking for a way to convert an interval into a number of days ( > integer); > > In sybase I would use : > > days(today()-r_expire) SELECT now()::DATE - '1900-12-10'::DATE; ?column? 37545 SELECT now()-'1900-12-10'::timestamp; ?column? 37545 days 06:10:54.2021608353 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Date interval
I am looking for a way to convert an interval into a number of days ( integer); In sybase I would use : days(today()-r_expire) where r_expire is the timestamp I am comparing against. How do we do this in postgres? date_trunc(today()-r_expire) does not seem to do it select extract(epoch from some_interval)/3600/24::integer Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Date interval
I am looking for a way to convert an interval into a number of days ( integer); In sybase I would use : days(today()-r_expire) where r_expire is the timestamp I am comparing against. How do we do this in postgres? date_trunc(today()-r_expire) does not seem to do it thanks Richard
Re: [SQL] Case Insensitive comparison
Roberto, > > Another problem is in creating function... > > How can i create a function that accept and return any type. the type > > "any" is not allowing as parameter or return type. Is it possible? i want > > to create a function similar to NULLIF(). > > Use the standard coalesce(). NULLIF is the converse of COALESCE(). Any idea when you're going to overhaul the CookBook? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] tsearch2 question
Wei Weng <[EMAIL PROTECTED]> writes: > In my tsearch2.sql there are statements like : > --dict interface > CREATE FUNCTION lexize(oid, text) > returns _text > as '$libdir/tsearch2' > language 'C' > with (isstrict); > I don't think $libdir is the real value that we want. Yes it is. 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] RFC: i18n2ascii(TEXT) stored procedure
On Thursday 25 September 2003 05:06 pm, Manuel Sugawara wrote: > Michael A Nachbaur <[EMAIL PROTECTED]> writes: > > b) is there a better and/or faster way of implementing this? I > > don't want searches to bog down (at least too badly) as a result of > > this. > > Use to_ascii(text), [snip] D'oh! I guess thats what I get for not RTFM. :-) -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "Oh no, not again." ---(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] RFC: i18n2ascii(TEXT) stored procedure
Michael A Nachbaur <[EMAIL PROTECTED]> writes:
> b) is there a better and/or faster way of implementing this? I
> don't want searches to bog down (at least too badly) as a result of
> this.
Use to_ascii(text),
masm=# select to_ascii('áéíóú');
to_ascii
--
aeiou
(1 row)
Regards,
Manuel.
---(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
[SQL] RFC: i18n2ascii(TEXT) stored procedure
I've created the following stored procedure to allow me to do international-insensitive text searches, e.g. a search for "Resume" would match the text "Résumé". I wanted to know: a) am I missing any characters that need to be converted? My first (and only language) is English, so I'm in the dark when that is concerned; b) is there a better and/or faster way of implementing this? I don't want searches to bog down (at least too badly) as a result of this. CREATE OR REPLACE FUNCTION i18n2ascii (TEXT) RETURNS TEXT AS ' my ($source) = @_; $source =~ tr/áàâäéèêëíìîïóòôöúùûüÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜ//; return $source; ' LANGUAGE 'plperl'; -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "Ah, " said Arthur, "this is obviously some strange usage of the word safe that I wasn't previously aware of. " ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] tsearch2 question
In my tsearch2.sql there are statements like : --dict interface CREATE FUNCTION lexize(oid, text) returns _text as '$libdir/tsearch2' language 'C' with (isstrict); I don't think $libdir is the real value that we want. Do I need to set some special parameters with ./configure (in the root level of the source tree) to replace $libdir with the real library path? Thanks Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] weird(to me) request
--On Thursday, September 25, 2003 20:09:35 +0100 Richard Huxton <[EMAIL PROTECTED]> wrote: On Thursday 25 September 2003 19:51, Larry Rosenman wrote: I have the following rows (in addition to others): acct_num text, master_acct_num text, These are in the same table. What I want to enforce is that if the master_acct_num field is NOT NULL (it can be NULL, and that's fine), that the value appears in some row as acct_num. acct_num has a unique index on it, so that's fine. I'm not sure how to do this ALTER TABLE my_table ADD CONSTRAINT my_self_fk FOREIGN KEY (master_acct_num) REFERENCES my_table (acct_num); It's really just a foreign-key to yourself I wasn't sure I could do that. Thanks! LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [SQL] weird(to me) request
On Thursday 25 September 2003 19:51, Larry Rosenman wrote: > I have the following rows (in addition to others): > > acct_num text, > master_acct_num text, > > > These are in the same table. > > What I want to enforce is that if the master_acct_num field is NOT NULL (it > can be NULL, and > that's fine), that the value appears in some row as acct_num. acct_num has > a unique index on it, so that's fine. > > I'm not sure how to do this ALTER TABLE my_table ADD CONSTRAINT my_self_fk FOREIGN KEY (master_acct_num) REFERENCES my_table (acct_num); It's really just a foreign-key to yourself -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] weird(to me) request
--On Thursday, September 25, 2003 20:59:25 +0200 Adam Wieckowski <[EMAIL PROTECTED]> wrote: U¿ytkownik Larry Rosenman napisa³: I have the following rows (in addition to others): acct_num text, master_acct_num text, These are in the same table. What I want to enforce is that if the master_acct_num field is NOT NULL (it can be NULL, and that's fine), that the value appears in some row as acct_num. acct_num has a unique index on it, so that's fine. I'm not sure how to do this Thanks! LER select case when master_acct_num is null then acct_num else master_acct_num end from table; did you mained somethink like this ? I'm actually looking to enforce the fact that if master_acct_num is entered, it points to a valid acct_num. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [SQL] weird(to me) request
Użytkownik Larry Rosenman napisał: I have the following rows (in addition to others): acct_num text, master_acct_num text, These are in the same table. What I want to enforce is that if the master_acct_num field is NOT NULL (it can be NULL, and that's fine), that the value appears in some row as acct_num. acct_num has a unique index on it, so that's fine. I'm not sure how to do this Thanks! LER select case when master_acct_num is null then acct_num else master_acct_num end from table; did you mained somethink like this ? smime.p7s Description: S/MIME Cryptographic Signature
[SQL] weird(to me) request
I have the following rows (in addition to others): acct_num text, master_acct_num text, These are in the same table. What I want to enforce is that if the master_acct_num field is NOT NULL (it can be NULL, and that's fine), that the value appears in some row as acct_num. acct_num has a unique index on it, so that's fine. I'm not sure how to do this Thanks! LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [SQL] Large Objects and Bytea
In this week's General Bits, we talk about using large objects. Perhaps this might help you understand what is involved with using them. http://www.varlena.com/GeneralBits/ cheers, [EMAIL PROTECTED] On Thu, Sep 25, 2003 at 09:41:28AM +0530, Kumar wrote: > Hi Friends, > > I am running Postgres 7.3.4 on RH Linux 7.2. > > I am migrating MS SQL DB to Postgres DB. I have tables with columns of data > type 'Image' in the MS SQL database. > IF I choose 'bytea' datatype, I am afraid it may lead to poor performance of > the database (which I read from the manual). In this case what is the best data > type to use. > > Please suggest me. The explanation of using LOB objects in a table and to write > them with image is not very clear in the documentation. Can any one send me any > link or white paper or examples about it. > > Thanks in advance. > > Kumar > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] [pg-sql] Character encoding
How is it possible to convert a database in SQL_ASCII to UNICODE ? Thx a lot Regards Ben ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Case Insensitive comparison
Thanks to all of you for your valuable suggesstions does postgresql internally uses the = operator(text,text) for any other purposes. i think that overloading it solves the index problem too... Tom Lane <[EMAIL PROTECTED]> wrote: Josh Berkus <[EMAIL PROTECTED]>writes:>> How can i create a function that accept and return any type. the type "any">> is not allowing as parameter or return type. Is it possible? i want to>> create a function similar to NULLIF().> You can't, nor will you be able to -- in te future, some 7.4 functions will be > able to *accept* any type, but they will still return a specific type.Au contraire. The 7.4 polymorphic-function features may well solvejinujose's problem, if he can indeed write a type-independent functionbody. For examplecreate function nullif(anyelement, anyelement) returns anyelement as'select case when $1 = $2 then null else $1 end' language sql;I'm not sure we're all the way there yet, cfhttp://archives.postgresql.org/pgsql-general/2003-09/msg00500.phpregards, tom lane---(end of broadcast)---TIP 4: Don't 'kill -9' the postmaster Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [SQL] Case Insensitive comparison
On Wed, Sep 24, 2003 at 23:30:08 -0600, Roberto Mello <[EMAIL PROTECTED]> wrote: > On Wed, Sep 24, 2003 at 08:35:50PM -0700, George A.J wrote: > > > Another problem is in creating function... > > How can i create a function that accept and return any type. the type "any" is not > > allowing as parameter or return type. Is it possible? i want to create a function > > similar to NULLIF(). > > Use the standard coalesce(). coalesce is used to do the inverse of nullif. Postgres also has a nullif function. I suspect that he relly doesn't want to use either of those, but instead wants to have some function that returns the same type as its argument without having to write one function for each different type. There is something along those lines coming in 7.4. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] few questions ..?
Thanks richards ..i will go through that doc.. With Regards Vijay From: Richard Huxton <[EMAIL PROTECTED]> To: "vijaykumar M" <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Subject: Re: [SQL] few questions ..? Date: Thu, 25 Sep 2003 11:07:00 +0100 On Thursday 25 September 2003 09:36, vijaykumar M wrote: > Hi All, > I have few questions, kindly suggest your comments on this.. > > 1. Is that postgres supports distributed database ? > In oracle, by creating Dblink we can communicate the remorte > database from the local one. How you could implement this in postgres > database. See contrib/dblink. I think someone is looking at linking to Oracle too (but I might be wrong about that). > 2. Is there any way to cache the table on memorry ? >In oracle, we can use the cache statement at the table creation > itself. PG uses the operating-system rather than replacing it. If you're using the table regularly it should end up in cache. If you're not using it regularly, you probably didn't want it in cache anyway. > 3. List out the main tuning parameters in postgres.conf to take care > of supporing millions of records. See http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php There are two documents there describing the various config settings and the basics of performance tuning. Millions of records don't need too much tuning even on a standard PC, but the standard config settings are *very* conservative. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ Answer simple questions. Win a free honeymoon. http://server1.msn.co.in/sp03/shaadi/index.asp Sail into the sunset! ---(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] plpgsql triggers question -> foo := NEW ?
> > Thanks a lot for Your reply by I wanted to create some *foo* variable ( > in declare part of pl/pgsql trigger function) and to set : > foo := NEW > > That's why I've posted the link from interactive docs - there is the > same question > > http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html > I see your point. I have found two possible workarounds, based on the PL/pgSQL Trigger Procedure Example from the link above. But still, you have to assign the individual fields one by one. First one would be restricted to the table in question. Second one would not be restricted to the table, but the columns, not necessarily the data type, but the name at least. Furthermore, to assign the foo RECORD substructure, a dynamic query has to be executed every time, which will slow down execution. Maybe others can see a better way now. Regards, Christoph (1) CREATE OR REPLACE FUNCTION emp_stamp () RETURNS TRIGGER AS ' DECLARE foo emp%ROWTYPE ; BEGIN foo.empname := NEW.empname; -- Check that empname and salary are given IF NEW.empname ISNULL THEN RAISE EXCEPTION ''empname cannot be NULL value''; END IF; IF NEW.salary ISNULL THEN RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END IF; -- Who works for us when she must pay for? IF NEW.salary < 0 THEN RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; END IF; -- Remember who changed the payroll when NEW.last_date := ''now''; NEW.last_user := current_user; foo.last_date := NEW.last_date; foo.last_user := NEW.last_user; -- RETURN NEW; RETURN foo; END; ' LANGUAGE 'plpgsql'; (2) CREATE OR REPLACE FUNCTION emp_stamp () RETURNS TRIGGER AS ' DECLARE foo RECORD ; BEGIN FOR foo IN EXECUTE ''SELECT * FROM '' || quote_ident(TG_RELNAME) || '' LIMIT 1'' LOOP END LOOP; foo.empname := NEW.empname; -- Check that empname and salary are given IF NEW.empname ISNULL THEN RAISE EXCEPTION ''empname cannot be NULL value''; END IF; IF NEW.salary ISNULL THEN RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END IF; -- Who works for us when she must pay for? IF NEW.salary < 0 THEN RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname; END IF; -- Remember who changed the payroll when NEW.last_date := ''now''; NEW.last_user := current_user; foo.last_date := NEW.last_date; foo.last_user := NEW.last_user; -- RETURN NEW; RETURN foo; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] few questions ..?
On Thursday 25 September 2003 09:36, vijaykumar M wrote: > Hi All, > I have few questions, kindly suggest your comments on this.. > > 1. Is that postgres supports distributed database ? > In oracle, by creating Dblink we can communicate the remorte > database from the local one. How you could implement this in postgres > database. See contrib/dblink. I think someone is looking at linking to Oracle too (but I might be wrong about that). > 2. Is there any way to cache the table on memorry ? >In oracle, we can use the cache statement at the table creation > itself. PG uses the operating-system rather than replacing it. If you're using the table regularly it should end up in cache. If you're not using it regularly, you probably didn't want it in cache anyway. > 3. List out the main tuning parameters in postgres.conf to take care > of supporing millions of records. See http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php There are two documents there describing the various config settings and the basics of performance tuning. Millions of records don't need too much tuning even on a standard PC, but the standard config settings are *very* conservative. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] few questions ..?
Hi All, I have few questions, kindly suggest your comments on this.. 1. Is that postgres supports distributed database ? In oracle, by creating Dblink we can communicate the remorte database from the local one. How you could implement this in postgres database. 2. Is there any way to cache the table on memorry ? In oracle, we can use the cache statement at the table creation itself. 3. List out the main tuning parameters in postgres.conf to take care of supporing millions of records. Thanks & Regards Vijay _ Attention NRIs! Banking worries? http://server1.msn.co.in/msnspecials/nriservices/index.asp Get smart tips. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Sequenties in pgSQL 7.3.x
HI
Thanks for help I'm found this solution and this works, but I'm use
something like that
PERFORM setval(seq_name, value);
It looks better but thanks a lot
- Original Message -
From: "Richard Huxton" <[EMAIL PROTECTED]>
To: "Przemysław Słupkowski" <[EMAIL PROTECTED]>; "Lista
dyskusyjna pgsql-sql" <[EMAIL PROTECTED]>
Sent: Thursday, September 25, 2003 9:55 AM
Subject: Re: [SQL] Sequenties in pgSQL 7.3.x
On Thursday 25 September 2003 07:36, Przemysław Słupkowski wrote:
> Hi
> I am using postgresql 7.3.x. I am upgrating a database PostgreSQL.
> The main problem is how to change field last value in sequencde which
> exists in database. I created script to read this field and then I'm drop
> the table, do modification and i'm must change this value to another
value.
> But DBMS returns me message like that
> ERROR: You can't change sequence relation seq_id_seq
> How to do this
Are you using and UPDATE to set last_value? If so, you should use the
setval()
function instead.
SELECT setval('my_seq', 1234);
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL]
Hi all, I have few questions to clear my self.. 1. Is that postgres supports distributed database ? _ Talk to Karthikeyan. Watch his stunning feats. http://server1.msn.co.in/sp03/tataracing/index.asp Download images. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sequenties in pgSQL 7.3.x
On Thursday 25 September 2003 07:36, Przemysław Słupkowski wrote:
> Hi
> I am using postgresql 7.3.x. I am upgrating a database PostgreSQL.
> The main problem is how to change field last value in sequencde which
> exists in database. I created script to read this field and then I'm drop
> the table, do modification and i'm must change this value to another value.
> But DBMS returns me message like that
> ERROR: You can't change sequence relation seq_id_seq
> How to do this
Are you using and UPDATE to set last_value? If so, you should use the setval()
function instead.
SELECT setval('my_seq', 1234);
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Sequenties in pgSQL 7.3.x
On Thu, 2003-09-25 at 07:36, Przemysław Słupkowski wrote: > Hi > I am using postgresql 7.3.x. I am upgrating a database PostgreSQL. > The main problem is how to change field last value in sequencde which > exists in database. > I created script to read this field and then I'm drop the table, do > modification and i'm must change this value to another value. > But DBMS returns me message like that > ERROR: You can't change sequence relation seq_id_seq > How to do this Use the setval() function? -- 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 "Who shall separate us from the love of Christ? shall tribulation, or distress, or persecution, or famine, or nakedness, or peril, or sword?...But in all these things we overwhelmingly conquer through Him who loved us." Romans 8:35,37 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Sequenties in pgSQL 7.3.x
Hi
I am using postgresql 7.3.x. I am upgrating a database PostgreSQL.
The main problem is how to change field last value in sequencde which
exists in database.
I created script to read this field and then I'm drop the table, do
modification and i'm must change this value to another value.
But DBMS returns me message like that
ERROR: You can't change sequence relation seq_id_seq
How to do this
Documentation says:
6.11. Sequence-Manipulation Functions
select setval('some_sequence',333);
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
