[SQL] I'm baffled
I have a couple of 'c' object files that send mail when ever there in an
insert or delete to specified tables.
The problem is that I get the mail message even if the insert fails!
what am I doing wrong?
thanks
The function;
create function insert_r1(text, text, text, text) returns int4
as '/var/pgsql/insert_r1.so' LANGUAGE 'c' ;
the rule:
create rule partmaster_I as
on insert to partmaster
do select insert_R1(new.afpn, 'partmaster(insert)','','') as answer;
the 'c' module:
# include "internal/postgres_fe.h"
# include
# include
int insert_r1(text *arg1, text *arg2, text *arg3, text *arg4)
{
char *table, *key, *mesg, *user;
char host[20];
FILE *fp;
arg1->vl_dat[arg1->vl_len - VARHDRSZ] = '\0';
arg2->vl_dat[arg2->vl_len - VARHDRSZ] = '\0';
arg3->vl_dat[arg3->vl_len - VARHDRSZ] = '\0';
arg4->vl_dat[arg4->vl_len - VARHDRSZ] = '\0';
key = strdup(arg1->vl_dat);
table = strdup(arg2->vl_dat);
mesg = strdup(arg3->vl_dat);
user = strdup(arg4->vl_dat);
gethostname(host, sizeof(host));
system(" cat /tmp/newMsg >> /tmp/newMsg.arc");
if ( (fp = fopen("/tmp/newMsg", "w+") ) == NULL)
return(-1);
fprintf(fp, "%s:: %s:(%s) Approval requested - %s %s\n",
host,table,key,mesg,user);
fclose (fp);
system("Mail -s \"Bmss \" [EMAIL PROTECTED] < /tmp/newMsg");
return(0);
}
---(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] trigger function building
What's the proper way to include the suport files for building 'C' trigger functions in to the normal PostgreSQL install? I.E. / "make install "does not include internal/postgres_fe.h, executor/spi.h nor commands/trigger.h in to /usr/local/pgsql/include. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] trigger data
Does any one have any reasonably simple application example that uses or parses the HeapTuple (PointerGetDatum(rettuple) ) returned by 'c' trigger function? Also, what is the syntax to parse the HeapTuple within the trigger function? thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] max value from join
I have a court program with related tables
citation
citkey varchar(16) NOT NULL PRIMARY KEY,
cdefendant
citkey varchar(16) NOT NULL PRIMARY KEY references citation,
ccourt
citkey varchar(16) NOT NULL PRIMARY KEY references citation,
disposition
citkeyvarchar(16) NOT NULL PRIMARY KEY references citation,
ccontinue
citkeyvarchar(16) NOT NULL references citation,
warrant
citkeyvarchar(16) NOT NULL references citation,
I am trying to extract data related to the last conttinue date using
select
c.citkey, /* c.cdate,
c.badge, c.vioDesc,
b.lname, b.fname,b.mi, b.race, b.dob, b.sex,
d.docket, d.plea, d.fine,d.costs, d.ddate, d.abdocket, d.bond,
p.disDate, p.disDesc, p.disCode, p.amount,
*/
t.contDate,
t.abcontinue,
w.bndType, w.bndAmt
from citation c, cdefendant b, ccourt d, ccontinue t,
disposition p, warrant w
where c.citkey = b.citkey and
b.citkey = d.citkey and
d.citkey = t.citkey and
t.citkey = p.citkey and
p.citkey = w.citkey
group by
c.citkey, c.cdate, c.badge, c.vioDesc,
b.lname, b.fname, b.mi, b.race, b.dob, b.sex,
d.docket, d.plea, d.fine, d.costs, d.ddate, d.abdocket, d.bond,
p.disDate, p.disDesc, p.disCode, p.amount,
t.abcontinue, t.contDate,
w.bndType, w.bndAmt
having max(t.contDate) = t.contDate
order by c.citkey
I cannot seem to get unique rows with only the max contDate??
i.e.
citkey | contdate | abcontinue | bndtype | bndamt
+-++-+-
991164031 | 06/07/2000 | 6 | Bond|0.00
991164031 | 07/19/2000 | 6 | Bond|0.00
thanks
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] trigger status
What's the easy way to tell if a trigger has fired??? thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] paging
I have a simple script to automate sql statements: q " select * from tablename" where q is an alias to echo "$@ ; " | psql imdb Turning on security breaks this script since is need s password. the new one works a before: q "select *..." passwd where q is now an expect script: #!/usr/bin/expect set query [lindex $argv 0] set query [lindex $argv 1] spawn psql imdb -U hook -n expect "Password: "; send "$passwd\n "; expect "imdb=# "; send "$query;\r"; expect "imdb=# "; send "\q;\r"; The problem is that long query returns invoke the paging option so you can scroll a page a time. Paging doesn't work properly within the script and even if it did , I would have to disable it in order to redirect the output to a file or a pipe. I can't find any info in the docs. Can some one point me in the right direction thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] (no subject)
When updating from 7.2.3 to 7.3.3 we have lost the ability to see the passwords in pg_shadow. Is there a way to decrypt the passwords? thanks -- A. R. Van Hook Honeywell Federal Manufacturing & Technologies IT System Engineer City of Lake Lotawana MO Mayor ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] now 7.2.3 - 7.3.3
this worked in 7.2.3 "select sum(cr) from ar where date(tdate) = now() -1 " but not in 7.3.3 What's the proper syntax for 7.3.3??? thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] (no subject)
We have been using 7.3.3 on several different applications and have discovered an anomaly. Backups are done nightly using "pg_dump --attribute-inserts -f att.dump.`dcode` spdb"; Dump files are used to restore and test. This test method has been used successfully on all previous versions. However, in 7.3.3, when the dump utility hits a carriage return imbeded with in a text field, the dump utility immeadiately jumps to the next record rendering the dump of successive records useless. i.e. INSERT INTO registry (rid, sid, aflag, fname, lname, mi, addr, city, state, zip, phone, email, weddate, regdate, items, \ notes, ref) VALUES (1148, 503, NULL, 'Jillian', 'Kooker', ' ', '', '', '', '', '610-649-3327', '', '2002-10-24', '2002-0\ 8-15', 'HP: 9120 Malis Henderson Veil: S2378 sparkle tulle and rat-tail edge', 'Used an 888 slip^M <<<<<< What am I doing wrong?? thanks -- A. R. Van Hook Honeywell Federal Manufacturing & Technologies IT System Engineer City of Lake Lotawana MO Mayor pager(816)458-2585 cell (816)564-0769 [EMAIL PROTECTED] (816)997-3531 [EMAIL PROTECTED] (816)578-4704 [EMAIL PROTECTED] (816)578-4215 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] max question
I have the following in a table: oid | citkey | contby | contdate | abcontinue | ccdate -++--+++ 5774835 | 04-0594703 | ||| 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 I am trying to pull rows that have the max. contdate. Why does the following give more than 2 rows? ql "select oid,* from ccontinue where citkey ='04-0594703' group by oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = contdate" oid | citkey | contby | contdate | abcontinue | ccdate -++--+++ 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 (3 rows) thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] sysid
It seems that in 8.1.0 we can no longer set the SYSID when adding users and groups. i.e template1=# create role hooker sysid 1345; NOTICE: SYSID can no longer be specified I have lots of code that depends on the actual group and user number. Is there a way to set the user and group number? thanks -- Arthur R. Van Hook Mayor The City of Lake Lotawana [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 578-4215 - City (816) 564-0769 - Cell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] new rule syntax?
I have two tables defined as: checks (ckidint NOT null PRIMARY KEY, payto text, notes text, ckdate date, printed int default 0, tdate timestamp not null) checkitems (itemint not null, ckidint NOT null references checks, itemtypeint not null, amt numeric(7,3), primary key (item, ckid)) in previous versions (<8.1) the following rule declaration seemed to work fine create rule checks_d0 as on delete to checks do delete from checkitems where ckid = checks.ckid; in 8.1.2 I get ERROR: missing FROM-clause entry from table "checks" any idea? -- Arthur R. Van Hook Mayor The City of Lake Lotawana [EMAIL PROTECTED] (816) 578-4704 - Home (816) 578-4215 - City (816) 564-0769 - Cell ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] date array
How do you add null values to a date array?
update dattable set insDate = '{04/12/2006,'null'}<-- doesn't work
update dattable set insDate= '{04/12/2006,null}' <-- doesn't work
update dattable set insDate= '{04/12/2006,''}' <-- doesn't work
the table field is defined as insDate date[]
thanks
--
Arthur R. Van Hook
Mayor
The City of Lake Lotawana
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 578-4215 - City
(816) 564-0769 - Cell
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] date array
While down loading 8.1.3, I tried the following:
update pf_inspectionsBld set insdate[0] = current_date
update pf_inspectionsBld set insdate[1] = current_date
update pf_inspectionsBld set insdate[2] = current_date
update pf_inspectionsBld set insdate[3] = current_date
update pf_inspectionsBld set insdate[4] = current_date + 1
select * from pf_inspectionsBld
A-2 |1793 |
[0:4]={04/24/2006,04/24/2006,04/24/2006,04/24/2006,04/25/2006}
yet if I try
update pr_inspectionsbld set inspdate[3] = null
then
select * from pf_inspectionsBld
A-2 |1793 |
[0:4]={04/24/2006,04/24/2006,04/24/2006,04/24/2006,04/25/2006}
i.e. no error and no change
what am I missing??
thanks
Bruno Wolff III wrote:
Please keep responses copied to the list. This both helps other people with
similar questions by getting the discussion in the archives and helps you
by letting other people see your additional questions.
On Sun, Apr 23, 2006 at 13:21:49 -0500,
"A. R. Van Hook" <[EMAIL PROTECTED]> wrote:
I am running 8.1.2, what can I do in the mean time?
You could build from source using HEAD. (This is probably not a good idea for
a production system.)
You could restructure your data model to not use arrays. (You probably
shouldn't be using them anyway. Arrays should be used for things that are
naturally arrays (e.g. vectors) and I would be surprised if this was the
case when you are storing dates.)
Also note that 8.1.3 is out and 8.1.4 will probably be out soon, so you
should be looking to upgrade in any case.
thanks
Bruno Wolff III wrote:
On Sun, Apr 23, 2006 at 07:55:54 -0500,
"A. R. Van Hook" <[EMAIL PROTECTED]> wrote:
How do you add null values to a date array?
update dattable set insDate= '{04/12/2006,null}' <-- doesn't work
That is the correct format. However, I think that only works in HEAD
(what will become 8.2 in about 6 months).
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Arthur R. Van Hook
Mayor
The City of Lake Lotawana
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 578-4215 - City
(816) 564-0769 - Cell
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Time interval sums
How does one sum interval sums?
the following does not work:
select sum(stop-start::interval) as total from startstop
where sid= 545 and
2006 = date_part('year', day) and
9 = date_part('month', day)
however select stop-start::interval) as total from startstop
where sid= 545 and
2006 = date_part('year', day) and
9 = date_part('month', day)
works great but I need an overall sum
Table schema:
Table "public.startstop"
Column | Type | Modifiers
++---
sid| integer| not null
start | time without time zone |
stop | time without time zone |
day| date | not null
thanks
--
Arthur R. Van Hook
Mayor
The City of Lake Lotawana
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 578-4215 - City
(816) 564-0769 - Cell
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] or function
I have been trying to do an 'or' function such that if a field value is zero then use 1 as a multiplier: "select sum((1 | i.count) * s.cost) as COST seems to work ok when the value is 0 but I get the wrong value is i.count in not zero stid | count | tot | ldate| pkcnt | status | cost --+---+-++---++-- 2995 |12 | 44 | 12/18/2006 |32 | Active | 3.60 qs "select sum(i.count * s.cost) from inventory i,stock s, stockkey k where i.stid=s.stid and i.status='Active' and s.ctype = k.cd and k.value = 'Other' and s.ssp = 'Stock'" sum --- 43.20 qs "select sum((1|i.count) * s.cost) from inventory i,stock s, stockkey k where i.stid=s.stid and i.status='Active' and s.ctype = k.cd and k.value = 'Other' and s.ssp = 'Stock'" sum --- 46.80 43.20 <> 46.80 Thanks -- Arthur R. Van Hook Mayor The City of Lake Lotawana [EMAIL PROTECTED] (816) 578-4704 - Home (816) 578-4215 - City (816) 564-0769 - Cell ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] join problem
I have join problem: "select i.ivid, v.eventdate, v.deposit, v.invdate, cai.db, sum(i.tax) as tax, sum(i.tax + i.rowtot) as totalP, (sum(i.tax + i.rowtot) - v.deposit) as balance from invoice v left outer join invoiceitems i on v.ivid = i.ivid where v.cusid = $cusid and v.cusid = cai.cusid group by i.ivid, v.eventdate, v.deposit, v.invdate, cai.db ERROR: missing FROM-clause entry for table "cai" If I add cai to the from clause "from invoice v, cai, I get ERROR: missing FROM-clause entry for table "cai" ERROR: invalid reference to FROM-clause entry for table "v" Where do I add the cai table reference??? thanks -- Arthur R. Van Hook Mayor - Retired The City of Lake Lotawana [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 564-0769 - Cell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] join problem
I have three tables relating to purchases invoice - transaction data (customer id, deposit. etc) invoiceitems - purachace items detail cai - customer data if I query for the total charges using select sum(rowtot + tax) from invoiceitems where ivid in (select ivid from invoice where cusid = 2128)" I get 1179.24 which is correct. if I query for the total deposit using select sum(deposit) from invoice where cusid = 2128" I also get 1179.24, also the correct amount If I try an inclusive query using the following: select sum(i.rowtot + i.tax) as tot, sum(v.deposit) as deposit from cai c join invoice v on (v.cusid = c.cusid) left join invoiceitems i on (v.ivid = i.ivid) where v.cusid = 2128 group by c.cusid I get tot| deposit --+- 1179.240 | 2819.24 Can someone correct the query? thanks tables definations are as follows: invoice (ividint NOT NULL PRIMARY KEY, rid int null references registry, sid int not null references staffname, cusid int, invdate date, ifname varchar(16), imi char, ilname varchar(16), addrtext, cityvarchar(16), state varchar(2), zip varchar(16), iphone varchar(16), eventdate date, paytype int, bustvarchar(16), height varchar(16), dressizevarchar(16), waist varchar(16), hipsvarchar(16), hollow varchar(16), deposit numeric(6,2), transtype int, notes text, neckvarchar(16), arm_length varchar(16), leg_length varchar(16), coatvarchar(16), shoevarchar(16), tux intdefault 0 invoiceItems (itemint NOT NULL, ividint NOT NULL references invoice ON DELETE CASCADE, qty int, stidint references stock, /*tag*/ descripttext, price numeric(6,2), tax numeric(7,3), discountnumeric(6,2), rowtot numeric(7,3), pickup int default 0, /* SO or to be picked up = 1 */ primary key(item, ivid) create table cai/* customer account information*/ (cusidint NOT null primary key, cfname varchar(16), cmi char default '', clname varchar(16), caddrtext, ccityvarchar(16), cstate varchar(2), czip varchar(16), cphone varchar(16), db numeric(7,2), tcodeint not null default 0, acodeint not null default 0, tdatetimestamp not null [EMAIL PROTECTED] ~]$ -- Arthur R. Van Hook Mayor - Retired The City of Lake Lotawana [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home ---(end of broadcast)--- TIP 1: 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] join problem
What is the correct query??? thanks Ragnar wrote: On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote: if I query for the total deposit using select sum(deposit) from invoice where cusid = 2128" I also get 1179.24, also the correct amount If I try an inclusive query using the following: select sum(i.rowtot + i.tax) as tot, sum(v.deposit) as deposit from cai c join invoice v on (v.cusid = c.cusid) left join invoiceitems i on (v.ivid = i.ivid) where v.cusid = 2128 group by c.cusid I get tot| deposit --+- 1179.240 | 2819.24 you are adding the invoice deposit once for each item gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Arthur R. Van Hook Mayor - Retired The City of Lake Lotawana [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 564-0769 - Cell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] join problem
Works great. Can you enlighten me as why the deposit is divided by the number of rows? thanks Ragnar wrote: On lau, 2007-06-23 at 04:15 -0500, A. R. Van Hook wrote: Ragnar wrote: On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote: If I try an inclusive query using the following: select sum(i.rowtot + i.tax) as tot, sum(v.deposit) as deposit from cai c join invoice v on (v.cusid = c.cusid) left join invoiceitems i on (v.ivid = i.ivid) where v.cusid = 2128 group by c.cusid I get tot| deposit --+- 1179.240 | 2819.24 you are adding the invoice deposit once for each item What is the correct query??? sum each invoice separately, and then group the sums by cusid. for example: select vcusid as cusid, sum(vtot) as tot, sum(vdeposit) as deposit from ( select v.cusid as vcusid, v.ivid as vivid, sum(i.rowtot + i.tax) as vtot, sum(v.deposit)/count(*) as vdeposit from invoice as v left join invoiceitems as i on (v.ivid = i.ivid) group by v.cusid, v.ivid ) as vsums where vsums.vcusid=2128 group by vsums.vcusid hope this helps gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Arthur R. Van Hook Mayor - Retired The City of Lake Lotawana [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 564-0769 - Cell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] wrong answer
I have a simple (and incorrect queuer) that gives the wrong answer. Can someone show me the correct syntax? t "select sum(s.ref), r.value, s.ref from registry as s left join referralkey as r on (s.ref = r.cd) group by s.ref, r.value order by r.value, s.ref" sum | value | ref --+-+- 3462 | A Friend| 3 150 | Bridal Guide| 6 33 | Coupon | 11 yet qs "select count(*) from registry where ref =3" count = 1154 qs "select count(*) from registry where ref=6" count = 25 qs "select count(*) from registry where ref=11" count = 3 thanks -- Arthur R. Van Hook [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 629-0071 Cell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Insert problem
I have a table where some of the entries of should have
been posted to the wrong cusid and item#:
cusid | item# |
1747 | 149 | 9251 : Jonathon Krinke ...
1747 | 150 | 10071 : Lindsey Steele
1747 | 151 | 10187 : Lindsay Stapley ...
1747 | 152 | 10188 : Amanda Goucher
1888 |0 | 10071 : PROM 75P1206 mi.
corrected:
1747 | 148 | 9250 : Jonathon Krinke
1747 | 149 | 9251 : Jonathon Krinke ...
1888 |0 | 10071 : PROM 75P1206 mi..
1888 |1 | 10071 : Lindsey Steele
1888 |2 | 10187 : Lindsay Stapley
1888 |3 | 10188 : Amanda Goucher
my correction script:
The script worked fine untill I tried in on the following entries:
1841 |2 | 9228 : Caty Case : SO:Bra:.
1841 |3 | 9621 : Kelsie Greenlee : PROM.
2072 |3 | null : Cookie Jared Cook :..
2072 |4 | null : Cookie Jared Cook :
2072 |5 | null : Cookie Jared Cook :
insert into schItem (scid, value, iflag, outalts, sidate, istid)
select 2072, i.value, i.iflag, i.outalts, i.sidate, i.istid
from schItem i, schItem s
where i.scid=1841 and i.item >= 3
and s.scid = 2072group by i.value,
i.iflag, i.outalts, i.sidate, i.istid, s.scid
ERROR: duplicate key violates unique constraint "schitem_pkey"
It seems I have something wrong with the select portion of the script
but I can figure it out.
Any idea??
thanks
ps
table defination
create sequence schItem_item_seq
create table schItem
(scid int NOT NULL references schedule ON DELETE CASCADE,
item int NOT NULL default nextval('schItem_item_seq'),
valuetext,
iflagint,
outalts int,
sidate date,
istidint references stock,
primary key (scid, item))
--
Arthur R. Van Hook
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 629-0071 Cell
--
Arthur R. Van Hook
[EMAIL PROTECTED]
[EMAIL PROTECTED]
(816) 578-4704 - Home
(816) 629-0071 Cell
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
