Re: a query on stored procedures/functions in pgsql

Pavel Stehule <pavel.stehule@xxxxxxxxx> · Thu, 21 Oct 2010 06:28:10 +0200

Hello

2010/10/21 Neil D'Souza <neil.xavier.dsouza@xxxxxxxxx>:
> consider the following sql statements:
>
> create table food(
> ÂÂÂ food_code serial unique,
> ÂÂÂ food_category varchar(20),
> ÂÂÂ food_name varchar(20)
> ÂÂÂ );
>
> insert into food (food_category, food_name) values ('fruit', 'tomato');
> insert into food (food_category, food_name) values ('fruit', 'banana');
> insert into food (food_category, food_name) values ('fruit', 'apple');
>
> insert into food (food_category, food_name) values ('vegetable', 'cabbage');
> insert into food (food_category, food_name) values ('vegetable', 'cauliflower');
> insert into food (food_category, food_name) values ('vegetable', 'okra');
>
> insert into food (food_category, food_name) values ('nuts', 'almonds');
> insert into food (food_category, food_name) values ('nuts', 'hazelnuts');
> insert into food (food_category, food_name) values ('nuts', 'pine-seeds');
>
> I tried the following queries - the output is listed below:
> select food_category, food_name, rank as my_rank from
> ÂÂÂ (
> ÂÂÂ Âselect food_category, food_name, rank()Â over (order by
> food_category, food_name) from food
> ÂÂÂ ) stage1
> where rank >= 4 and rank <=8;
> output
> ---------
> food_category |Â food_nameÂ | my_rank
> ---------------+-------------+---------
> ÂnutsÂÂÂÂÂÂÂÂÂ | almondsÂÂÂÂ |ÂÂÂÂÂÂ 4
> ÂnutsÂÂÂÂÂÂÂÂÂ | hazelnutsÂÂ |ÂÂÂÂÂÂ 5
> ÂnutsÂÂÂÂÂÂÂÂÂ | pine-seedsÂ |ÂÂÂÂÂÂ 6
> ÂvegetableÂÂÂÂ | cabbageÂÂÂÂ |ÂÂÂÂÂÂ 7
> ÂvegetableÂÂÂÂ | cauliflower |ÂÂÂÂÂÂ 8
>
> select food_category, food_name, my_rank from
> ÂÂÂ (
> ÂÂÂ Âselect food_category, food_name, rank()Â over (order by
> food_category, food_name)asÂ my_rank from food
> ÂÂÂ ) stage1
> where my_rank >= 4 and my_rank <=8;
>
> output
> ---------
> Âfood_category |Â food_nameÂ | my_rank
> ---------------+-------------+---------
> ÂnutsÂÂÂÂÂÂÂÂÂ | almondsÂÂÂÂ |ÂÂÂÂÂÂ 4
> ÂnutsÂÂÂÂÂÂÂÂÂ | hazelnutsÂÂ |ÂÂÂÂÂÂ 5
> ÂnutsÂÂÂÂÂÂÂÂÂ | pine-seedsÂ |ÂÂÂÂÂÂ 6
> ÂvegetableÂÂÂÂ | cabbageÂÂÂÂ |ÂÂÂÂÂÂ 7
> ÂvegetableÂÂÂÂ | cauliflower |ÂÂÂÂÂÂ 8
>
>
> Consider what happens when I try to make a simple variant of the 2nd
> query into a stored procedure
>
> create or replace function food4(p1 int, p2 int)
> returns table (
> food_code int,
> food_category varchar(20),
> 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) as my_rank from food
> ÂÂÂ ) stage1;
> ÂÂÂ --where rank >= 4 and rank <=8;
> end
> $$ language plpgsql;
>
> nxd=> \i my_rank_sp4.sql
> psql:my_rank_sp4.sql:16: ERROR:Â syntax error at or near "$4"
> LINE 1: ... $1 ,Â $2 ,Â $3 , rank() over (order byÂ $1 ) asÂ $4Â from f...
> ÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂ ^
> ÂÂÂ The stored procedure does not allow me to rename the variable to
> the name I need in the output table.
>
> ÂÂÂ I went to the plpgsql documentation of the user manual - Chapter
> 38 - section 38.3.1 . There you have the "extended_sales" function
> which also returns a table (what I needed), and there the table has a
> parameter called total which is computed - the multiplication of
> "quantity * price" is not renamed to "total" which is in the output
> table, rather "quantity*price" is in the same position (2nd position)
> in the select query that "total" occupies in the output table. Hence I
> decided not to rename the ranked field - stored procedure query given
> below.
>
> create or replace function food5(p1 int, p2 int)
> returns table (
> food_code int,
> food_category varchar(20),
> 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;
>
> and this works - However when I run the function this is what i get
> nxd=> \i my_rank_sp5.sql
> CREATE FUNCTION
> nxd=> select * from food5(1,9);
> Âfood_code | food_category | food_name | my_rank
> -----------+---------------+-----------+---------
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
> ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂÂÂÂÂ |ÂÂÂÂÂÂ 1
>
> The values are blank as you can see above
> If, I run a plain query like this - which is just text from the stored
> procedure,
> but not embedded in a plpgsql function - the result is fine
> nxd=> select stage1.* from
> nxd-> (
> nxd(>Â select food_code, food_category, food_name, rank()Â over (order
> by food_code) from food
> nxd(> ) stage1;
> Âfood_code | food_category |Â food_nameÂ | 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
>
> Can someone please tell me what I am doing wrong?

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.

create or replace function food5(p1 int, p2 int)
returns table (
food_code int,
food_category varchar(20),
food_name varchar(20),
my_rank bigint
) as $$
begin
    select stage1.* from
    (
     select food_code, food_category, food_name, rank()  over (order
by food_code) from food
    ) stage1;
  end
$$ language sql;

regards

Pavel Stehule

>
> Many Thanks for your help in advance,
> Neil
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general