Message-ID: <20020524135106.99232.qmail@web20802.mail.yahoo.com>
Date: Fri, 24 May 2002 06:51:06 -0700 (PDT)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: Functions with dynamic queries
To: Gabriel Dovalo Carril <dovalo@terra.es>
Cc: Lista SQL de postgres <pgsql-sql@postgresql.org>
In-Reply-To: <3CED7D29.7E55D278@terra.es>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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