Skip site navigation (1) Skip section navigation (2)

Re: Functions with dynamic queries

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: Gabriel Dovalo Carril <dovalo(at)terra(dot)es>
Cc: Lista SQL de postgres <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Functions with dynamic queries
Date: 2002-05-24 13:51:06
Message-ID: 20020524135106.99232.qmail@web20802.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)terra(dot)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(at)postgresql(dot)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

In response to

Responses

pgsql-sql by date

Next:From: Doug FieldsDate: 2002-05-24 17:20:43
Subject: Efficiency question: VARCHAR with empty string vs NULL
Previous:From: Rajesh Kumar Mallah.Date: 2002-05-24 11:53:17
Subject: Re: Functions with dynamic queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group