From: | "Neil D'Souza" <neil(dot)xavier(dot)dsouza(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: a query on stored procedures/functions in pgsql |
Date: | 2010-10-21 04:48:39 |
Message-ID: | AANLkTikPKGpvvAs+c8iSdhJz0++37C-m8HNgiZOS3OAe@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> You have same plpgsql identifiers as sql identifiers, and because
> plpgsql identifiers has higher priority, your query is broken. For
> simple functions like this don't use a plpgsql language - use sql
> language instead.
>
Thank you for the quick reply. The example I constructed was
specifically for this post. I modified the function as below and it
works fine now. It would be great if the point you mentioned was a
note in the PGSQL Documentation (or did I miss it). In case I didnt
miss it, Is there anyone I have to write to, to help get this note in?
create or replace function food6(p1 int, p2 int)
returns table (
p_food_code int,
p_food_category varchar(20),
p_food_name varchar(20),
my_rank bigint
) as $$
begin
return query
select stage1.* from
(
select food_code, food_category, food_name, rank() over (order by
food_code) from food
) stage1;
--where rank >= 4 and rank <=8;
end
$$ language plpgsql;
nxd=> select * from food6(1,9);
p_food_code | p_food_category | p_food_name | my_rank
-------------+-----------------+-------------+---------
1 | fruit | tomato | 1
2 | fruit | banana | 2
3 | fruit | apple | 3
4 | vegetable | cabbage | 4
5 | vegetable | cauliflower | 5
6 | vegetable | okra | 6
7 | nuts | almonds | 7
8 | nuts | hazelnuts | 8
9 | nuts | pine-seeds | 9
(9 rows)
Many Thanks once again,
Kind Regards,
Neil
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2010-10-21 05:00:37 | Re: a query on stored procedures/functions in pgsql |
Previous Message | Tom Lane | 2010-10-21 04:47:39 | Re: pg_temp implicit search path: functions vs. tables |