Thread: Functions with dynamic queries
Hi, I would appreciate it if somebody could send me some examples (or url) on how to create functions with text parameters, use these parameters to build a dynamic query and return a value obtained from that query. A very simple case would be: Create table people (nif char(12),name varchar(40),primary (nif) ); And now to create a function to return "name" value giving "nif" values as a parameter. Thanks -- Gabriel D.
I guess the examples on standard docs explain it. dont they? > > A very simple case would be: > > Create table people ( > nif char(12), > name varchar(40), > primary (nif) > ); > > And now to create a function to return "name" value giving > "nif" values as a parameter. you may not need a function at all if your case is that simple . you can use subselects and || operators to do many complicated things.. can we know a bit more abt. your specific problem regds ,-- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
> you may not need a function at all if your case is that simple . you > can use subselects and || operators to do many complicated things.. > can we know a bit more abt. your specific problem Ok. I have this query: Select sum(stocks.stkreal)::text || ',' || sum(stocks.stkpteser)::text as stock From stocks, prendas Where prendas.codprenda = stocks.codprenda and prendas.codarticulo = '020720004'; And I want to create a function which receives "codarticulo" as a parameter. *---------------- Create Function calc_stocks(text) Returns text AS ' Declare codart ALIAS For $1; all_stocks record; BeginExecute '' Select sum(stocks.stkreal)::text || '' || '''''' ,'''''' || '' || '' || ''sum(stocks.stkpteser)::text asstock '' || ''into all_stocks '' || ''From stocks, prendas '' || ''Where prendas.codprenda = stocks.codprenda and '' ||''prendas.codarticulo = codart; '' Return all_stocks.stock; End; ' language 'plpgsql'; *----------------- gesyweb=# select calc_stocks('020220064'); ERROR: record all_stocks is unassigned yet gesyweb=#
Try this instead: Create Function calc_stocks(text) Returns text AS ' Declare codart ALIAS For $1; all_stocks record; stock text; Begin Select sum stocks.stkreal as stock1, sum stocks.stkpteser) as stock2 into all_stocks From stocks, prendas Where prendas.codprenda = stocks.codprenda and prendas.codarticulo = codart; stock := (all_stocks.stock1 || all_stocks.stock2)::text; Return stock; End; ' language 'plpgsql'; --- Gabriel Dovalo Carril <dovalo@terra.es> wrote: > > > > you may not need a function at all if your case is > that simple . you > > can use subselects and || operators to do many > complicated things.. > > can we know a bit more abt. your specific problem > > > Ok. I have this query: > > Select sum(stocks.stkreal)::text || ',' || > sum(stocks.stkpteser)::text as stock > From stocks, prendas > Where prendas.codprenda = stocks.codprenda and > prendas.codarticulo = '020720004'; > > And I want to create a function which receives > "codarticulo" as a parameter. > > *---------------- > Create Function calc_stocks(text) Returns text AS ' > Declare > codart ALIAS For $1; > all_stocks record; > Begin > Execute '' Select sum(stocks.stkreal)::text || '' > || '''''' ,'''''' > || '' || '' > || ''sum(stocks.stkpteser)::text as stock '' > || ''into all_stocks '' > || ''From stocks, prendas '' > || ''Where prendas.codprenda = stocks.codprenda > and '' > || ''prendas.codarticulo = codart; '' > Return all_stocks.stock; > End; > ' language 'plpgsql'; > *----------------- > gesyweb=# select calc_stocks('020220064'); > ERROR: record all_stocks is unassigned yet > gesyweb=# > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com
Reminder to self: cast to text _before_ concatenating(!) I won't retype the code, but I hope you get the idea... --- Jeff Eckermann <jeff_eckermann@yahoo.com> wrote: > Try this instead: > > Create Function calc_stocks(text) Returns text AS ' > Declare > codart ALIAS For $1; > all_stocks record; > stock text; > Begin > Select sum stocks.stkreal as stock1, > sum stocks.stkpteser) as stock2 > into all_stocks > From stocks, prendas > Where prendas.codprenda = stocks.codprenda and > prendas.codarticulo = codart; > stock := (all_stocks.stock1 || > all_stocks.stock2)::text; > Return stock; > End; > ' language 'plpgsql'; > > --- Gabriel Dovalo Carril <dovalo@terra.es> wrote: > > > > > > > you may not need a function at all if your case > is > > that simple . you > > > can use subselects and || operators to do many > > complicated things.. > > > can we know a bit more abt. your specific > problem > > > > > > Ok. I have this query: > > > > Select sum(stocks.stkreal)::text || ',' || > > sum(stocks.stkpteser)::text as stock > > From stocks, prendas > > Where prendas.codprenda = stocks.codprenda and > > prendas.codarticulo = '020720004'; > > > > And I want to create a function which receives > > "codarticulo" as a parameter. > > > > *---------------- > > Create Function calc_stocks(text) Returns text AS > ' > > Declare > > codart ALIAS For $1; > > all_stocks record; > > Begin > > Execute '' Select sum(stocks.stkreal)::text || '' > > || '''''' ,'''''' > > || '' || '' > > || ''sum(stocks.stkpteser)::text as stock '' > > || ''into all_stocks '' > > || ''From stocks, prendas '' > > || ''Where prendas.codprenda = stocks.codprenda > > and '' > > || ''prendas.codarticulo = codart; '' > > Return all_stocks.stock; > > End; > > ' language 'plpgsql'; > > *----------------- > > gesyweb=# select calc_stocks('020220064'); > > ERROR: record all_stocks is unassigned yet > > gesyweb=# > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the > > unregister command > > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > > > __________________________________________________ > Do You Yahoo!? > LAUNCH - Your Yahoo! Music Experience > http://launch.yahoo.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com
Jeff Eckermann escribió: > > Reminder to self: cast to text _before_ > concatenating(!) > I won't retype the code, but I hope you get the > idea... Yes, I have tried this: Create Function calc_stocks(text) Returns text AS ' Declare codart ALIAS For $1; all_stocks record; stock text; Begin Select sum(stocks.stkreal) as stock1, sum(stocks.stkpteser) as stock2 into all_stocksFrom stocks, prendasWhere prendas.codprenda= stocks.codprenda and prendas.codarticulo = codart; stock := (all_stocks.stock1::text || all_stocks.stock2::text); Return stock; End; ' language 'plpgsql'; But now I have got neither error nor result. gesyweb=# select calc_stocks('020220064');calc_stocks ------------- (1 row) -- Gabriel D.
"Rajesh Kumar Mallah." escribió: > > I guess the examples on standard docs explain it. > dont they? > Hi Rajesh, I have read docs examples again, and even examples at: http://www.brasileiro.net/postgres/plpgsql/plpgsql-description.html section 1.2.5.3, which explains how to execute dynamic queries. At the end of this section there is an example with a comment: -- This works because we are not substituting any variables -- Otherwise is would fail. And I need to know how can a query be executed substituting variables. Thanks, -- Gabriel D.
Hi Gabriel, I am not very adept in using PLPGSQL. Once I had tried to create a query dynamically and execute it but did not work out (but i do not exactly remeber the problem). but what i feel is using "EXECUTE" would be a overkill. I think Jeff has almost solved your problem and with your little coperation the problem can be sorted out. If you still really need the solution desperately i would apprecite if you could prepare an .sql file which creates , the table and inserts some data relevent to the SQL query into it , attach it and post to the list. Regds Mallah. > And I need to know how can a query be executed substituting > variables. > On Friday 24 May 2002 01:54 pm, Gabriel Dovalo Carril wrote: > "Rajesh Kumar Mallah." escribió: > > I guess the examples on standard docs explain it. > > dont they? > > Hi Rajesh, > > I have read docs examples again, and even examples at: > > http://www.brasileiro.net/postgres/plpgsql/plpgsql-description.html > > section 1.2.5.3, which explains how to execute dynamic queries. > At the end of this section there is an example with a comment: > > -- This works because we are not substituting any variables > -- Otherwise is would fail. > > And I need to know how can a query be executed substituting > variables. > > Thanks, -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Hmm. The fact that you don't get an error suggests that the function is running correctly, but not doing what we expect. What do you get when you just run the SQL from the command line? I am assuming that something is actually returned for both sums: if there are no values for either "stkreal" or "stkpteser" for "codarticulo = '020220064'", then a null will be returned, which will turn the final result into a null... If there are null values anywhere in those columns, then that could happen at any time. To guard against that, use something like: stock := coalesce(all_stocks.stock1::text, '') || coalesce(all_stocks.stock2::text, ''); --- Gabriel Dovalo Carril <dovalo@terra.es> wrote: > Jeff Eckermann escribi�: > > > > Reminder to self: cast to text _before_ > > concatenating(!) > > I won't retype the code, but I hope you get the > > idea... > > Yes, I have tried this: > > Create Function calc_stocks(text) Returns text AS ' > Declare > codart ALIAS For $1; > all_stocks record; > stock text; > Begin > Select sum(stocks.stkreal) as stock1, > sum(stocks.stkpteser) as stock2 > into all_stocks > From stocks, prendas > Where prendas.codprenda = stocks.codprenda and > prendas.codarticulo = codart; > stock := (all_stocks.stock1::text || > all_stocks.stock2::text); > Return stock; > End; > ' language 'plpgsql'; > > But now I have got neither error nor > result. > > gesyweb=# select calc_stocks('020220064'); > calc_stocks > ------------- > > (1 > row) > > > > -- > Gabriel D. > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly __________________________________________________ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com
> What do you get when you just run the SQL from the > command line? It works fine in command line . > I am assuming that something is actually returned for > both sums: if there are no values for either "stkreal" > or "stkpteser" for "codarticulo = '020220064'", then a > null will be returned, which will turn the final > result into a null... > If there are null values anywhere in those columns, No, there are no NULL values. Try attached scripts. They are only examples, (no real data) They are very, very simple and fails in the same situation. The only difference between then is line 19 (func_error.sql) Select people.age into person (func_nodata.sql) Select max(people.age) as age into person The first one returns: ERROR: record person is unassigned yet And the second one: get_age --------- (1 row) -- Gabriel Dovalo. Create table people ( nif char(12), age int4, primary key (nif) ); insert into people (nif, age) values ( '001', 10); insert into people (nif, age) values ( '002', 30); Create Function get_age(text) Returns text as ' Declare param_nif Alias For $1; person record; text_to_return text; Begin Select people.age into person From people Where people.nif = param_nif; text_to_return := person.age::text; Return text_to_return; End; ' language 'plpgsql'; Select get_age('001'); Create table people ( nif char(12), age int4, primary key (nif) ); insert into people (nif, age) values ( '001', 10); insert into people (nif, age) values ( '002', 30); Create Function get_age(text) Returns text as ' Declare param_nif Alias For $1; person record; text_to_return text; Begin Select max(people.age) as age into person From people Where people.nif = param_nif; text_to_return := person.age::text; Return text_to_return; End; ' language 'plpgsql'; Select get_age('001');
Hi Gabriel, sorry for the late response, I tried both the scripts and they do work. psql -h 192.168.0.11 -U tradein test -f b.sql DROP psql:b.sql:6: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'people_pkey' for table 'people' CREATE INSERT 27856939 1 INSERT 27856940 1 DROP CREATEget_age ---------10 (1 row) Hmm, am i missing anything ? mallah. Try attached scripts. They are only examples, (no real data)They are very, very simple and fails in the same situation. > The only difference between then is line 19 > > (func_error.sql) > Select people.age into person > > (func_nodata.sql) > Select max(people.age) as age into person > > > The first one returns: > ERROR: record person is unassigned yet > > And the second one: > get_age > --------- > > (1 > row) -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Rajesh, Thanks for telling me that I am _not_ going crazy. Because I could not see any reason why Gabriel's scripts should not work. Gabriel, I have two remaining suggestions: 1. Maybe you have stuff in your database that you do not expect. Perhaps try running this via a newly-created, empty database? 2. If that still doesn't work, you probably have a problem with your installation. If you post details of your OS platform, PostgreSQL version etc., some guru on the list may be able to suggest something. Sorry, that's the best I can think of. Jeff --- "Rajesh Kumar Mallah." <mallah@trade-india.com> wrote: > > Hi Gabriel, > > sorry for the late response, > > I tried both the scripts and they do work. > > psql -h 192.168.0.11 -U tradein test -f b.sql > DROP > psql:b.sql:6: NOTICE: CREATE TABLE / PRIMARY KEY > will create implicit index 'people_pkey' for table > 'people' > CREATE > INSERT 27856939 1 > INSERT 27856940 1 > DROP > CREATE > get_age > --------- > 10 > (1 row) > > Hmm, am i missing anything ? > > mallah. > > > > > > > > Try attached scripts. They are only examples, (no > real data) > They are very, very simple and fails in the same > situation. > > > The only difference between then is line 19 > > > > (func_error.sql) > > Select people.age into person > > > > (func_nodata.sql) > > Select max(people.age) as age into person > > > > > > The first one returns: > > ERROR: record person is unassigned yet > > > > And the second one: > > get_age > > --------- > > > > (1 > > row) > > -- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com
Jeff Eckermann escribió: > > Rajesh, > Thanks for telling me that I am _not_ going crazy. > Because I could not see any reason why Gabriel's > scripts should not work. > Gabriel, I have two remaining suggestions: > 1. Maybe you have stuff in your database that you do > not expect. Perhaps try running this via a > newly-created, empty database? > 2. If that still doesn't work, you probably have a > problem with your installation. Yes, I have made a new installation, and the query has worked. Thanks, -- Gabriel D.