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

----- Forwarded message from elein <elein(at)varlena(dot)com> -----

Delivered-To: elein(at)varlena(dot)com
Date: Sun, 14 Sep 2003 20:48:22 -0700
From: elein <elein(at)varlena(dot)com>
To: Avi Schwartz <avi(at)CFFtechnologies(dot)com>
Cc: elein <elein(at)varlena(dot)com>
Subject: Re: [GENERAL] is General Bits Issue # 43 correct?
In-Reply-To: <FC8DEA7D-E72B-11D7-A5E9-000393AE5044(at)CFFtechnologies(dot)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(at)varlena(dot)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 -----