Thread: Re: Postgres DB

Re: Postgres DB

From

Tom Lane

Date:

23 February 2004, 02:29:15

"Sumita Biswas" <sbiswas@cisco.com> writes:
> But when I type the command "psql" and don't specify a DB name, it says
> that:
> psql: FATAL:  Database "postgres" does not exist in the system catalog.

Yes, because psql's default behavior is to try to connect to the DB with
the same name as your user name.  That doesn't mean that such a DB is
guaranteed to exist, or even that anything is going to try to create it
for you.  It's just a default behavior that people have found handy.
        regards, tom lane

Input Arguments

From

"Sumita Biswas"

Date:

28 February 2004, 21:11:40

One more query:

Cant I do something like this:

$2 := $2 + interval ''86399 seconds'';

Where $2 is one of my input variables to the function.
It gives me an error:
ERROR:  $2 is declared CONSTANT

Re: Error Number in Function

From

"Sumita Biswas"

Date:

28 February 2004, 21:17:56

Thanks for the clarification Tom.

I have one more query. 
Is there a way to get the error number for the last Transact-SQL
statement executed in a Function in postgresql?
In MSSQL Stored Procedures we used @@ERROR variable to get the same. Is
there an equivalent in postgresql functions?

Regards,
Sumita

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Monday, February 23, 2004 8:59 AM
To: Sumita Biswas
Cc: pgsql-sql@postgresql.org
Subject: Re: Postgres DB 


"Sumita Biswas" <sbiswas@cisco.com> writes:
> But when I type the command "psql" and don't specify a DB name, it 
> says
> that:
> psql: FATAL:  Database "postgres" does not exist in the system
catalog.

Yes, because psql's default behavior is to try to connect to the DB with
the same name as your user name.  That doesn't mean that such a DB is
guaranteed to exist, or even that anything is going to try to create it
for you.  It's just a default behavior that people have found handy.
        regards, tom lane

DATEADD

From

"Sumita Biswas"

Date:

28 February 2004, 21:22:02

I had the following code in my MSSQL Stored Procedure:
--Adding 23Hrs 59Mins 59Secs to the ToDate(@ld_ToDate is
DateTime datatype)SET @ld_ToDate = DATEADD(ss, 86399, @ld_ToDate);

I have changed it to the following for Postgres Function:
--Adding 23Hrs 59Mins 59Secs to the ToDate--Here $2 is an input variable(timestamp datatype)    lv_ToDate := $2 +
interval''86399 seconds'';
 


Does this make sense?

Any pointers appreciated.

Thanks,
Sumita

EXECUTing QUERY:

From

"Sumita Biswas"

Date:

28 February 2004, 21:24:24

I had the following code in an MSSQL Stored Procedure:
SET @QueryToPopulateTemp= "INSERT INTO
#Tmp_Conference(ConferenceType, CallManagerId, ClusterId,
DestConversationId, ConnectDate, FinalDestination)"        +" SELECT 1, Global_CallId_CallManagerId,
globalCallId_ClusterID, DestConversationID, StartDate = MIN
(Connect_Date), Final_Destination "        +" FROM Tbl_Billing_Data WHERE (connect_date
BETWEEN '"+        cast(@ld_FromDate as NVARCHAR) +"' AND '"+           cast(@ld_ToDate as NVARCHAR) +"' OR
disconnect_date BETWEEN '"+            cast(@ld_FromDate as NVARCHAR)+"' AND
'"+cast(@ld_ToDate as NVARCHAR)+"')  AND DestConversationID <> 0 AND
Call_Type='" + @lv_Adhoc_Conf + "'"        +" GROUP BY Global_CallId_CallManagerId,
globalCallId_ClusterID, DestConversationID, Final_Destination order by
startdate";            EXEC sp_executesql @QueryToPopulateTemp;

------------------------------------------------------------------------
-------------------------------------------------

I have changed it to the following in the Postgres Function:

QueryToPopulateTemp := ''INSERT INTO Tmp_Conference(ConferenceType,
CallManagerId, ClusterId, DestConversationId, ConnectDate,
FinalDestination)''        +'' SELECT 1, Global_CallId_CallManagerId,
globalCallId_ClusterID, DestConversationID, StartDate = MIN
(Connect_Date), Final_Destination ''        +'' FROM Tbl_Billing_Data WHERE (connect_date
BETWEEN ''''+        cast(ld_FromDate as NVARCHAR) +'''' AND ''''+        cast(ld_ToDate as NVARCHAR) +'''' OR
disconnect_date BETWEEN ''''+        cast(ld_FromDate as NVARCHAR)+'''' AND
''''+cast(ld_ToDate as NVARCHAR)+'''')  AND DestConversationID <> 0 AND
Call_Type='''' + lv_Adhoc_Conf + ''''''        +'' GROUP BY Global_CallId_CallManagerId,
globalCallId_ClusterID, DestConversationID, Final_Destination order by
startdate'';
    PERFORM QueryToPopulateTemp;


------------------------------------------------------------------------
-------------------------------------------------

Does this look fine?

Regards,
Sumita

Re: Postgres DB

From

"Sumita Biswas"

Date:

28 February 2004, 21:33:30

The following Query string , when executed does not give an Error, even
though the table Tbl_Billing_Data is not present.

QueryToPopulateTemp := ''INSERT INTO Tmp_Conference(ConferenceType,
CallManagerId, ClusterId, DestConversationId, ConnectDate,
FinalDestination)''+'' SELECT 2, Global_CallId_CallManagerId,
globalCallId_ClusterID, DestConversationID, StartDate = MIN
(Connect_Date), Final_Destination ''+'' FROM Tbl_Billing_Data'';

PERFORM QueryToPopulateTemp;

I some how feel that the Query is not getting executed at all, otherwise
its sure to give an error.

So can you please comment what would have gone wrong?

Regards,
Sumita

Execute Query in Postgres Function

From

"Sumita Biswas (sbiswas)"

Date:

29 February 2004, 18:29:22

How do we execute a Query String in Postgresql function?
Or what is the equivalent of the following Code in MSSQL Stored
Procedure:

SET @QueryToPopulateTemp = "INSERT INTO
#Tmp_ConferenceDetail(ConferenceType, ConnectTime,
DisconnectTime,DestConversationId, Duration, AppConfId, OrigDeviceName,
DestDeviceName, destLegIdentifier) " + 
"SELECT  * FROM Tbl_Billing_Data ";        

EXEC sp_executesql @QueryToPopulateTemp;


Thanks for any inputs.

Regards,
Sumita

Unicode Support

From

"Sumita Biswas (sbiswas)"

Date:

29 February 2004, 18:46:30

Hi All,

This is regarding Unicode Encoding(UTF8) in Postgres.
I have the following assumptions, please correct me if I am wrong:

1. Postgres Supports unicode encoding(UTF8).
2. Any database takes the default encoding unless specified. Hence we
can configure a Database to support unicode(UTF8) while creating the
database.
3. The datatype which will store unicode data will be "character
varying" as there are no other datatypes like NVARCHAR(in MSSQL) in
Postgres. This will happen only when the Database is configured to
support unicode(UTF8) encoding.

Regards,
Sumita

CURSORS - Please help urgent.

From

"Sumita Biswas (sbiswas)"

Date:

29 February 2004, 18:52:59


The follow code gives me Error:

DECLARE CURSOR_GET_ALL_CONFERENCE_RECORDS CURSOR FOR Select
ConferenceType,CallManagerId,ClusterId,DestConversationId,AppConfId,Fina
lDestination FROM Tmp_Conference;

OPEN CURSOR_GET_ALL_CONFERENCE_RECORDS;
FETCH FROM CURSOR_GET_ALL_CONFERENCE_RECORDS INTO lv_ConfId,
li_CallManagerId, lv_ClusterId, li_DestConversationId, lv_AppConfId,
lv_FinalDestination;
-- Close and de-allocate cursor
CLOSE CURSOR_GET_ALL_CONFERENCE_RECORDS;

Error is as follows:
plpgsql: ERROR during compile of proc_conferencesummary near line 212
ERROR:  syntax error at or near "OPEN"

Please comment what can be wrong.

Regards,
Sumita

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Monday, February 23, 2004 8:59 AM
To: Sumita Biswas
Cc: pgsql-sql@postgresql.org
Subject: Re: Postgres DB 


"Sumita Biswas" <sbiswas@cisco.com> writes:
> But when I type the command "psql" and don't specify a DB name, it 
> says
> that:
> psql: FATAL:  Database "postgres" does not exist in the system
catalog.

Yes, because psql's default behavior is to try to connect to the DB with
the same name as your user name.  That doesn't mean that such a DB is
guaranteed to exist, or even that anything is going to try to create it
for you.  It's just a default behavior that people have found handy.
        regards, tom lane

Re: Postgres DB

From

Michael Chaney

Date:

01 March 2004, 02:16:43

On Mon, Feb 23, 2004 at 02:24:32PM +0530, Sumita Biswas wrote:
> The following Query string , when executed does not give an Error, even
> though the table Tbl_Billing_Data is not present.
> 
> QueryToPopulateTemp := ''INSERT INTO Tmp_Conference(ConferenceType,
> CallManagerId, ClusterId, DestConversationId, ConnectDate,
> FinalDestination)''+'' SELECT 2, Global_CallId_CallManagerId,
> globalCallId_ClusterID, DestConversationID, StartDate = MIN
> (Connect_Date), Final_Destination ''+'' FROM Tbl_Billing_Data'';

I had a situation where a subquery had an error in it, and the query
simply returned no rows instead of throwing an error.

It looks like it might well be a bug that we're experiencing, I just
haven't had time to chase it down.

Michael
-- 
Michael Darrin Chaney
mdchaney@michaelchaney.com
http://www.michaelchaney.com/

Postgres DB

From

"Sumita Biswas"

Date:

26 April 2004, 14:17:54

Hi All,

Our application is using the Postgres 7.3.4-RH database that is packaged
with the AS3.0 for CCM. There is a variable type called refcursor that
is being used by CAR Functions in Postgres database. This variable works
fine when we execute the postgres Function from the database i.e through
psql. When we try to execute the Function from java code, it uses
pg73jdbc3.jar which is bundled with the Postgres 7.3.4, and
pg73jdbc3.jar does not support this variable type refcursor.

It works fine with pg74jdbc3.jar which comes along with Postgres 7.4.

Is this a bug?


Regards,
Sumita