Обсуждение: Problem with dynamic query

Поиск
Список
Период
Сортировка

Problem with dynamic query

От
"Kumar"
Дата:
Dear Friends,
I am using postgres 7.3.4 running on RH Linux 7.2. While writing dynamic SQL stmts, i have the following problem. Using cursor works fine.
 
OPEN ref FOR
 EXECUTE
 \'SELECT  comma(full_name) FROM (SELECT usr.full_name
 FROM  project_members,users usr 
 WHERE  project_members.project_role_id IN\'||\'(\'|| p_res_ids||\')
 AND  usr.usr_id = project_members.usr_id
 ORDER BY  usr.full_name)foo\';
 
 RETURN ref;
 
While I try to assign the value into a local variable v_names , it is not working

 EXECUTE
 \'SELECT  comma(full_name) INTO '||v_names||' FROM (SELECT usr.full_name
 FROM  project_members,users usr 
 WHERE  project_members.project_role_id IN\'||\'(\'|| p_res_ids||\')
 AND  usr.usr_id = project_members.usr_id
 ORDER BY  usr.full_name)foo\';
 
 RETURN v_names;
 
Why is it so. Cant I use 'INTO [variable_name]' inside a dynamic query. Please shed some light.
 
Regards
Kumar

Re: Problem with dynamic query

От
Tomasz Myrta
Дата:
Dnia 2003-12-05 13:43, Użytkownik Kumar napisał:

> Why is it so. Cant I use 'INTO [variable_name]' inside a dynamic query. 
> Please shed some light.

Well, you didn't read the manual, did you?

9.5.4. Executing dynamic queries
"the only way to extract a result from a dynamically-created SELECT is 
to use the FOR-IN-EXECUTE form"

Regards,
Tomasz Myrta