Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 2ABE9475DAE
	for <pgsql-sql@postgresql.org>; Thu, 23 May 2002 18:47:51 -0400 (EDT)
Received: from web20809.mail.yahoo.com (web20809.mail.yahoo.com
	[216.136.226.198])
	by postgresql.org (Postfix) with SMTP id EA4ED47611F
	for <pgsql-sql@postgresql.org>; Thu, 23 May 2002 18:43:17 -0400 (EDT)
Message-ID: <20020523224320.56005.qmail@web20809.mail.yahoo.com>
Received: from [208.13.35.95] by web20809.mail.yahoo.com via HTTP;
	Thu, 23 May 2002 15:43:20 PDT
Date: Thu, 23 May 2002 15:43:20 -0700 (PDT)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: Functions with dynamic queries
To: Jeff Eckermann <jeff_eckermann@yahoo.com>,
	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: <20020523224022.55237.qmail@web20809.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200205/261
X-Sequence-Number: 7896

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@yahoo.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@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
> 
> ---------------------------(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