Re: [GENERAL] Problems with plpgsql and FOR loops
Dear Bill Moran , I know the correct solution is to use the same version to develop on that I'm using to test. So I'm going to downgrade my version to 7.3.4 for now ... but this doesn't solve my biggest problem: getting the FOR loop to work. It appears from the error that the parser is getting confused between a FOR IN SELECT loop and a FOR integer loop. If its not top secret we would like to see the code -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- I am usually called as Vishal Kashyap and my Girlfriend calls me Vishal CASH UP. Because everyone loves me as Vishal Kashyap and my Girlfriend loves me as CASH. ___ //\\\ ( 0_0 ) o0o-o0o- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] boolean to int
Dear Mage , I'm wondering why pgsql doesn't support boolean typecasts like select true::int; Many client applications including php assign 1 to true and 0 to false This was a issue PHP 4.2 + PostgreSQL 7.3.x and supports it till now for backward compatibility -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 Yahoo IM: [EMAIL PROTECTED] --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- pgsql=# select marital_status from vishals_life; marital_status -- Single not looking 1 Row(s) affected ___ //\\\ ( 0_0 ) o0o-o0o- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] select statement against pg_stats returns inconsistent
Dear Shelby Cain , Is this expected behavior or perhaps a bug? For a novice like me can anyone please tell me 1. Will this effect my application developed on PostgreSQL 2. Will my Application break at some point I heavly use the type of queries defined in the post. Would be greatfull for any kinda answers. -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- I am usually called as Vishal Kashyap and my Girlfriend calls me Vishal CASH UP. Because everyone loves me as Vishal Kashyap and my Girlfriend loves me as CASH. ___ //\\\ ( 0_0 ) o0o-o0o- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] embeding postgre
Dear twosk I heard from somone that i could use PostgreSQL, but I cannot find any information that confirms or denies it. AFAIK There was a discussion on the same topic in Dec2003 please search the list and you will geta solution -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- I am usually called as Vishal Kashyap and my Girlfriend calls me Vishal CASH UP. Because everyone loves me as Vishal Kashyap and my Girlfriend loves me as CASH. ___ //\\\ ( 0_0 ) o0o-o0o- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] backup and restore questions
Dear Sally Sally , I had a few questions concerning the backup/restore process for pg. 1) Is it possible to dump data onto an existing database that contains data (assumning the schema of both are the same). Has anyone done this? I am thinking of this in order to expediate the data load process This can work provided the database are on same system and have have same schema not tried though. 2) I read that when dumping and restoring data the insert option is safer but slower than copy? Does anyone know from experience how much slower (especially for a database containing millions of records). If you are real serious about your data best way AFAIK is insert because with insert statments you can move around in case you upgrade your database or add a new colum in new table but trying to restore a old data of the same table. On an Celeron 900 PostgreSQL 7.3.4 RH 9.0 a 151Kb tared backup takes about 5 Minutes. Though data restore depends 99 % on disk throughput 1% on CPU in case of plain insert file and 90 % on disk throughput and 10 % CPU in case of tared file. 3) can pg_restore accept a file that is not archived like a zipped file or plain text file (file.gz or file) Can use both zipped and Plain. New versions of pg_restore i.e 7.3 identify the file format automatically 4) Is the general practise to have one whole dump of a database or several separate dumps (by table etc...)? One dump for data and other dump for schema will always help. -- Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com ~*~*~*~*~*~*~* You Guys start coding I will take care of what this customers needs. ~*~*~*~*~*~*~* I Know you believe my words so logon to Jabber.org and add [EMAIL PROTECTED] to your roster. OR Seek Me at 264360076 ~*~*~*~*~*~*~* I am usually called as Vishal Kashyap but my Girlfriend calls me as Vishal CASH UP. This is because others identify me because of my generosity but my Girlfriend identify me because of my CASH. ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] insertion with trigger failed unexpectedly
Dear Anton Nikiforov, The problem: From the very beginning everything was fine and all records that i was getting from routers were calculated just right. I spent a weeks monitoring and testing my software. Now i have 10.000.000 records in raw table and when i'm inserting data alot of records are missing in raw and daily. Sometimes i got UPDATE failed errors, sometimes INSERT failed, but in general i'm getting this messages twice a day but not only two records are missing - hundreds of them. I am suggesting something but may be its wrong in eithercase we would require your PostgreSQL version number etc Also Checkout the disk space if you have run out of diskspace Currently i have no idea where to go and what to check. I did my tests mainly on FreeBSD platform and now i did tests on RedHat Linux and the result is the same - some records just did not This is a real problem its seems no disk space probelm in this case would you please show us the code reach the database (trigger has logger that is saying that everything Regards, Vishal Kashyap ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OIDS and its limitations
Hi Amir Khawaja , I have a rather trivial (I hope) question about OID types and PostgreSQL. Since PostgreSQL creates tables WITH OIDS by default, I'm wondering if it is bad practice to allow the default behaviour. For example, if I have a database with 60+ tables (all tables have their own PK that is not of type OID) and all of them have an OID field created by PostgreSQL by default, will this be problematic in the long run? For example, if I have 30 tables with records greater than 500,000, will PostgreSQL choke? I have done this in one of my project which was using OID'S (i.e tables were created with OIDS nothing more) thus data inserted into the table of a database generated new OID's and it was from this list I came to know that one of the guys table have exausted all the OIDS and to get around this problem he had to drop recreate the database and again populate the tables. From this point onwards I decided to create Tables WITHOUT OIDS and yes my aplication is running fine. This was with PostgreSQL 7.3.x and 7.4.x could not tell about the older one. Even the documentation (Read Chapter 8 of SQL language- data type ) Says So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables. Hope this helps Regards Vishal Kashyap ---(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: [GENERAL] Parser does not like %ROWTYPE in the RETURNS clause of a
Dear ezra epstein ; Using Postgres 7.4, the function test gets this: psql:temp3.sql:10: ERROR: syntax error at or near % at character 135 the function test2 gets this: psql:temp3.sql:10: ERROR: syntax error at or near ROWTYPE at character 141 Very odd. The first doesn't even like the '%' character -- perhaps because doof is a table type rather than a column (domain) type??? ROWTYPE for SQL Language you may please check that code /* CREATE TABLE doof ( pk_id serial ) WITHOUT OIDS; */ CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; /code The above code gave error on mine system also PostgreSQL 7.3.4 what I think you want to something like this code CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof AS ' SELECT * FROM doof WHERE pk_id = $1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test1(INTEGER) RETURNS doof AS ' SELECT * FROM doof WHERE pk_id = $1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; /code Mine Limited knowledge tells me that this is not a BUG but just an effect of thinking out of the box Shoot back if I was right please. Regards, Vishal Kashyap ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Unix 2038 And PostgreSQL
Dear all, Before reading all this let me assure you nothing is out of control just DONT PANIC I was just going through the pages of http://www.2038.org and was concerned by the fact my computer using Linux will rollover to Fri Dec 13 20:45:52 1901 after Tue Jan 19 03:14:07 2038 Now this was very serious for me as my clients use big dates for work though my linux system indicated it would be effected by 2038 (Run the perl script available on that site) but a quick select date'2038-01-19' + 1; gave me result 2038-01-20 Now that was what I looking for I believe that PostgreSQL guys have worked around the 2038 problem any suggestions and further finding's are most welcome. My Systems Config is , PostgreSQL 7.3.4 RH 9.0 Regards, Vishal Kashyap. Related Links ... http://news.com.com/2100-7355-5129875.html http://www.unix-systems.org/version2/whatsnew/year2000.html http://www.UNIX-systems.org/go/unix http://www.unix-systems.org/go/unix. http://developers.slashdot.org/developers/03/12/21/1952207.shtml?tid=126tid=128tid=156 ---(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: [GENERAL] Excel, OpenOffice and Postgres
Hello javier garcia, Hello Bob; Some time ago I were using Postrges along with Excel via Microsoft Query and ODBC. I just remember that there was a problem when trying to see views but it worked with single tables. Perhaps you can experiment with that. This problem can be over come by unticking the updateable cursurs in pgsqlODBC config menu Regards Vishal Kashyap Javier El Mi 17 Dic 2003 17:01, Bob Powell escribi: Hello everyone: Can anyone tell me if it is possible to connect excel or openoffice via odbc directly to a postgres backend database. Also, if that is possible does either OpenOffice or Excel support SQL queries to said database? Bob Powell ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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: [GENERAL] dump and sequence initilizing
Dear Markus Rebbert , what happens with sequences if i dump a database and i want to reload it? The pg_dump command also dumps the index value of sequences and you do not have to do any thing Just make sure your pg_restore and pg_dump with option --disable-tiggers Regards, Vishal kashyap ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Shell access send email from PLPGSQL?
Dear Philippe Lang, For e-mail from PgSQL function you need is http://pgmail.sourceforge.net/ Tell me if it help's n one of my project its a great help Regards, V Kashyap ---(end of broadcast)--- TIP 8: explain analyze is your friend