From: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: 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 15:40:22 -0700 (PDT)
Message-ID: <20020523224022.55237.qmail@web20809.mail.yahoo.com> (raw)
In-Reply-To: <3CED5594.85BF8596@terra.es>
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
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, mallah@trade-india.com
Subject: Re: Functions with dynamic queries
In-Reply-To: <20020523224022.55237.qmail@web20809.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