pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with query
2+ messages / 2 participants
[nested] [flat]

* Problem with query
@ 2011-01-15 17:56 Barbara Woolums <barb.woolums@ymail.com>
  2011-01-17 23:42 ` Re: Problem with query Fernando Hevia <fhevia@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Barbara Woolums @ 2011-01-15 17:56 UTC (permalink / raw)
  To: pgsql-performance

Hi there,

Could someone please tell me why 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 recipe_ing where recipe in(1084, 1086, 1012, 618) and qtydec>0 and ing not 
in(select ing from excluded_ing where owner=1) 

order by aisle

the query returns the following with no values for aisle, but there should be 
some.

"c";"Pumpkin Seeds";""
"tb";"Horseradish";""
"c";"Puffed Quinoa";""
"c";"Honey";""
"c";"sesame seeds";""
"";"Red Onion";""
"ts";"Spicy Mustard";""
"c";"Dry Oatmeal";""
"c";"Ketchup";""
"ts";"Pepper";""
"tb";"Brown Sugar";""
"c";"Pecans";""
"ts";"Dijon Mustard";""
"single";"Cadbury Flake Bar";""
"g";"Caster Sugar";""
"g";"Low-fat Mozzarella Cheese";""
"md";"Onion";""
"sm";"Whole-wheat Pita";""
"medium";"Lemon";""
"c";"Raisins";""
"c";"Almonds";""
"c";"Dates";""
"g";"Ham";""
"lb";"Ground Sirloin";""
"c";"Shredded Coconut";""
"c";"Sunflower Seeds";""
"";"Tomato";""

The function used to extract aisle is

CREATE OR REPLACE FUNCTION get_ing_aisle(bigint, bigint)
  RETURNS character AS
'SELECT get_aisle(aisle) as aisle FROM ingredient_owner WHERE ingredient=$1 and 
owner=$2'
  LANGUAGE 'sql' VOLATILE
  COST 100;

 Cheers
Barb



      

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Problem with query
  2011-01-15 17:56 Problem with query Barbara Woolums <barb.woolums@ymail.com>
@ 2011-01-17 23:42 ` Fernando Hevia <fhevia@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Fernando Hevia @ 2011-01-17 23:42 UTC (permalink / raw)
  To: Barbara Woolums <barb.woolums@ymail.com>; +Cc: pgsql-performance

On Sat, Jan 15, 2011 at 14:56, Barbara Woolums <barb.woolums@ymail.com>wrote:

>  Hi there,
>
> Could someone please tell me why 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 recipe_ing where recipe in(1084, 1086, 1012, 618) and qtydec>0 and ing
> not in(select ing from excluded_ing where owner=1)
> order by aisle
>
>

You seem to be mixing up the parameters for get_ing_aisle.
Try *get_ing_aisle(ing,1) as aisle* instead

^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2011-01-17 23:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-15 17:56 Problem with query Barbara Woolums <barb.woolums@ymail.com>
2011-01-17 23:42 ` Fernando Hevia <fhevia@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox