Re: Functions with dynamic queries
От | Jeff Eckermann |
---|---|
Тема | Re: Functions with dynamic queries |
Дата | |
Msg-id | 20020523224320.56005.qmail@web20809.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: Functions with dynamic queries (Jeff Eckermann <jeff_eckermann@yahoo.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: