Re: Functions with dynamic queries

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(at)yahoo(dot)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(at)terra(dot)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(at)postgresql(dot)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