From: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: Gabriel Dovalo Carril <dovalo@terra.es>
Cc: Lista SQL de postgres <pgsql-sql@postgresql.org>
Subject: Re: Functions with dynamic queries
Date: Fri, 24 May 2002 06:51:06 -0700 (PDT)
Message-ID: <20020524135106.99232.qmail@web20802.mail.yahoo.com> (raw)
In-Reply-To: <3CED7D29.7E55D278@terra.es>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeff_eckermann@yahoo.com, dovalo@terra.es
Subject: Re: Functions with dynamic queries
In-Reply-To: <20020524135106.99232.qmail@web20802.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox