From: Gabriel Dovalo Carril <dovalo@terra.es>
To: Rajesh Kumar Mallah. <mallah@trade-india.com>
Cc: Lista SQL de postgres <pgsql-sql@postgresql.org>
Subject: Re: Functions with dynamic queries
Date: Thu, 23 May 2002 22:48:20 +0200
Message-ID: <3CED5594.85BF8596@terra.es> (raw)
References: <3CECC5C5.BDF77592@terra.es>
<200205231914.27923.mallah@trade-india.com>
> 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=#
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: dovalo@terra.es, mallah@trade-india.com
Subject: Re: Functions with dynamic queries
In-Reply-To: <3CED5594.85BF8596@terra.es>
* 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