From: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: 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:43:20 -0700 (PDT)
Message-ID: <20020523224320.56005.qmail@web20809.mail.yahoo.com> (raw)
In-Reply-To: <20020523224022.55237.qmail@web20809.mail.yahoo.com>
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
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: <20020523224320.56005.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