Обсуждение: a query on stored procedures/functions in pgsql
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?
Many Thanks for your help in advance,
Neil
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
>
> Many Thanks for your help in advance,
> Neil
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
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