[SQL] 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: [SQL] 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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

[SQL] Is it possible to set a NOT NULL constraint deferrable?

[Olivier Hubaut]

I can put all the other constaints deferrable, but the *NOT NULL* one 
seems to be undeferrable.

Is ther a way to by-pass this or is do you know if this is planned in 
the future versions?

--
Ci-git une signature avortee.
** RIP **
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Index not recognized

[Grace C. Unson]

Hello.

Why is it that my index for text[] data type is not recognized by the
Planner?

I did these steps:

1. create function textarr(text[]) returns text language sql as 'select
$1[1]' strict immutable
2. create index org_idx on EmpData (textarr(org));

3. vacuum full
4. explain analyze select name from EmpData where org *= 'math';

Result:
=
Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488)
(actual time=3.71.35..371.35 rows=0 loops=1)

Filter: (org[0]='math'::text)

Total runtime: 371.47 msec
(3 rows)

==

I have 20,496 records. My postgres version is 7.3.

Any help is greatly appreciated.

Thank you..

Ace


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match