X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 26E2BD1B482 for ; Fri, 5 Dec 2003 12:46:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44195-05 for ; Fri, 5 Dec 2003 08:46:26 -0400 (AST) Received: from smtp106.mail.sc5.yahoo.com (smtp106.mail.sc5.yahoo.com [66.163.169.226]) by svr1.postgresql.org (Postfix) with SMTP id 8202ED1B48B for ; Fri, 5 Dec 2003 08:46:22 -0400 (AST) Received: from unknown (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp106.mail.sc5.yahoo.com with SMTP; 5 Dec 2003 12:46:23 -0000 Message-ID: <01c301c3bb2d$76596b80$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "psql" Subject: Problem with dynamic query Date: Fri, 5 Dec 2003 18:13:50 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01C0_01C3BB5B.88D9D0A0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_30_40 X-Spam-Level: X-Archive-Number: 200312/48 X-Sequence-Number: 16214 This is a multi-part message in MIME format. ------=_NextPart_000_01C0_01C3BB5B.88D9D0A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear Friends, I am using postgres 7.3.4 running on RH Linux 7.2. While writing dynamic SQ= L stmts, i have the following problem. Using cursor works fine. OPEN ref FOR=20 EXECUTE \'SELECT comma(full_name) FROM (SELECT usr.full_name FROM project_members,users usr=20=20 WHERE project_members.project_role_id IN\'||\'(\'|| p_res_ids||\') AND usr.usr_id =3D 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 w= orking EXECUTE \'SELECT comma(full_name) INTO '||v_names||' FROM (SELECT usr.full_name FROM project_members,users usr=20=20 WHERE project_members.project_role_id IN\'||\'(\'|| p_res_ids||\') AND usr.usr_id =3D 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. Ple= ase shed some light. Regards Kumar ------=_NextPart_000_01C0_01C3BB5B.88D9D0A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Dear Friends,

I am using postgres 7.3.4 running on RH Linux 7.2. Whil= e=20 writing dynamic SQL stmts, i have the following problem. Using cursor works= =20 fine.

OPEN ref FOR=20
EXECUTE
\'SELECT  comma(full_name) FROM (SELEC= T=20 usr.full_name
FROM  project_members,users usr =20
WHERE  project_members.project_role_id IN\'||\'(\'||=20 p_res_ids||\')
AND  usr.usr_id =3D=20 project_members.usr_id
ORDER BY usr.full_name)foo\';<= /DIV>

RETURN ref;

While I try to assign the value into a local variable v= _names=20 , it is not working

EXECUTE
\'SELECT  comma(full_name) IN= TO=20 '||v_names||' FROM (SELECT=20 usr.full_name
FROM  project_members,users usr =20
WHERE  project_members.project_role_id IN\'||\'(\'||=20 p_res_ids||\')
AND  usr.usr_id =3D=20 project_members.usr_id
ORDER BY usr.full_name)foo\';<= /DIV>

RETURN v_names;

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

Regards

Kumar

------=_NextPart_000_01C0_01C3BB5B.88D9D0A0--