Обсуждение: 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