Re: [SQL] [HACKERS] ALTER TABLE ( smallinto -> boolean ) ...
Moved off of -hackers, since its long gotten out of that realm :) On Thu, 1 Sep 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: On Mon, 29 Aug 2005, Tom Lane wrote: No, because there's no built-in cast from smallint to bool. 'k, I just took a read through the "CREATE CAST" man page, and don't think I can use that for this, Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. Ah, okay, I just re-read the man page and think I stumbled upon what I overlooked the first time ... all I want to do is: CREATE CAST ( 0 AS boolean ) WITH FUNCTION AS ASSIGNMENT; And then each time I try to insert a '0' into a BOOLEAN field, it will auto convert that (based on my function) to 'f' ... And I'd need to do a second one for 1 -> 't' ... Am I reading it right this time ... ? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [HACKERS] ALTER TABLE ( smallinto -> boolean ) ...
Marc G. Fournier wrote: > Moved off of -hackers, since its long gotten out of that realm :) > > On Thu, 1 Sep 2005, Tom Lane wrote: > > > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > >> On Mon, 29 Aug 2005, Tom Lane wrote: > >>> No, because there's no built-in cast from smallint to bool. > > > >> 'k, I just took a read through the "CREATE CAST" man page, > and don't think > >> I can use that for this, > > > > Sure you can. Make a SQL or PLPGSQL function that does the > conversion > > you want and then create a cast using it. > > Ah, okay, I just re-read the man page and think I stumbled > upon what I > overlooked the first time ... > > all I want to do is: > > CREATE CAST ( 0 AS boolean ) > WITH FUNCTION > AS ASSIGNMENT; > > And then each time I try to insert a '0' into a BOOLEAN > field, it will > auto convert that (based on my function) to 'f' ... > > And I'd need to do a second one for 1 -> 't' ... > > Am I reading it right this time ... ? Here's what I'm using: CREATE OR REPLACE FUNCTION int2bool (INTEGER) RETURNS BOOLEAN AS $$ SELECT ($1 != 0); $$ LANGUAGE 'SQL' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION bool2int (BOOLEAN) RETURNS INTEGER AS $$ SELECT CASE WHEN $1 THEN 1 WHEN NOT $1 THEN 0 ELSE NULL END; $$ LANGUAGE 'SQL' IMMUTABLE STRICT; CREATE CAST (INTEGER AS BOOLEAN) WITH FUNCTION int2bool (INTEGER) AS ASSIGNMENT; CREATE CAST (BOOLEAN AS INTEGER) WITH FUNCTION bool2int (BOOLEAN) AS ASSIGNMENT; done. Owen ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [HACKERS] ALTER TABLE ( smallinto -> boolean ) ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > all I want to do is: > CREATE CAST ( 0 AS boolean ) > WITH FUNCTION > AS ASSIGNMENT; > And then each time I try to insert a '0' into a BOOLEAN field, it will > auto convert that (based on my function) to 'f' ... > And I'd need to do a second one for 1 -> 't' ... No, you want one function from smallint to boolean, and the cast the same way. (The cast is really just syntactic sugar for invoking the function.) Depending on what you want this to do, you might have to make the cast IMPLICIT rather than ASSIGNMENT. I'd try ASSIGNMENT first, though, since it's less likely to bite you when you weren't expecting it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] queries problems
HiIm having problems doing some SQL queries. I want information about the following statistics:-List lock users-Show schema owners, it may also so more schema information-Check if a certain table has a toast tableIf any one could help that would be very good.Regards__Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
Re: [SQL] queries problems
On Fri, Sep 02, 2005 at 12:33:00AM +, Joÿffe3o Carvalho wrote: > I want information about the following statistics: > -List lock users > -Show schema owners, it may also so more schema information > -Check if a certain table has a toast table See "Monitoring Database Activity" and "System Catalogs" in the documentation. Here are links to the documentation for the latest released version of PostgreSQL: http://www.postgresql.org/docs/8.0/static/monitoring.html http://www.postgresql.org/docs/8.0/static/catalogs.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] queries problems
[Please copy the mailing list on replies so others can contribute to and learn from the discussion.] On Thu, Sep 01, 2005 at 10:33:44PM -0300, João Carvalho wrote: > Michael Fuhr <[EMAIL PROTECTED]> escreveu: > > http://www.postgresql.org/docs/8.0/static/monitoring.html > > http://www.postgresql.org/docs/8.0/static/catalogs.html > > Tanks a lot, it is part of that information in there. I resolved > the schema problem and probably the toast table problem. but I still > can´t get any information in there about the users of the locks and > wich atributes are part of each index. For locks, you can join pg_locks.pid with pg_stat_activity.procpid and get user names from pg_stat_activity.usename. http://www.postgresql.org/docs/8.0/static/view-pg-locks.html http://www.postgresql.org/docs/8.0/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE For index attributes, see pg_index.indkey and pg_attribute.attnum, or the pg_get_indexdef() function. http://www.postgresql.org/docs/8.0/static/catalog-pg-index.html http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html http://www.postgresql.org/docs/8.0/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Last access date for databases/users
Is there anyway to determine when a database was last accessed, or when a user last connected to the database using the pg_* views? I'm trying to determine what databases in my system are currently active. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
