Обсуждение: 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) >