Re: don't know how to get SELECT - Mailing list pgsql-general

Hi,

Thanks a lot, but I already done it. Also i need SELECT query
and not UPDATE. I know how to deal with UPDATE, but with
select you have to use FOR IN EXECUTE query
Something like this

FOR record | row IN EXECUTE text_expression LOOP
    statements
END LOOP;

Only that way you can use SELECT for now

From docs: "The results from SELECT queries are discarded by
EXECUTE, and SELECT INTO is not currently supported within EXECUTE.
So, the only way to extract a result from a dynamically-created
SELECT is to use the FOR-IN-EXECUTE form described later."

--
bye,
 Uros


Monday, May 13, 2002, 7:51:17 PM, you wrote:

DF> Use the EXECUTE command.

DF> This allows the execution of dynamic queries

DF> EXECUTE ''UPDATE ''||table||'' SET id = ''||id||''WHERE something =
DF> something'';

DF> This is just an example but it shows how to execute dynamic queries

DF> HTH

DF> Darren Ferguson

DF> On Mon, 13 May 2002, Uros Gruber wrote:

>> Hi!
>>
>> i have some problems with plpgsql. Here is my function
>>
>>
>> create or replace function rm_cat(varchar,int) returns boolean as '
>> declare
>>         table alias for $1;
>>         id    alias for $2;
>>         data   RECORD;
>> begin
>>       ******************************************************************
>>        SELECT INTO data lft,rgt FROM d_categories WHERE id_category=id;
>>        ******************************************************************
>>       IF NOT FOUND THEN
>>               RAISE EXCEPTION ''id % does not exist'',id;
>>               return 0;
>>       end if;
>>
>>       -- deleting the leftmost node does not lower lft for all
>>          execute ''update '' || quote_ident(table) || '' set level=level-1 where lft > ''  || data.lft || ''and rgt
<'' || data.rgt || '';''; 
>>          execute ''update '' || quote_ident(table) || '' set lft=lft-1 where lft >= '' || data.lft || '';'';
>>          execute ''update '' || quote_ident(table) || '' set lft=lft-1 where lft >= '' || data.rgt || '';'';
>>          execute ''update '' || quote_ident(table) || '' set rgt=rgt-1 where rgt >= '' || data.lft || '';'';
>>          execute ''update '' || quote_ident(table) || '' set rgt=rgt-1 where rgt >= '' || data.rgt || '';'';
>>          execute ''delete from '' || quote_ident(table) || '' where id_category = '' || id || '';'';
>>       return 1;
>> end;
>> 'language 'plpgsql';
>>
>>
>> My problems is in line wraped with ***. What i want is that
>> SELECT have to be dinamyc because i want with an argument to
>> saj for what table do this select. I tried many ways and i
>> think i don't understand something. Can somebody help me
>> solve this.
>>
>>
>>
>> --
>> bye,
>>  Uros                          mailto:uros.gruber@sir-mag.com
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>