[SQL] force command execution
Hi, Let's suppose I have a plpgsql function like: ... begin alter ... ...; insert ... ...; create ... ...; drop ...; and lot of such commands in any order... end; ... (There is no "perform" keyword.) Sometimes "ALTER" failes becouse it is already done. Sometimes "INSERT" failes becouse record already exists. Sometimes "DROP" failes bacouse object is already dropped by earlier execution of this function. When any of the command fails function has no effect at all becouse everything is rolled back. Is it possible to run this function without rollback effect and just skip failures? (Server is 8.0.0-rc1.) If the only way is putting each command in begin perform command; exception when others then NULL; end; then I'm looking for a text processing tool which can do this "wrapping" automaticaly. I've too much functions and commands in them, so hand work is not an option. Daniel ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Combining values in a column
I have a query which returns a single column of integers which I want to combine together with &, "bitwise AND". Basically it's a simple list of access levels that a user might have and I want to merge the list into one value. There can be zero, one or more values in the list so I have to be able to cope with the no-results . For example access 31 511 Would lead to 31 & 511 which gives 31. I guess really this can lead to the more generic question. How do I apply a general function to combine the values, if I, for example, wanted to write my own aggregate function like sum? Thanks in advance, Stephen Quinney ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Combining values in a column
See below for creating aggregates: http://www.postgresql.org/docs/current/static/xaggr.html But, there is a useful function built-in, bit_and, that does what you want: http://www.postgresql.org/docs/current/static/functions- aggregate.html#FUNCTIONS-AGGREGATE-TABLE create table testint ( myint int ); CREATE TABLE insert into testint values (31); INSERT 428988938 1 insert into testint values (511); INSERT 428988939 1 select bit_and(myint) from testint; bit_and - 31 (1 row) On Apr 15, 2005, at 5:22 AM, Stephen Quinney wrote: I have a query which returns a single column of integers which I want to combine together with &, "bitwise AND". Basically it's a simple list of access levels that a user might have and I want to merge the list into one value. There can be zero, one or more values in the list so I have to be able to cope with the no-results . For example access 31 511 Would lead to 31 & 511 which gives 31. I guess really this can lead to the more generic question. How do I apply a general function to combine the values, if I, for example, wanted to write my own aggregate function like sum? Thanks in advance, Stephen Quinney ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL]
There some documentation about aggregate functions in the manual, for example: http://www.postgresql.org/docs/7.4/static/sql-createaggregate.html Here's a simple agg function that should work for you, assuming your col types are int4. CREATE AGGREGATE andsum ( sfunc = int4and, basetype = int4, stype = int4 ); TJ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] send mail from Postgres using PLTCLU language.
How to send mail
from Postgres using PLTCLU language.
I am able to send
mail, but I want to know how to add "Cc" option also. (Program attached
below)
How to add
"Cc" (Right now containd "From", "To", "Subject" and
"Message").
CREATE or REPLACE FUNCTION pgmail(text,
text, text, text) returns int4 as ' set mailfrom $1 set mailto
$2 set mailsubject $3 set mailmessage
$4 set myHost "localhost" set myPort
25 set mySock [socket $myHost $myPort] set
toemailaddress_start [string first "<" $mailto] if
{$toemailaddress_start != -1} { set toemailaddress_finish [string
first ">" $mailto] set toemailaddress_start [expr
$toemailaddress_start + 1] set toemailaddress_finish [expr
$toemailaddress_finish - 1] set toemailaddress [string range
$mailto $toemailaddress_start $toemailaddress_finish] } else
{ set toemailaddress $mailto } set
fromemailaddress_start [string first "<" $mailfrom] if
{$fromemailaddress_start != -1} { set fromemailaddress_finish
[string first ">" $mailfrom] set fromemailaddress_start [expr
$fromemailaddress_start + 1] set fromemailaddress_finish [expr
$fromemailaddress_finish - 1] set fromemailaddress [string range
$mailfrom $fromemailaddress_start $fromemailaddress_finish] } else
{ set fromemailaddress
$mailfrom } fileevent $mySock writable [list
svcHandler $mySock] fconfigure $mySock -buffering
none puts $mySock "helo 127.0.0.1" gets
$mySock name puts $mySock "mail from: $fromemailaddress" gets
$mySock name puts $mySock "rcpt to: $toemailaddress" gets
$mySock name puts $mySock "data" gets $mySock
name puts $mySock "To: $mailto" puts $mySock "From:
$mailfrom" puts $mySock "Subject: $mailsubject" puts $mySock
"" puts $mySock "$mailmessage" puts $mySock "." gets
$mySock name close $mySock return 1'language
'pltclu';
RegardsDinesh Pandey
Re: [SQL] send mail from Postgres using PLTCLU language.
After using
puts $mySock "rcpt cc: [EMAIL PROTECTED]"
I am able to send mail as Cc:
also.
Thanks
Dinesh Pandey
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dinesh
PandeySent: Friday, April 15, 2005 6:18 PMTo:
'PostgreSQL'Subject: [SQL] send mail from Postgres using PLTCLU
language.
How to send mail
from Postgres using PLTCLU language.
I am able to send
mail, but I want to know how to add "Cc" option also. (Program attached
below)
How to add
"Cc" (Right now containd "From", "To", "Subject" and
"Message").
CREATE or REPLACE FUNCTION pgmail(text,
text, text, text) returns int4 as ' set mailfrom $1 set mailto
$2 set mailsubject $3 set mailmessage
$4 set myHost "localhost" set myPort
25 set mySock [socket $myHost $myPort] set
toemailaddress_start [string first "<" $mailto] if
{$toemailaddress_start != -1} { set toemailaddress_finish [string
first ">" $mailto] set toemailaddress_start [expr
$toemailaddress_start + 1] set toemailaddress_finish [expr
$toemailaddress_finish - 1] set toemailaddress [string range
$mailto $toemailaddress_start $toemailaddress_finish] } else
{ set toemailaddress $mailto } set
fromemailaddress_start [string first "<" $mailfrom] if
{$fromemailaddress_start != -1} { set fromemailaddress_finish
[string first ">" $mailfrom] set fromemailaddress_start [expr
$fromemailaddress_start + 1] set fromemailaddress_finish [expr
$fromemailaddress_finish - 1] set fromemailaddress [string range
$mailfrom $fromemailaddress_start $fromemailaddress_finish] } else
{ set fromemailaddress
$mailfrom } fileevent $mySock writable [list
svcHandler $mySock] fconfigure $mySock -buffering
none puts $mySock "helo 127.0.0.1" gets
$mySock name puts $mySock "mail from: $fromemailaddress" gets
$mySock name puts $mySock "rcpt to: $toemailaddress" gets
$mySock name puts $mySock "data" gets $mySock
name puts $mySock "To: $mailto" puts $mySock "From:
$mailfrom" puts $mySock "Subject: $mailsubject" puts $mySock
"" puts $mySock "$mailmessage" puts $mySock "." gets
$mySock name close $mySock return 1'language
'pltclu';
RegardsDinesh Pandey
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
Hi, Andreas, Andreas Joseph Krogh schrieb: >>>So, what you're suggesting is that a restart of the webapp should make >>>vacuum able to delete those dead rows? >>Yes, but that'll only solve your problem for now. You'll have the >>problem again soon. What's keeping open the transaction? > Don't know... All my web-apps semm to have *one* line each in "ps" which says > "dbuser dbname 127.0.0.1 idle in transaction". Those are java-web-apps which > get their connections from a connection-pool(Apache-Commons ConnectionPool), > but there is exactly *one* line pr. web-app which has the "idle in > transaction" line, even tho they have many connections open each. > Any hints on how I can find out what's keeping the connection idle in a > transaction? I realize now that I should probably ask that question on the > pgsql-jdbc-list:-) Could you try the latest postgresql-8.0-311.jdbcX.jar? The current versions should solve the idle in transaction problem, the previous ones tended to issue BEGIN; just after every COMMIT, so there's always an open transaction. Markus signature.asc Description: OpenPGP digital signature
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
On Friday 15 April 2005 18:22, Markus Schaber wrote: > Hi, Andreas, > > Andreas Joseph Krogh schrieb: > >>>So, what you're suggesting is that a restart of the webapp should make > >>>vacuum able to delete those dead rows? > >> > >>Yes, but that'll only solve your problem for now. You'll have the > >>problem again soon. What's keeping open the transaction? > > > > Don't know... All my web-apps semm to have *one* line each in "ps" which > > says "dbuser dbname 127.0.0.1 idle in transaction". Those are > > java-web-apps which get their connections from a > > connection-pool(Apache-Commons ConnectionPool), but there is exactly > > *one* line pr. web-app which has the "idle in transaction" line, even tho > > they have many connections open each. Any hints on how I can find out > > what's keeping the connection idle in a transaction? I realize now that I > > should probably ask that question on the pgsql-jdbc-list:-) > > Could you try the latest postgresql-8.0-311.jdbcX.jar? The current > versions should solve the idle in transaction problem, the previous ones > tended to issue BEGIN; just after every COMMIT, so there's always an > open transaction. I could, but is it save agains a 7.4.5 version? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
Hi, Andrew, Andrew Sullivan schrieb: > Nope. That's a problem with your pool software. It's no doubt > issuing "BEGIN;" as soon as it connects. This problem may as well be caused by some versions of the postgresql jdbc driver, no need to blame the pool software. This is fixed with the current build 311 (and AFAIK with 310, too). signature.asc Description: OpenPGP digital signature
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
Hi, Andreas, Andreas Joseph Krogh schrieb: >>Could you try the latest postgresql-8.0-311.jdbcX.jar? The current >>versions should solve the idle in transaction problem, the previous ones >>tended to issue BEGIN; just after every COMMIT, so there's always an >>open transaction. > I could, but is it save agains a 7.4.5 version? Yes, it should be. I even tested 8.0-310 against a 7.2 server, and the other way round, without any problems. Markus signature.asc Description: OpenPGP digital signature
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
On Friday 15 April 2005 18:34, Markus Schaber wrote: > Hi, Andreas, > > Andreas Joseph Krogh schrieb: > >>Could you try the latest postgresql-8.0-311.jdbcX.jar? The current > >>versions should solve the idle in transaction problem, the previous ones > >>tended to issue BEGIN; just after every COMMIT, so there's always an > >>open transaction. > > > > I could, but is it save agains a 7.4.5 version? > > Yes, it should be. I even tested 8.0-310 against a 7.2 server, and the > other way round, without any problems. Great, I will, thanks! -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
