Обсуждение: don't know how to get SELECT
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
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)
>>
Use the EXECUTE command. This allows the execution of dynamic queries EXECUTE ''UPDATE ''||table||'' SET id = ''||id||''WHERE something = something''; This is just an example but it shows how to execute dynamic queries HTH Darren Ferguson 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) >