Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 17A7547590F
	for <pgsql-sql@postgresql.org>; Thu, 23 May 2002 18:40:22 -0400 (EDT)
Received: from web20809.mail.yahoo.com (web20809.mail.yahoo.com
	[216.136.226.198])
	by postgresql.org (Postfix) with SMTP id 3FBD547546E
	for <pgsql-sql@postgresql.org>; Thu, 23 May 2002 18:40:20 -0400 (EDT)
Message-ID: <20020523224022.55237.qmail@web20809.mail.yahoo.com>
Received: from [208.13.35.95] by web20809.mail.yahoo.com via HTTP;
	Thu, 23 May 2002 15:40:22 PDT
Date: Thu, 23 May 2002 15:40:22 -0700 (PDT)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: Functions with dynamic queries
To: Gabriel Dovalo Carril <dovalo@terra.es>,
	"Rajesh Kumar Mallah." <mallah@trade-india.com>
Cc: Lista SQL de postgres <pgsql-sql@postgresql.org>
In-Reply-To: <3CED5594.85BF8596@terra.es>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200205/260
X-Sequence-Number: 7895

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