Re: a query on stored procedures/functions in pgsql

>
> 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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>