Message-ID: <3CED5594.85BF8596@terra.es>
Date: Thu, 23 May 2002 22:48:20 +0200
From: Gabriel Dovalo Carril <dovalo@terra.es>
MIME-Version: 1.0
To: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Cc: Lista SQL de postgres <pgsql-sql@postgresql.org>
Subject: Re: Functions with dynamic queries
References: <3CECC5C5.BDF77592@terra.es>
	<200205231914.27923.mallah@trade-india.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


> 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=#