[SQL] ConnecDB() -- couldn't send SSL negotiation packet:

[Bhuvan A]

hi,

While i am trying to connect pgsql from a perl program, i am
getting the error message as

ConnectDB()  -- couldn't send SSL negotiation packet:
errno=9 Bad file descriptor

Why i am getting this error?
How can i rectify this problem?

Remember, i am socksifying my application for some necessary
reason.




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] problem with date/time constants

[Bhuvan A]

Hi,

'now' is a function... 
so try 'select now();'

On Tue, 3 Jul 2001, datactrl wrote:

> Hi, 
> server:PostgreSQl 7.1
> why doesn't following statement work?
> "select now;"
> 
> THANK YOU!
> JACK LIU
> 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] how can we change definition of a table once created?

[Bhuvan A]

hello sirs,,

can any one say how can we change the table definition once
created?

say, we have a table with 1000s of records. if one needs
to change the data type of particular column or if he
needs to change the width of a column what should be
done? 

As of now, i am copying the data to a file(COPY COMMAND) and
creating a new table after deleting the old one and copying
the content of that file to the new table. 

is it the only way to do it?


Bhuvan.


---(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

[SQL] SELECT * from select - HOW?

[Bhuvan A]

hi,

how can we select * from a view named 'select'?? 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] SELECT * FROM select -- got it!

[Bhuvan A]

sorry! infact, i didn't try that with quote. now it is
fine.. sorry for ur inconvinence and thankx however.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] is it possible to comment each column of a table?

[Bhuvan A]

hi all,

is it possible to comment a column of a table similar to
EXTRA of mySQL.

If yes then how??


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] CAST(ipaddress as text) -- HOW?

[Bhuvan A]

hi all,

how can we cast an ipaddress(type cidr) to any other type?

thankx in advance..


Regards,

Bhuvaneswar.


---(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: [SQL] CAST(ipaddress as text) -- HOW?

[Bhuvan A]

hi,

ipaddress::text

is resulting in

ERROR:   Cannot cast type 'cidr' to 'text'

how else we can???


Thankx.


On Tue, 17 Jul 2001, omid omoomi wrote:

> ie,
> ipaddrss::text
> 
> >From: Bhuvan A <[EMAIL PROTECTED]>
> >To: [EMAIL PROTECTED]
> >Subject: [SQL] CAST(ipaddress as text)   -- HOW?
> >Date: Tue, 17 Jul 2001 19:35:53 +0530 (IST)
> >
> >
> >hi all,
> >
> >how can we cast an ipaddress(type cidr) to any other type?
> >
> >thankx in advance..
> >
> >
> >Regards,
> >
> >Bhuvaneswar.
> >
> >
> >---(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
> 
> _
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
> 


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] CAST(ipaddress as text) -- HOW?

[Bhuvan A]

hi

ipaddr::text 

I got it!

few among the many ways are

SELECT * FROM .. WHERE CAST(ipaddr::inet as text) ~ '200';
SELECT * FROM .. WHERE text(ipaddr::inet) ~ '200';
SELECT * FROM .. WHERE host(ipaddr::inet) ~ '200';

Regards,
Bhuvaneswar.


On Thu, 19 Jul 2001, omid omoomi wrote:

> Sorry  friend, you are right. I could not do that too. :o
> I'll look for it.
> omid
> 
> >From: Bhuvan A <[EMAIL PROTECTED]>
> >To: omid omoomi <[EMAIL PROTECTED]>
> >CC: [EMAIL PROTECTED]
> >Subject: Re: [SQL] CAST(ipaddress as text) -- HOW?
> >Date: Wed, 18 Jul 2001 14:53:04 +0530 (IST)
> >
> >
> >hi,
> >
> >ipaddress::text
> >
> >is resulting in
> >
> >ERROR:   Cannot cast type 'cidr' to 'text'
> >
> >how else we can???
> >
> >
> >Thankx.
> >
> >
> >On Tue, 17 Jul 2001, omid omoomi wrote:
> >
> > > ie,
> > > ipaddrss::text
> > >
> > > >From: Bhuvan A <[EMAIL PROTECTED]>
> > > >To: [EMAIL PROTECTED]
> > > >Subject: [SQL] CAST(ipaddress as text)   -- HOW?
> > > >Date: Tue, 17 Jul 2001 19:35:53 +0530 (IST)
> > > >
> > > >
> > > >hi all,
> > > >
> > > >how can we cast an ipaddress(type cidr) to any other type?
> > > >
> > > >thankx in advance..
> > > >
> > > >
> > > >Regards,
> > > >
> > > >Bhuvaneswar.
> > > >
> > > >
> > > >---(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
> > >
> > > 
> >_
> > > Get Your Private, Free E-mail from MSN Hotmail at 
> >http://www.hotmail.com.
> > >
> >
> 
> 
> _
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
> 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] how can we get total records in pg server?

[Bhuvan A]

Hi all,

how can we get the COUNT of total records in the db server?

hope this could be simple for pg experts.
thankx in advance!

Regards,
Bhuvaneswar.


Eighty percent of married men cheat in America.  The rest cheat in Europe.
-- Jackie Mason



---(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: [SQL] how can we get total records in pg server?

[Bhuvan A]

hi all,
consider the pg server with databases
bhuvan
uday
guru

the need is to get TOTAL RECORDS of all the THREE DATABASES or atleast
TOTAL RECORDS of ONE DATABASE.

Seems to be simple.
Thankx in advance and infact i was a newbie. 

Regards, 
Bhuvaneswar.

  "There is nothing new under the sun, but there are lots of old things 
  we don't know yet."
-Ambrose Bierce


On Mon, 23 Jul 2001, omid omoomi wrote:

> you mean this ?
> select count(*) from tablefoo;
> 
> 
> >From: Bhuvan A <[EMAIL PROTECTED]>
> >To: [EMAIL PROTECTED]
> >Subject: [SQL] how can we get total records in pg server?
> >Date: Mon, 23 Jul 2001 20:03:42 +0530 (IST)
> >
> >
> >Hi all,
> >
> >how can we get the COUNT of total records in the db server?
> >
> >hope this could be simple for pg experts.
> >thankx in advance!
> >
> >Regards,
> >Bhuvaneswar.
> >
> > Eighty percent of married men cheat in America.  The rest cheat in 
> >Europe.
> > -- Jackie Mason
> >
> >
> >
> >---(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
> 
> 
> _
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
> 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] is it possible to use arrays in plpgsql function??

[Bhuvan A]

hi all,

Is it possible to use arrays of any datatypes in plpgsql function? 

If yes, how??

thankx.

 ==
   Every absurdity has a champion who will defend it.

 ==
Regards,
Bhuvaneswar.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[SQL] Getting 'n-1'th record.

[Bhuvan A]

hi all,

consider below..

An sql query results with 'n' records. OK.
I need ONLY the 'n-1'th record.


HOW CAN I GET THIS?

Thankx in advance!

 ==
Q:  What's the difference between the 1950's and the 1980's?
 A: In the 80's, a man walks into a drugstore and states loudly, "I'd
like some condoms," and then, leaning over the counter, whispers,
 "and some cigarettes."

 ==

Regards,
Bhuvaneswar.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] trigger trouble -- procedure not found

[Bhuvan A]

On Sep 14, Kevin Way wrote:

> I'm having trouble creating a trigger.  First i'm creating a function,
> add_to_search in PL/pgSQL.
> 
> \df verifies the existance of this function:
>  smallint | add_to_search   | text, text, text, integer
> 

--  We can verify all the functions which wont return 'OPAQUE'.

> but when I call:
> 
> CREATE TRIGGER item_insert_search_add AFTER INSERT
> ON item FOR EACH ROW
> EXECUTE PROCEDURE add_to_search (name, description, reason, node_id);
> 
> I get: 
> ERROR:  CreateTrigger: function add_to_search() does not exist

The function executed by a trigger should by default return a
record. ie.. the return type should be 'OPAQUE'.

Try the above..
Hope this will work.

> 
> What am I missing here?  It seems to me that I'm missing something very
> simple, but I can't figure out what it is for the life of me.
> 
> Kevin Way
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [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]

[Bhuvan A]

Hi all,

Consider this..

SELECT EXTRACT(EPOCH FROM TIMESTAMP(now()));
 date_part

 1000467997
(1 row)

Fine..

Similarly, how could i get timestamp value for these SECONDS?
ie.. VICE VERSA


Something like this..
SELECT EXTRACT(TIMESTAMP FROM INTERVAL('1000467997 SECONDS'));


:)


Kindly Apologize for any inconvenience.


 ==
   He who invents adages for others to peruse
   takes along rowboat when going on cruise.

 ==

Regards,
Bhuvaneswar.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] How to Get Timestamp From SECONDS?

[Bhuvan A]

Hi all,

Consider this..

SELECT EXTRACT(EPOCH FROM TIMESTAMP(now()));
 date_part

 1000467997
(1 row)

Fine..

Similarly, how could i get timestamp value for SECONDS?
ie.. VICE VERSA


Something like this..

:)

select extract(timestamp from interval('1000467997 seconds'));

:)


Kindly Apologize for any inconvenience.


 ==
   "Help Mr. Wizard!"
  -- Tennessee Tuxedo

 ==

Regards,
Bhuvaneswar.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] COUNT func

[Bhuvan A]

hi,
try this too..

select count(*),age,status from person  where age=40 and
status='MARRIED' group by age,status;
 
Regards,
Bhuvaneswar.

On Oct 18, Oleg Lebedev wrote:

> Hi,
> I am trying to count the number or rows in a table with similar field
> values. I want to do it in one table scan.
> In other words, say I want to count the number of rows in Person table,
> having age equal to 40, and the number of rows with status as 'married'.
> I want Person table to be scanned only once during this query.
> So, basically I want to do the following:
> SELECT COUNT(age=40), COUNT(status='married')
> FROM Person;
> 
> I tried to use aggregate functions with group by and having clauses, but
> I still can't figure it out (probably because of the lack of
> experience).
> Please help.
> Thanks,
> 
> Oleg
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Need a VIEW without SUB-SELECT

[Bhuvan A]

Hi all,

Kindly apologize any inconvenience!

I have 2 tables status and po_status like..

  CREATE TABLE status ( 
status_id int primary key,
status_name   varchar(15)
  );

  CREATE TABLE po_status (
po_no varchar(15),
approval_status_id  int references status(status_id),
acknowledgement_status_id   int references status(status_id)
  );


Right..

Now, how can i have a VIEW on po_status table to get
'approval_status_name' and 'acknowledgement_status_name' instead of
approval_status_id and acknowledgement_status_id?

I am sure that a VIEW with SUB-SELECT will solve everything. But my QB
won't support SUB-SELECT and i wasn't ready to redesign my QB, which
is a time consuming process.

How can i accompolish this 

Thankx in advance.
 
==
  Kime's Law for the Reward of Meekness:
  Turning the other cheek merely ensures two bruised cheeks
==

Regards,
Bhuvaneswar.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Connecting to different DataBase In PlPgsql Function

[Bhuvan A]

hello all,

How can we connect to different database using plpgsql function?  Can
we?

Thankx in advance.

  ==
  A beer delayed is a beer denied.
  ==

Regards,
Bhuvaneswar.


---(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

[SQL] Strange Problem As Type Casting

[Bhuvan A]

hi all,

i am using postgresql 7.1.
here i am facing some strange problem for last 2 days.

i have a table 'users' as below

test=> \d users
Table "users"
 Attribute |   Type   |   Modifier  
---+--+---
 user_id   | integer  | not null default 
nextval('user_id_seq'::text)
 login_id  | text |
 user_code | text |
 comp_code | text |
 user_name | text |
 password  | text |
 last_login| timestamp with time zone |
 noof_logins   | integer  |
 dob   | date |
 eff_date  | date |
 is_active | boolean  | default 'f'
 email_id  | text |
 creation_time | timestamp with time zone |
 modification_time | timestamp with time zone |
Indices: users_login_id_key,
 users_pkey,
 users_user_code_key
Constraint: ((email_id ~ '@'::text) AND (email_id ~ '.'::text))



here user_id is of type integer and not serial. ie.. i explicitly created 
the sequence and had it as default.
while i update user_id field

test=> UPDATE users set user_id=1 ,login_id='admin' where user_id='1346';
ERROR:  Unable to identify an operator '=' for types 'text' and 'int4'
You will have to retype this query using an explicit cast

again i tried it as below
test=> UPDATE users set login_id='admin' where user_id='1346';
UPDATE 1
test=> UPDATE users set user_id='1' where login_id='admin';
ERROR:  Unable to identify an operator '=' for types 'text' and 'int4'
You will have to retype this query using an explicit cast

what could be the problem here?

Thankx in advance

With Regards,
Bhuvaneswar.



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] COPY COMMAND -- Hanging

[Bhuvan A]

hi all,

i am facing a strange problem in using COPY COMMAND.

As i would like to merge 2 databases, i dumped data from one by
$ pg_dump -R bhuvan -f bhuvan.sql

Here i found data dumped using COPY COMMAND.
now i tried to restore few of the tables. so i copied those tables 
alone to seperate file copy_commands.sql. then, i just tried this

$ psql bhuvan -f copy_commands.sql

...

i didnt get back with the result.. even none of the records have
been copied. what could be the problem here?

 ==
  Youth is when you blame all your troubles on your parents; maturity is
  when you learn that everything is the fault of the younger generation.

 ==

Regards,
Bhuvaneswar.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] How to Return Unique Elements From An Array?

[Bhuvan A]

hello all,

how can we get unique elements from an array(of any type)?

Regards,
Bhuvaneswar.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] COPY COMMAND -- Hanging

[Bhuvan A]

hi all,

i am facing a strange problem in using COPY COMMAND.

As i would like to merge 2 databases, i dumped data from one by
$ pg_dump -R bhuvan -f bhuvan.sql

Here i found data dumped using COPY COMMAND.
now i tried to restore few of the tables. so i copied those tables 
alone to seperate file copy_commands.sql. then, i just tried this

$ psql bhuvan -f copy_commands.sql

...

i didnt get back with the result.. even none of the records have
been copied. what could be the problem here?

 ==
  Youth is when you blame all your troubles on your parents; maturity is
  when you learn that everything is the fault of the younger generation.

 ==

Regards,
Bhuvaneswar.




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] how to write procedures

[Bhuvan A]

Hi,

it seems you weren't yet installed appropriate procedural language. for 
example, if you wish to code in plpgsql you must install 'plpgsql'. for 
details refer the documentation and it has enough information.

regards,
bhuvaneswaran.

On Thu, 4 Jul 2002, srikanth wrote:

> Hi, I am using postgre sql server on linux server but for my database I am
> using storedprocedures which i need to create , but there are no commands to
> create procedures it says it does not support is there any way to work with
> stored procedures in postgre sql server.
> thanks,
> srikanth.
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] contrib/dblink suggestion

[Bhuvan A]

Hi,

I am using postgresql 7.2.1.
I badly require to interconnect between databases. contrib/dblink seems to
be handy and ofcourse it well suits my requirement. But while browsing
across, i heard that it is not advicable to use it. So i wish to know
someone's experience in using dblink and how handy it is. 

Will contrib/dblink be available with future postgresql releases? Valuable 
suggestions are very welcome. 

TIA.

regards, 
bhuvaneswaran


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] contrib/dblink suggestion

[Bhuvan A]

Dear Mr.Joe Conway,

Thank you very much for your suggestion. 

Really nowhere i got to know about specific problem in dblink. But some
discussions had went on this mailing list long back and here is that link

http://archives.postgresql.org/pgsql-sql/2002-05/msg7.php

Anyway thank you once again.

regards, 
bhuvaneswaran

On Tue, 30 Jul 2002, Joe Conway wrote:

> Bhuvan A wrote:
> > I am using postgresql 7.2.1.
> > I badly require to interconnect between databases. contrib/dblink seems to
> > be handy and ofcourse it well suits my requirement. But while browsing
> > across, i heard that it is not advicable to use it. So i wish to know
> > someone's experience in using dblink and how handy it is. 
> > 
> > Will contrib/dblink be available with future postgresql releases? Valuable 
> > suggestions are very welcome. 
> > 
> 
> I've heard of at least two people who seem to be using dblink fairly 
> heavily without problems. One recently reported something like 500 
> million records transferred without error. I use it myself, but not in 
> what I'd call heavy use.
> 
> If you are aware of any specific problems, please point me to them, and 
> I'll fix them before the next release.
> 
> Thanks,
> 
> Joe
> 


---(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

[SQL] Casting integer to boolean

[Bhuvan A]

Hi,

I am using postgresql 7.2.1.

How do i cast an integer value to boolean? I did try the below sequence of
SQLs and was little bit confused, by the way it behaves. It casts the
integer value to boolean in one case but not ever again.

bhuvan=> SELECT count(*)::int::boolean from my_table;
ERROR:  Cannot cast type 'integer' to 'boolean'
bhuvan=> -- The SQL similar to the above SQL is my requirement
bhuvan=> SELECT true where (1);
ERROR:  WHERE clause must return type boolean, not type integer
bhuvan=> SELECT true where (1::boolean);
 bool
--
 t
(1 row)

bhuvan=> SELECT true where (1::int::boolean);
ERROR:  Cannot cast type 'integer' to 'boolean'
bhuvan=>

I donot know whether i am wrong or its a bug. I request someone to correct
me if i am wrong or please suggest me the right way to cast an integer to
boolean as, returning true for non-zero value, false otherwise.

regards, 
bhuvaneswaran



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Trigger/Function problem

[Bhuvan A]

On Wed, 21 Aug 2002, Andreas Johansson wrote:

> Hi all,
> 
> I have a slight problem using triggers and functions in PostGreSQL. I'm
> currently running PostGreSQL 7.2.1 on a RedHat 7.2. I've compiled the
> sources (no rpm installation).
> 
> I have several different tables and in each table there's a status flag
> that's telling if a row is deleted or not (I don't actually want to delete
> the rows).
> 
> Here are my tables (simplified).
> 
> Site:
> idserial
> name  char(120)
> statusint
> 
> Page:
> idserial
> name  char(120)
> statusint
> site_id   int
> 
> Text:
> idserial
> name  char(120)
> statusint
> page_id   int
> 
> 
> 
> Now to my problem. What I want to do is if I change status in site or page
> the child items to them should also change. No problem I thought, I'll solve
> this with a trigger and a function. Did my first test like this:
> 
> ---
> create function fix_status() returns opaque
> as '
> BEGIN
> IF old.status <> new.status THEN
>   update text set status = new.status
> where page_id = new.id;
> END IF;
> RETURN new;
> END;
> '
> language 'plpgsql';
> 
> 
> CREATE TRIGGER page_status
> AFTER UPDATE ON pages FOR EACH ROW
> EXECUTE PROCEDURE fix_status();
> ---
> 
> Works fine. Now I'd like to add the same thing for the site table. No
> worries I thought but I don't wont to duplicate the fix_status function so
> I'll make it take a parameter.

Its not necessary to duplicate or change the parameters to execute the
same function triggered from multiple tables. Currently it is not possible 
to trigger a function with arguments. All you have to do is to trigger the 
same function on site table too,

create trigger site_status after update on sites
for each row execute procedure fix_status();

and it should do the trick.

> 
> ---
> create function fix_status(char, char) returns opaque
> as '
> BEGIN
> IF old.status <> new.status THEN
>   update $1 set status = new.status where $2 = new.id;
> END IF;
> RETURN new;
> END;
> '
> language 'plpgsql';
> 
> 
> CREATE TRIGGER page_status
> AFTER UPDATE ON pages FOR EACH ROW
> EXECUTE PROCEDURE fix_status('text','page_id');
> 
> ---
> 
> Then I get the following error:
> 
> ERROR:  CreateTrigger: function fix_status() does not exist
> 
> Why doesn't the trigger acknowledge that I want to call fix_status with a
> parameter for which table name I should use?

It doesn't acknowledge since it is unable to refer the oid of the function
it has been trying to trigger (with params). Refer pg_trigger for more 
details.

regards,
bhuvaneswaran

> 
> I'm completely stuck and I someone out there can help me.
> 
> -> Andreas
> 
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] record count

[Bhuvan A]

> 
> Hello list,
> 
> Is there a system attribute or table or ? providing record count by table? 
> 
> Thanks. 
> 

pg_class.reltuples has the record count.

regards,
bhuvaneswaran



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] DELETE command is getting blocked

[Bhuvan A]

Hi,

I am using 7.2.

I have problem in deleting records from a particular table in my database.  
Say, if the DELETE command results deleting 10 records, the command is 
blocked at the end and is not returning. FYI, that table donot have any
triggers/constraints/rules on delete. I have checked whether its due to
the same table access by some other client, but it is not so. No other
process is accessing this table by that time. 

Infact i am able to repeat the problem. To make sure, i wrote a plpgsql
and tried deleting these 10 records one by one in a for loop. There too it
is blocked for infinite time, after deleting the last record. what would
be the problem? Is the table corrupted?

FYI, table has a primary key which is referred from different tables.  
Anyhow i have deleted records from foreign key tables, before deleting
from the primary key table.

I am lucky if i have given enough information, you required. If not please
get me back. It is quite urgent. Please..

TIA.

regards, 
bhuvaneswaran







---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] How can I retrieve an info about function arguments ?

[Bhuvan A]

> I need to retreive full set of info about argument and return types (for
> variable length too).

test_db=>\df+ my_func
will serve your need :(

> How I can get it from system catalog ?
pg_proc.pronargs-- count of arguments
pg_proc.proargtypes -- oid(s) of pg_type for appropriate arguments

regards,
bhuvaneswaran


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] 'next' or similar in plpgsql

[Bhuvan A]

Hi,

In perl we have 'next' function to skip rest of the statements in the loop
and to start with next iteration. In plpgsql, do we have something
similar? How do we skip rest of the statements in a loop in plpgsql?

TIA.

regards, 
bhuvaneswaran


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Database Design tool

[Bhuvan A]

> Can anybody take me reference on Database design tool with PostgreSQL 
> support.
> 

visit, http://sourceforge.net/projects/phppgadmin/

regards,
bhuvaneswaran


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] How do I get rid of these messages?

[Bhuvan A]

> How do I get rid of the messages like "NOTICE:  CREATE TABLE / PRIMARY
> KEY will create implicit index 'test_pkey' for table 'test'" coming out
> from stderr when I run psql with my create table script?
> 

AFAIK, by default it cannot be done eventhough syslog is enabled (somebody
correct me if wrong), since such messages are sent to the frontend in all
cases. If you are ready to re-compile, i have the patch.  You can contact
me.

regards,
bhuvaneswaran



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] Password user postgres

[Bhuvan A]

On Mon, 23 Dec 2002, Arnaudo Massimo wrote:

> Hi everibody,
> i have to see the table pg_shadow, but i don't remind the default password
> for user postgres in a Debian system.
> 
> Can you help me?

Edit pg_hba.conf and set the method to 'trust' and restart postmaster.  
Login(it wont ask for password) and change the password of postgres user,
restore pg_hba.conf and restart postmaster.

regards,
bhuvaneswaran



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Adding a Primary Key to an exisiting table

[Bhuvan A]

> Hi
> 
> I just exported my MS Access databases into postgres using an ODBC driver,
> but the thing is during the export postgres doesn't recognize any of my
> primary keys in Access.
> Is there any way to alter the tables so that i can make one of my current
> fields the primary key?  I don't want to drop the column since I already
> have data in the table and i don't want to create/drop tables as some of
> the documentation suggests since most of my tables have 10+ fields.. any
> suggestions??
> 

AFAIK, it can be done in any of the below two ways:

1. Create unique index
   CREATE UNIQUE INDEX index_name ON table_name USING btree (field1, .., 
   fieldn);
2. Add primary key constraint
   ALTER TABLE table_name ADD PRIMARY KEY (field1, .., fieldn);

For this to happen, the fields should have unique values.

regards,
bhuvaneswaran



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] pg_dump problem

[Bhuvan A]

> mv camper.dump20020116 camper_bak/
> 
> The error I get is ::
> 
> mv: camper.dump20020116: Value too large for defined data type
> 

It may not be a postgresql problem. It may be due to your filesystem. Try 
the documentation or mailing list of your filesystem.

regards,
bhuvaneswaran



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] Implementing automatic updating of primary keys...

[Bhuvan A]

> 
> there can be many  such tables that have foreign key id which is referencing
> the master table test_master column "id". what we want is when some ids become 
   ^^^
It should be profile_master, right?

> redundant we have to merge two ids , we want that thru out the system the 
> occurance of the old primary key shud be removed and replaced with the new id.
> 

Definitely it can be done in the database level. Trigger a function before
delete on the primary table (profile_master) and update the foreign tables
with appropriate id and so the redundant id can be deleted from the
primary table. 

regards,
bhuvaneswaran



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] PostgreSQL + SSL

[Bhuvan A]

> I´m trying to config PG with SSL, but i got a error. I create the key
> and the certificate and put both in $PGDATA directory. I also enabled
> the ssl option in postgresql.conf. But when i run postmaster i got a
> error saying that server.key has wrong permissions.

It reports the error in either of the below cases:

1. If the file permission is not -r--r--r--.
2. If the certificate and the private key are invalid.  

The clear advice is available in the documentation itself. Try out
http://developer.postgresql.org/docs/postgres/ssl-tcp.html for details.

regards,
bhuvaneswaran




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] DBCC CheckIdent in a stored proc?

[Bhuvan A]

> I need to know the last id of the last record added to the table from an
> ASP page.

=> select field1 from my_table order by oid desc limit 1; -- will do that.

regards,
bhuvaneswaran



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] CAST from VARCHAR to INT

[Bhuvan A]

> (Postgres 7.2.1)
> 
> I screwed up when I was designing a table a while back and made a column a
> VARCHAR that referenced (and should have been) an INT.
> 
> Now I'm trying to correct my mistake, I've created a new table and I'm
> trying to INSERT INTO...SELECT the data into it, but it's complaining that
> it can't stick a VARCHAR into an INT. All the values in the column are valid
> integers (the foreign key sees to that) but even a CAST won't do it.
> 
> How can I force it to copy/change the values?
> 

varchar cannot be casted to integer directly. Rather we can do it this 
way:

=> select your_varchar_field::text::int from your_table;

regards,
bhuvaneswaran



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql

[Bhuvan A]

> Can someone tell me how I can adjust the syntax of the code and in global:
> how can I convert sql-code , for microsoft sql server 2000, to sql-code for
> postgresql?
> 

Try,
http://techdocs.postgresql.org/techdocs/sqlserver2pgsql.php

regards,
bhuvaneswaran


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] Format in psql

[Bhuvan A]

Hi,

I am using pgsql-7.2.3. Can i able to format the output of a SELECT sql in 
psql as perl format? 

Something like,
+--++
| work_desc_id |   short_desc   |
+--++
|1 | Short description line 1   |
|  | Short description line 2   |
|  | Short description line 3   |
|  | Short description line n   |
+--++

If yes, how can i do this? I have gone through the manual page of psql but
such formatting information donot appear over there. So, hope to get some
details over here. A link would be enough.

regards, 
bhuvaneswaran


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] Format in psql

[Bhuvan A]

> man psql.
> 
> Inside psql:
> \pset border 2
> 

Fine, \pset border 2 draws the border. But it donot format the multi line
value, without affecting the format of other column. I want to format the
multi line column appropriately. It should not affect the format of other
column, similar to perl format ($ man perlform). Hope i am clear.

regards,
bhuvaneswaran




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]