pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
anal about my syntax 3+ messages / 2 participants [nested] [flat]
* anal about my syntax @ 2003-02-11 22:26 James Cooper <jim@luckydigital.com> 0 siblings, 1 reply; 3+ messages in thread From: James Cooper @ 2003-02-11 22:26 UTC (permalink / raw) To: pgsql-sql 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 ) '' LOOP tot = 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 ) ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: anal about my syntax @ 2003-02-12 01:10 Josh Berkus <josh@agliodbs.com> parent: James Cooper <jim@luckydigital.com> 0 siblings, 1 reply; 3+ messages in thread From: Josh Berkus @ 2003-02-12 01:10 UTC (permalink / raw) To: James Cooper <jim@luckydigital.com>; pgsql-sql James, > 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 > ) '' > LOOP > tot = 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. Not as written, no. > 2. could this be a straight SQL function instead( I dont think you > can append strings together in SQL functions ) Yes, it could. If I have time later, I'll draft you an example. -Josh ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: anal about my syntax @ 2003-02-13 00:59 Josh Berkus <josh@agliodbs.com> parent: Josh Berkus <josh@agliodbs.com> 0 siblings, 0 replies; 3+ messages in thread From: Josh Berkus @ 2003-02-13 00:59 UTC (permalink / raw) To: James Cooper <jim@luckydigital.com>; +Cc: pgsql-sql James, A small efficiency improvement, and a syntax fix: CREATE FUNCTION get_people_letter_total( text, integer ) RETURNS integer AS ' select count(person_id) as total from person where person_email like ($1 || ''%'') and EXISTS ( select cp.person_id from cluster_person cp, cluster c where cp.cluster_id = c.cluster_id and c.c_id = $2 and cp.person_id = person.person_id); ' LANGUAGE SQL; > I tried the above but my result is always 0; Yes, that's because of your syntax mistake in your "like" expression. One more comment: the above function will only work case-sensitively; i.e., it won't return a count for SMITHERS@yahoo.com is you search on "smithers". You may want to consider putting a functional index on lower(person.person_email), and changing the above to: CREATE FUNCTION get_people_letter_total( text, integer ) RETURNS integer AS ' select count(person_id) as total from person where lower(person_email) like lower($1 || ''%'') and EXISTS ( select cp.person_id from cluster_person cp, cluster c where cp.cluster_id = c.cluster_id and c.c_id = $2 and cp.person_id = person.person_id); ' LANGUAGE SQL; -- Josh Berkus Aglio Database Solutions San Francisco ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-02-13 00:59 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-02-11 22:26 anal about my syntax James Cooper <jim@luckydigital.com> 2003-02-12 01:10 ` Josh Berkus <josh@agliodbs.com> 2003-02-13 00:59 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox