Re: [GENERAL] Returning a RECORD, not SETOF RECORD

2005-04-28 Thread Michael Fuhr
On Thu, Apr 28, 2005 at 09:47:45PM +0200, Thomas Hallgren wrote: > > > >What version of PostgreSQL are you using > > The latest and greatest from CVS. Which branch? HEAD? REL8_0_STABLE? > Wouldn't it make sense to be able to define a record in the projection > part of a query, similar to what

Re: [GENERAL]

2005-04-28 Thread Wenzhe Zhou (wzhou)
unsubscribe me

Re: [GENERAL] Postgresql and VBA vs Python

2005-04-28 Thread John DeSoi
On Apr 28, 2005, at 5:04 PM, [EMAIL PROTECTED] wrote: I would just like to find a free, or low cost basic like language that would let easily paint GUI forms in some IDE, and then easily add my own script to interact with the Postgresql database. Such an a development environment, and such tutor

Re: [GENERAL] oid or schema name of current plpgsql function

2005-04-28 Thread Michael Fuhr
On Thu, Apr 28, 2005 at 10:37:47PM +0200, Matko Andjelinic wrote: > > Is there a way to know the OID of the current plpgsql function from > inside the function? I'm not aware of a way to get the current function's OID in PL/pgSQL, but you can do it in C. > What I really need is to extract the nam

[GENERAL]

2005-04-28 Thread Ignatius Gabriell Rama S.
unsubscribe me

Re: [GENERAL] Postgresql and VBA vs Python

2005-04-28 Thread Tony Caduto
Like I said before get the personal/standard version of Delphi 6,7 or 2005, it's 99.99 and you can connect to postgres with it using third party component sets like Zeos. (2005 may not be available yet) The licence varies on the version, I think Delphi 6 personal allows for commercial developem

Re: [GENERAL] info on strange error messages on postgresql

2005-04-28 Thread Tom Lane
Joe Maldonado <[EMAIL PROTECTED]> writes: > I have these messages on my 7.4.7 database log... > TopMemoryContext: 87494704 total in 10676 blocks; 179400 free (61 > chunks); 87315304 used > TopTransactionContext: 57344 total in 3 blocks; 648 free (5 chunks); > 56696 used > DeferredTriggerXact: 0 tot

[GENERAL] Increasing statistics results in worse estimates

2005-04-28 Thread Shelby Cain
I'm having a problem with analyze in 8.0.2 that is really bothering me. I have a table that contains address, city and state with about 7.8m rows in it. On that table I have two non-unique indexes for city and zipcode respectively. This table was loaded in a manner such that it is essentially so

[GENERAL] Postgresql and VBA vs Python

2005-04-28 Thread Typing80wpm
Tony, thanks. Your posts are very helpful to me. I never said anything about designing some application that depends upon bringing over a million rows.  I just want to play around with Postgresql on windows, at home, and see what happens if I load in a million rows.  Supposedly, from my read

[GENERAL] oid or schema name of current plpgsql function

2005-04-28 Thread Matko Andjelinic
Is there a way to know the OID of the current plpgsql function from inside the function? What I really need is to extract the name of the schema where the function is stored. -- matko ---(end of broadcast)--- TIP 5: Have you checked our extensive

Re: [GENERAL] Clustering

2005-04-28 Thread Karsten Hilbert
> Slony-I isn't clustering. They would have to look at pgCluster or > something like that. Sure but it seemed to fit their description of what they wanted to do. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end o

[GENERAL] GUITools update

2005-04-28 Thread Brennan Stewart
I am starting work on a comparison of all the Postgresql design & administration tools. The problem: Many Postgresql-compatible administration/design tools exist, but we lack a full comparison and review of each. This impedes migration and slows down the development process (time spent researc

Re: [GENERAL] Clustering

2005-04-28 Thread Scott Marlowe
On Thu, 2005-04-28 at 14:54, Joshua D. Drake wrote: > Karsten Hilbert wrote: > > On Thu, Apr 28, 2005 at 11:59:59AM -0700, Patrick Haugen wrote: > > > > > >>Pseudo code: > >>When databse xyz table companyname is updated update the same table > >>and rows in database abc; > > > > ... > > > >>Wha

[GENERAL] info on strange error messages on postgresql

2005-04-28 Thread Joe Maldonado
Hello, I have these messages on my 7.4.7 database log... TopMemoryContext: 87494704 total in 10676 blocks; 179400 free (61 chunks); 87315304 used TopTransactionContext: 57344 total in 3 blocks; 648 free (5 chunks); 56696 used DeferredTriggerXact: 0 total in 0 blocks; 0 free (0 chunks); 0 used SPI

Re: [GENERAL] temp tables ORACLE/PGSQL

2005-04-28 Thread Tony Caduto
We only do the connection "refesh" in the Lightning Admin Query editorfor testing our SQL that uses temp tables. refreshing the connection eliminates the OID does not exist problems. We put everything into stored procs and use them from Delphi applications and still never use execute in our procs

Re: [GENERAL] Clustering

2005-04-28 Thread Joshua D. Drake
Karsten Hilbert wrote: On Thu, Apr 28, 2005 at 11:59:59AM -0700, Patrick Haugen wrote: Pseudo code: When databse xyz table companyname is updated update the same table and rows in database abc; ... What is a good clustering technique for PostgreSQL? Slony-I. Slony-I isn't clustering. They would h

Re: [GENERAL] Returning a RECORD, not SETOF RECORD

2005-04-28 Thread Thomas Hallgren
Michael, Thanks for your reply on this. CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...' CREATE TABLE abc(a int, b int); Now I want to call my xyz function once for each row in abc and I want my RECORD to be (x int, y int, z timestamptz). How do I write that query? I.e. where do specify my

Re: [GENERAL] Clustering

2005-04-28 Thread Karsten Hilbert
On Thu, Apr 28, 2005 at 11:59:59AM -0700, Patrick Haugen wrote: > Pseudo code: > When databse xyz table companyname is updated update the same table > and rows in database abc; ... > What is a good clustering technique for PostgreSQL? Slony-I. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E1

Re: [GENERAL] on insert rule & primary key

2005-04-28 Thread Scott Frankel
Problem solved. Hacking away 'til the wee hours yielded a solution using an ON UPDATE rule, adding a row to a new table. Successful test sample follows, for anyone interested. Scott CREATE TABLE colors ( clrs_pkey SERIALPRIMARY KEY, first_nametext UNIQUE DEFAULT NULL, fav_col

Re: [GENERAL] Clustering

2005-04-28 Thread Scott Marlowe
On Thu, 2005-04-28 at 13:59, Patrick Haugen wrote: > I haven't found any information on clustering with PostgreSQL. > > One idea we've been tossing around is through PostgreSQL you can > create a function that does something when something else happens. > > Pseudo code: > When databse xyz table c

Re: [GENERAL] temp tables ORACLE/PGSQL

2005-04-28 Thread Dennis Sacks
Tony Caduto wrote: This is not entirely correct. We use temp tables all the time in PLpgsql functions and never have to use execute. We have found that you have to use EXECUTE only in certain circumstances. we use this in all our functions that use temp tables, and we use PG Lightning Admin,

[GENERAL] Clustering

2005-04-28 Thread Patrick Haugen
I haven't found any information on clustering with PostgreSQL. One idea we've been tossing around is through PostgreSQL you can create a function that does something when something else happens. Pseudo code: When databse xyz table companyname is updated update the same table and rows in database

Re: [GENERAL] Problem with GIST-index and timestamps

2005-04-28 Thread Oleg Bartunov
On Thu, 28 Apr 2005, [ISO-8859-1] Sebastian B?ck wrote: Hello, i wanted to define and GIST-index on a table with a timestamp-column containing 'infinity' values, and it throws the following error: ERROR: cannot subtract infinite timestamps Is this a known limitation? I don't know. How can i avoid

Re: [GENERAL] Returning a RECORD, not SETOF RECORD

2005-04-28 Thread Michael Fuhr
On Fri, Apr 22, 2005 at 12:24:26AM +0200, Thomas Hallgren wrote: > > CREATE FUNCTION xyz(int, int) RETURNS RECORD AS '...' > CREATE TABLE abc(a int, b int); > > Now I want to call my xyz function once for each row in abc and I want > my RECORD to be (x int, y int, z timestamptz). How do I write

Re: [GENERAL] temp tables ORACLE/PGSQL

2005-04-28 Thread Steve Atkins
On Thu, Apr 28, 2005 at 01:14:58PM -0500, Tony Caduto wrote: > This is not entirely correct. We use temp tables all the time in PLpgsql > functions and never have to use > execute. We have found that you have to use EXECUTE only in certain > circumstances. > > we use this in all our functions

Re: [GENERAL] temp tables ORACLE/PGSQL

2005-04-28 Thread Tony Caduto
This is not entirely correct. We use temp tables all the time in PLpgsql functions and never have to use execute. We have found that you have to use EXECUTE only in certain circumstances. stored procedures that use temporary tables are more painful to write - you need to use EXECUTE for any S

[GENERAL] OT: phpPgAdmin

2005-04-28 Thread Simon Windsor
Hi I apologise for this off-topic question. I am setting up phpPgAdmin on a reporting server to access other, remote postgres servers. I want to use a restricted, password user account and use pg_hba.conf to restrict access to each database. For the user account, what level of permissions will I

Re: [GENERAL] temp tables ORACLE/PGSQL

2005-04-28 Thread Dennis Sacks
NO-fisher-SPAM_PLEASE wrote: Hi I used to work with Oracle and now tryin' PostgreSQL I'm a bit confused. I found that creating temp table in one session does not make it available for other sessions for the same user? Is this intended?? PostgreSQL does not support global temporary tables. This

Re: [GENERAL] temp tables ORACLE/PGSQL

2005-04-28 Thread Pavel Stehule
> Hi > I used to work with Oracle and now tryin' PostgreSQL I'm a bit > confused. > I found that creating temp table in one session does not make it > available for other sessions for the same user? Is this intended?? Yes, it's natural behave of temp. tables in PostgreSQL. The life cycle of temp

Re: [GENERAL] ERROR: Could not convert UTF-8 to ISO8859-1

2005-04-28 Thread John D. Burger
Why does postgresql issue the error message "ERROR: Could not convert UTF-8 to ISO8859-1", when I execute a select statement after I changed the client encoding to latin1 ??? Because every UTF8 character cannot necessarily be encoded as Latin1. Unicode has more than 64, characters, Latin1 ha

[GENERAL] Linux Journal Users' Choice Award Nominations

2005-04-28 Thread Steve Crawford
Linux Journal has a new process for their Readers' Choice awards. Before there is a vote, they are soliciting nominations for each category. Say (for example) you wanted to see PostgreSQL included among the databases up for vote you would send an email to: [EMAIL PROTECTED] and include the fo

[GENERAL] temp tables ORACLE/PGSQL

2005-04-28 Thread NO-fisher-SPAM_PLEASE
Hi I used to work with Oracle and now tryin' PostgreSQL I'm a bit confused. I found that creating temp table in one session does not make it available for other sessions for the same user? Is this intended?? I was tryin to use because of lack of session and package variables in PGSQL (thats what

[GENERAL] ERROR: Could not convert UTF-8 to ISO8859-1

2005-04-28 Thread Fritz Bayer
Hello, my database encodes data as utf8. The data I want to select contains characters like äöü. If I select some columns and change the encoding to latin1, then I get the following error message: select created, keywords from requests; ERROR: Could not convert UTF-8 to ISO8859-1 If I set the

Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is

2005-04-28 Thread Ron Mayer
Marco Colombo wrote: That is, given a variable A, you can always write a boolean expression that evaluates True or False to test if A is _equal_ to None/undef/NULL (in C): And of course you can do this in SQL as well. ('a', NULL) is neither the same _nor different_ from ('a', 2). Uh, I'm not sure w

[GENERAL] Is there a bug in the win32 pg_restore (version 8.02) ?

2005-04-28 Thread Tony Caduto
Hi, I just tried to restore a db with the win32 pg_restore that shipped with 8.02 and I get to the part where it prompts me for the password, and I enter it, then I get the following error: C:\>pg_restore.exe --ignore-version --verbose --dbname=mandi_test -W -U postgres --port=5432 --host=192.168

Re: [GENERAL] Help

2005-04-28 Thread Scott Marlowe
On Thu, 2005-04-28 at 09:51, ElayaRaja S wrote: > After login as su postgres > I am unable to create the db; > > > $ createdb -E utf8 -U system myDB; > ERROR: permission denied to create database > createdb: database creation failed: ERROR: permission denied to create > database What's with

Re: [GENERAL] Thanks for insights into internal design

2005-04-28 Thread Tony Caduto
That's fine for a system like access or DBASE, but you should never be making queries that large for a production application. Access or DBASE or any other local FILE based system will not have any problems bringing back 1 million records because it does not have to bring the records across the w

[GENERAL] Clarification

2005-04-28 Thread ElayaRaja S
Hi, I am using Redhat linux 9 with postgreSQL 7.4.5. It contain 3 database such as postgres, template0 template1. If i want to create mydb where i have to create. Either i have to login as us postgres , and create mydb or under psql template1 and create mydb or under psql template0 and c

[GENERAL] Help

2005-04-28 Thread ElayaRaja S
After login as su postgres I am unable to create the db; $ createdb -E utf8 -U system myDB; ERROR: permission denied to create database createdb: database creation failed: ERROR: permission denied to create database -- Warm Regards, S.ElayaRaja Mobile: (+91) 98450 59540 E-Mail: [EMAIL PR

Re: [GENERAL] Visual Query Builder

2005-04-28 Thread Ariel Mónaco
We use EMS PostgreSQL Manager 2. Greetings, Ariel. On 4/27/05, Hrishikesh Deshmukh <[EMAIL PROTECTED]> wrote: > Hi All, > > I have DB in Postgresql; is there a visual query builder? > > Thanks in advance. > Hrishi > > ---(end of broadcast)--- > TI

Re: [GENERAL] Help

2005-04-28 Thread Woody Woodring
You can use the "adduser" command to create the postgres user on your linux box. I use Uid:26 Gid:100, but I don't know that it matters. Woody -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ElayaRaja S Sent: Thursday, April 28, 2005 9:19 AM To: pgsql-ge

Re: [GENERAL] Help

2005-04-28 Thread Tom Lane
ElayaRaja S <[EMAIL PROTECTED]> writes: > Note: At the time of installing itself i get problem while setting permission > as >root&chown postgres.postgres /usr/local/pgsql/ >chown: `postgres.postgres': invalid user Some versions of chown only support the notation user:group no

Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is NOT

2005-04-28 Thread Stephane Bortzmeyer
On Thu, Apr 28, 2005 at 02:04:29PM +0200, Marco Colombo <[EMAIL PROTECTED]> wrote a message of 146 lines which said: > No. NULL is NOT 'None', nor 'undef', and definitely not NULL as in > C. Thanks for the very good and detailed explanation of NULL in SQL. Curious people may note that the stra

[GENERAL] Help

2005-04-28 Thread ElayaRaja S
Hi, I am using Redhat linux 9. At the time of instllation i omitted the option of SQL server. AFter installing Linux 9 i started installing postgresql 7.4.5. After installing i am unable to enter into the path root$ su postgres su: user postgres does not exist Note: At the time of installing

Re: [GENERAL] Reduce size of $PGDATA for "demo cdrom"?

2005-04-28 Thread Scott Marlowe
On Wed, 2005-04-27 at 20:44, Bruno Wolff III wrote: > On Wed, Apr 27, 2005 at 10:23:19 -0500, > Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > > My first recommendation would be to put everything into one database. > > it looks like you've got 6 databases. If you've still got the template0 > >

Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each column is

2005-04-28 Thread Marco Colombo
On Wed, 2005-04-27 at 17:00 +0200, Stephane Bortzmeyer wrote: > On Wed, Apr 27, 2005 at 09:36:57AM -0500, > Scott Marlowe <[EMAIL PROTECTED]> wrote > a message of 18 lines which said: > > > Often the best bet here, btw, is to declare it not null then use > > something other than null to represe

[GENERAL] Thanks for insights into internal design

2005-04-28 Thread Typing80wpm
You give me valuable insight into the inner workings of such software.  I am a firm believer in testing everything with very large files.  One might spend months developing something, and have it in production for a year, and not realize what will happen when their files (tables) grow to sev

[GENERAL] Problem with GIST-index and timestamps

2005-04-28 Thread Sebastian Böck
Hello, i wanted to define and GIST-index on a table with a timestamp-column containing 'infinity' values, and it throws the following error: ERROR: cannot subtract infinite timestamps Is this a known limitation? How can i avoid or circumvent this? Thanks in advance Sebastian --