[elein@varlena.com: Re: is General Bits Issue # 43 correct?]

----- Forwarded message from elein <elein@varlena.com> -----

Delivered-To: elein@varlena.com
Date: Sun, 14 Sep 2003 20:48:22 -0700
From: elein <elein@varlena.com>
To: Avi Schwartz <avi@CFFtechnologies.com>
Cc: elein <elein@varlena.com>
Subject: Re: [GENERAL] is General Bits Issue # 43 correct?
In-Reply-To: <FC8DEA7D-E72B-11D7-A5E9-000393AE5044@CFFtechnologies.com>
User-Agent: Mutt/1.3.22.1i

Yep, you are right.  Good catch.

Note, however, you have to do the extra quotes I was warning
about if the id is text:

for v_tmp in execute ''select min(price) as price_selected_min,
                      max(price) as price_selected_max
                      from table1
                      where id = '''''' || myid ||
                      '''''' and 1 = 1 '' loop

And thank you!  I will publish the correction before most
people read the article :-)

elein@varlena.com


On Sun, Sep 14, 2003 at 10:23:33PM -0500, Avi Schwartz wrote:
> In this issue the following statement is made:
>
> "In plpgsql, you cannot run a dynamic SELECT statement and be able to
> do anything with the selected data. You cannot assign the selected
> value to a variable or return the selected value from the function. You
> can, however, easily construct INSERT, UPDATE and DELETE statements as
> well a DDL (Data Definition Language: CREATE, ALTER, etc.)"
>
> This is not my experience.  For example, I am doing the following in my
> code and it works just fine:
>
> for v_tmp in execute ''select min(list_price) as price_selected_min,
> max(list_price) as price_selected_max
>     from table1
>     where xx_id = '' || p_xx_id ||
>     '' and date_deleted is null'' loop
>
>     v_price_selected_min := v_tmp.price_selected_min;
>     v_price_selected_max := v_tmp.price_selected_max;
>
> end loop;
>
> It works just fine for me.
>
> I also can return result set using
>
> for r in execute ''select statement'' loop
>            return next r;
> end loop;
>
> Avi
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

----- End forwarded message -----