pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem 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