[SQL] anal about my syntax

[James Cooper]

Hello All 
 
I was looking at this plpgsql 
function: 
FOR rec IN EXECUTE''select 
count(person_id) as total from person where person_email like '' || $1 || 
''% and person_id IN (select cp.person_id from cluster_person cp, 
cluster c where cp.cluster_id = c.cluster_id and c.c_id = '' || $2 || ''group by 
cp.person_id) ''LOOPtot = rec.total;END 
LOOP;
 
It works fine - I was just 
wondering if you can:
1. execute this sql without a loop 
being used as its not required.
2. could this be a straight SQL 
function instead( I dont think you can append strings together  in SQL 
functions )
 
 
 

[SQL] sql question after upgrade

[James Cooper]

Hi just upgraded to 7.3.2 and imported my db schema 
with no probs!
Just wondering... why VOLATILE is being appended to 
my functions - can anyone ansewer me what this means? - should i be using joins 
instead, does this make a difference? If so an example of this would be much 
appreciated!!
CREATE FUNCTION public.get_people_total(int4) RETURNS 
int8 AS 'select count(person_id) as total from person where person_id 
IN(select cp.person_id from cluster_person cp, cluster c where 
cp.cluster_id = c.cluster_id and c.c_id = $1 group by cp.person_id );' 
LANGUAGE 'sql' VOLATILE;
 
cheers all
James

[SQL] indexing

[James Cooper]

Hi all,
 
I've been doing a little reading on indexing in 
prelude to indexing my db.
I have the following to ask:
 
if I had three tables for a many to many 
relationship say A, B, AND C
B being the lookup. B being a huge 50k rows plus 
column and made just two forigen keys(b.a_id,b.c_id).
is it best to create two non-unique indexes or one 
unique index on both fields?
 
 
Please advise
 
Ps 
 
I also attempted creating an index on a table i 
have called person on person_id and 
ran 
Explain 
select person_id from person where person_id < n 

 
but saw no results of my created index being used - 
am i doing something incorrectly
 
Pps
When indexing if searching tables is more important 
than concurrency - which type of index is best?

[SQL] a change of query

[James Cooper]

select distinct cp.person_id from cluster_person cp, cluster c where cp.cluster_id = c.cluster_id and c.c_id = 1
can this query be changed to give just the row count?( of the 
distinct rows )
yes: i am aware of count() but this wont count the distinct rows 
- I can however achive this with an nested person_id in(select... etc 
but that slows things down big time
the above quey gives me my resultset asap - is there a way to 
get just the number of rows with the same speed
My thoughts are that I could just make a plpgsql  function 
with a GET Diagniostics ROW_COUNT - 
thoughts?
 
James