Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1ED841337B7A for ; Sat, 15 Jan 2011 14:02:47 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 60927-01 for ; Sat, 15 Jan 2011 18:02:39 +0000 (UTC) X-Greylist: delayed 00:06:10.078113 by SQLgrey-1.7.6 Received: from nm12.bullet.mail.sp2.yahoo.com (nm12.bullet.mail.sp2.yahoo.com [98.139.91.82]) by mail.postgresql.org (Postfix) with SMTP id 7269D1337B58 for ; Sat, 15 Jan 2011 14:02:39 -0400 (AST) Received: from [98.139.91.63] by nm12.bullet.mail.sp2.yahoo.com with NNFMP; 15 Jan 2011 17:56:28 -0000 Received: from [98.139.91.18] by tm3.bullet.mail.sp2.yahoo.com with NNFMP; 15 Jan 2011 17:56:28 -0000 Received: from [127.0.0.1] by omp1018.mail.sp2.yahoo.com with NNFMP; 15 Jan 2011 17:56:28 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 284854.6173.bm@omp1018.mail.sp2.yahoo.com Received: (qmail 11637 invoked by uid 60001); 15 Jan 2011 17:56:28 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s1024; t=1295114187; bh=p0rFCERAiQWN16qnljWS2dWxQG7k2fxJ0JsWdilPW+s=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=DMasA9w3XtcWYUXqxENDCuvXSGvDxG2L8dU3N9l/znwXYz0T/jM2+qCNkHrY+by2NOSamF/5Ioe7coYFqtgx/r74RzZXlfmHKice8VqBjW3F0ZMKXkap2Ay8aY9xFP5KMdoPsxhwf7ji+ks5+8qX32lcfFSR+p4nqka3RlBEKNI= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=RU3PnyrnMcSxL9wBaYBj55Gy/WDwzGVOhTpNm4SGQxdALhOE7jDYJT6onI06kRE1GYoC2ftL9Fxb9bFXLy2NFWhUVF8US+5fV5gDPwGM20XI6czA31uiGlQmz+mAxY4tcy3dhyot/kANf/KWBLBDMR1hqa4WlHayu1gnF3a5ZG0=; Message-ID: <969645.11215.qm@web111812.mail.gq1.yahoo.com> X-YMail-OSG: k6O1cs4VM1kQeuzhsR6xenPzc58gPg8tN9gR1f509Pc7tBh Yv4TndwYKg4PPQH3soWGw_KobTT5XD42eJGrzTN.LSUhM.5X_tPIZU2AB5eA .PPa3mPd2ucGkZg7XnhPZRSFV9GpiqtfA81k9N1G.5HyxQfoR_yOAuAuklnE 6pA4DZuP3VjfAoDa6bNrBPQuMl.IKdDFfbUI2P7RxWaZQhLKecgHBwHoBxLe ULRht8m213NMn7ZqVRCKwXJ.zWi4aPY50h3jHiQ051fbLkRP_Tl8NltLkRFJ 2xkG38dLNY4g_N3Hd5wRQ8U6IMSLgvdNazYbOHYUKGNOFAXXqHw-- Received: from [79.155.118.166] by web111812.mail.gq1.yahoo.com via HTTP; Sat, 15 Jan 2011 09:56:27 PST X-Mailer: YahooMailRC/555 YahooMailWebService/0.8.107.285259 Date: Sat, 15 Jan 2011 09:56:27 -0800 (PST) From: Barbara Woolums Subject: Problem with query To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-865395331-1295114187=:11215" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.486 tagged_above=-10 required=5 tests=BAYES_05=-0.5, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RFC_ABUSE_POST=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01, WEIRD_QUOTING=0.001 X-Spam-Level: X-Archive-Number: 201101/118 X-Sequence-Number: 41905 --0-865395331-1295114187=:11215 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi there,=0A=0ACould someone please tell me why the following query won't w= ork=0A=0Aselect DISTINCT get_unit(unit) as unit, get_ingredient(ing) as ing= , =0Aget_ing_aisle(1,ing) as aisle =0A=0Afrom recipe_ing where recipe in(10= 84, 1086, 1012, 618) and qtydec>0 and ing not =0Ain(select ing from exclude= d_ing where owner=3D1) =0A=0Aorder by aisle=0A=0Athe query returns the foll= owing with no values for aisle, but there should be =0Asome.=0A=0A"c";"Pump= kin Seeds";""=0A"tb";"Horseradish";""=0A"c";"Puffed Quinoa";""=0A"c";"Honey= ";""=0A"c";"sesame seeds";""=0A"";"Red Onion";""=0A"ts";"Spicy Mustard";""= =0A"c";"Dry Oatmeal";""=0A"c";"Ketchup";""=0A"ts";"Pepper";""=0A"tb";"Brown= Sugar";""=0A"c";"Pecans";""=0A"ts";"Dijon Mustard";""=0A"single";"Cadbury = Flake Bar";""=0A"g";"Caster Sugar";""=0A"g";"Low-fat Mozzarella Cheese";""= =0A"md";"Onion";""=0A"sm";"Whole-wheat Pita";""=0A"medium";"Lemon";""=0A"c"= ;"Raisins";""=0A"c";"Almonds";""=0A"c";"Dates";""=0A"g";"Ham";""=0A"lb";"Gr= ound Sirloin";""=0A"c";"Shredded Coconut";""=0A"c";"Sunflower Seeds";""=0A"= ";"Tomato";""=0A=0AThe function used to extract aisle is=0A=0ACREATE OR REP= LACE FUNCTION get_ing_aisle(bigint, bigint)=0A RETURNS character AS=0A'SEL= ECT get_aisle(aisle) as aisle FROM ingredient_owner WHERE ingredient=3D$1 a= nd =0Aowner=3D$2'=0A LANGUAGE 'sql' VOLATILE=0A COST 100;=0A=0A Cheers=0A= Barb=0A=0A=0A=0A --0-865395331-1295114187=:11215 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi there,

Could someone please tell me w= hy the following query won't work

select DISTINCT get_unit(unit) as = unit, get_ingredient(ing) as ing, get_ing_aisle(1,ing) as aisle
from re= cipe_ing where recipe in(1084, 1086, 1012, 618) and qtydec>0 and ing not= in(select ing from excluded_ing where owner=3D1)
order by aisle
the query returns the following with no values for aisle, but there should= be some.

"c";"Pumpkin Seeds";""
"tb";"Horseradish";""
"c";"Pu= ffed Quinoa";""
"c";"Honey";""
"c";"sesame seeds";""
"";"Red Onion= ";""
"ts";"Spicy Mustard";""
"c";"Dry Oatmeal";""
"c";"Ketchup";""=
"ts";"Pepper";""
"tb";"Brown Sugar";""
"c";"Pecans";""
"ts";"D= ijon Mustard";""
"single";"Cadbury Flake Bar";""
"g";"Caster Sugar";""
"g";"Low-fat Mozzarella Cheese";""
"md";"Onion";""
"sm"= ;"Whole-wheat Pita";""
"medium";"Lemon";""
"c";"Raisins";""
"c";"A= lmonds";""
"c";"Dates";""
"g";"Ham";""
"lb";"Ground Sirloin";"""c";"Shredded Coconut";""
"c";"Sunflower Seeds";""
"";"Tomato";""
The function used to extract aisle is

CREATE OR REPLACE FUNCTIO= N get_ing_aisle(bigint, bigint)
  RETURNS character AS
'SELECT g= et_aisle(aisle) as aisle FROM ingredient_owner WHERE ingredient=3D$1 and ow= ner=3D$2'
  LANGUAGE 'sql' VOLATILE
  COST 100;
&nb= sp;
Cheers
Barb

=0A

=0A=0A=0A=0A --0-865395331-1295114187=:11215--