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