[SQL] Change column data type
Dear Friends, Using Postgres 7.3.4 over the linux server 7.3. Is it possible to alter/change the data type of a existing table's column, with out dropping and recreating a column of same name. Thanks for ur knowledge sharing. Regards Kumar
[SQL] Backup error - Please help
Hi, I have 2 versions of Postgres server 7.2 & 7.3.4 installed on Linux server 7.3. I have not started the Postgres Server 7.2 which is located at /var/library/pgsql. I am running and using only 7.3.4 located at /usr/local/pgsql. After I login, I come to the /usr/local/pgsql/bin and then issued the following [EMAIL PROTECTED] bin]$ pg_dump -h 192.168.2.51 -f /home/usr1/prod.sql prodpg_dump: server version: pg_dump.mo; pg_dump version: 7.2.1pg_dump: aborting because of version mismatch (Use the -i option to proceed anyway.)[EMAIL PROTECTED] bin]$ pg_dump -h 192.168.2.51 -i -f /home/usr1/prod.sql prod pg_dump: server version: pg_dump.mo; pg_dump version: 7.2.1pg_dump: proceeding despite version mismatchpg_dump: query to obtain list of data types failed: ERROR: Attribute "typprtlen" not found[EMAIL PROTECTED] bin]$ Where i am doing mistake. Pls shed some light. Thank you very much. Kumar
[SQL] Error message during compressed backup
Dear Friends, While doing compressed backup for one of the database running at Postgres Server 7.3.4 on RH Linux 7.2, I got the following error., but it backup other items --Command to backup $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f /home/db_repository/testdb20031023.sql.tar.gz -u -F c --Error msg WARNING: owner of function "plpgsql_call_handler" appears to be invalid Could anyone tell me why I am getting this. I could able to do normal back and restore. Please shed some light. Regards Kumar
Re: [SQL] Error message during compressed backup
Hi Peter Eisentraut, >>select proowner from pg_proc where proname = 'plpgsql_call_handler'; It gives me an id '101' While I search for the users in the pg_user, there is no user of id 101 select * from pg_user where usesysid = 101; No result was fetched. While I search this way select * from pg_user where usename like 'postgres' This give me a result username = postgres usesysid = 1 In comments you said >> You may want to adjust the owner of the function to a valid user (use UPDATE). Do u mean I need to update the table pg_proc, with the following statement UPDATE pg_proc SET proowner = 1 where proname = 'plpgsql_call_handler'; I am asking this because to ensure not any wrong impact gets into the live database. Pls advise. Regards Kumar - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Senthil Kumar S" <[EMAIL PROTECTED]> Cc: "psql" <[EMAIL PROTECTED]> Sent: Friday, October 24, 2003 6:01 PM Subject: Re: [SQL] Error message during compressed backup > Senthil Kumar S writes: > > > $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f /home/db_repository/testdb20031023.sql.tar.gz -u -F c > > > WARNING: owner of function "plpgsql_call_handler" appears to be invalid > > Run > > select proowner from pg_proc where proname = 'plpgsql_call_handler'; > > which gives you the ID of the user that owns this function. Then run > > select * from pg_user; > > to get the list of valid users. You may want to adjust the owner of the > function to a valid user (use UPDATE). > > -- > Peter Eisentraut [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] writing a dynamic sql
Dear friends,I am having an lengthy SQL, which will be called every conditions of if...else statement. I tried with passing that via a string and execute it.But I got error. v_sql := 'INSERT INTO activities( activity_id, parent_activity_id, activity_type_id, subject, description, category_id, priority_id, activity_start_time, activity_end_time,) VALUES ( NEXTVAL(\'seq_activities\'), rec_recurrence.activity_id, rec_activity.activity_type_id, rec_activity.subject, rec_activity.description, rec_activity.category_id, rec_activity.priority_id, rec_activity.activity_start_time, rec_activity.activity_end_time);'; execute v_sql;I got an error saying that the record variable which I declared earlier isnot having reference (or it does not exists).Any suggestion to pack this inside a dynamic function and call it insidemany if conditions.Pls shed some light.Regardskumar
