[SQL] Doubts porting from Oracle to postgresql

[Senthil]

Hai everybody,
  
Hai,    Currently, I'm at the work of 
converting database from Oracle to Postgresql.   I have some 
doubts:
 1) What is the 
equivalent of Oracle ROWNUM in Postgresql?  If there is no such equivalent, 
can U give me an idea about         
        
that? 2) How to replace 
stored procedures with many OUT parameters?  I want to call the procedure 
from Java 
code. 
Please tell me the way to return the values of OUT parameters returned 
previously by Oracle, from the plpgsql 
code.  
It's very urgent.  Please reply quickly with some code examples.
 
HOW 
to handle the values returned by the Oracle OUT parameters, in Postgresql?
    
i.e, how to return the Oracle OUT parameters'  values to the calling Java 
code, in postgresql.
    
If anybody knows, please explain with suitable examples with plpgsql code 
and also the Calling Java code.
 3) Can default values 
be assigned to postgresql function parameters?  If yes, how? 
 
 
Thanks in advance.
Senthil.

[SQL] Windows postgres

[Senthil]

Respected sir/madam,

Please can you send me link of windows postgres sql download page.

thanks
senthil


---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Change column data type

[Senthil Kumar S]

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

[Senthil Kumar S]

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

[Senthil Kumar S]

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

[Senthil Kumar S]

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

[Senthil Kumar S]

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