a query on stored procedures/functions in pgsql

Hello

2010/10/21 Neil D'Souza <neil.xavier.dsouza@gmail.com>:

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

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

Hello

2010/10/21 Neil D'Souza <neil.xavier.dsouza@gmail.com>:

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?

yes, it's probably undocumented :(. see - unofficial plpgsql
documentation http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29#Recommendation_for_design_of_saved_procedures_in_PL.2FpqSQL_language

but it is solved on 9.0, where you will got adequate error message.

Regards

Pavel Stehule

"Neil D'Souza" <neil.xavier.dsouza@gmail.com> writes:

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).

As of 9.0, plpgsql's default behavior is to throw an error when there's
an ambiguity of this sort.

regards, tom lane

On Wednesday 20 October 2010 9:48:39 pm Neil D'Souza wrote:

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?

For the record it is in the docs twice:

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
"Caution

PL/pgSQL will substitute for any identifier matching one of the function's
declared variables; it is not bright enough to know whether that's what you
meant! Thus, it is a bad idea to use a variable name that is the same as any
table, column, or function name that you need to reference in commands within
the function. For more discussion see Section 38.10.1. "

http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

"The substitution mechanism will replace any token that matches a known
variable's name. This poses various traps for the unwary. For example, it is a
bad idea to use a variable name that is the same as any table or column name
that you need to reference in queries within the function, because what you
think is a table or column name will still get replaced. In the above example,
suppose that logtable has column names logtxt and logtime, and we try to write
the INSERT as...

"

--
Adrian Klaver
adrian.klaver@gmail.com