On 02/08/11 18:22, Jan Wielgus wrote:
select participant.participant_id from participant participant
join person person on person.person_participant_id = participant.participant_id
left join registration registration on registration.registration_registered_participant_id = participant.participant_id
left join enrollment enrollment on registration.registration_enrollment_id = enrollment.enrollment_id
join registration_configuration registration_configuration on enrollment.enrollment_configuration_id = registration_configuration.configuration_id
left join event_context context on context.context_id = registration_configuration.configuration_context_id
where participant.participant_type = 'PERSON'
and participant_status = 'ACTIVE'
and context.context_code in ('GB2TST2010A')
and registration_configuration.configuration_type in ('VISITOR')
and registration_configuration.configuration_id is not null
and participant.participant_tsv || person.person_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail'))
limit 100
I am experimenting with formatting styles, especially relating to
joins. Because I have poor eyesight: visual clues are important, so
that I can focus on key points. Hence the use of abbreviations, naming
conventions, and careful indenting. (I found this especially
important, when I had to write a stored procedure with some 3K lines of
Sybase TransactSQL!) I also use uppercase key words, but I have not
bothered here.
So I would like people's opinions on how I have reformatted the above.
select
participant.participant_id
from
participant pa
join person pe
on pe.person_participant_id = pa.participant_id
left join registration re
on re.registration_registered_participant_id = pa.participant_id
left join enrollment en
on re.registration_enrollment_id = en.enrollment_id
join registration_configuration rc
on en.enrollment_configuration_id = rc.configuration_id
left join event_context ec
on ec.context_id = rc.configuration_context_id
where
pa.participant_type = 'PERSON' and
pa.participant_status = 'ACTIVE' and
ec.context_code in ('GB2TST2010A') and
rc.configuration_type in ('VISITOR') and
rc.configuration_id is not null and
pa.participant_tsv || pe.person_tsv @@
to_tsquery('simple',to_tsquerystring('Abigail'))
limit 100
Cheers,
Gavin
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
