Jump to content
  • Advertisement
Sign in to follow this  
polly

Postgresql functions

This topic is 4812 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

OK. This one has me stumped. Is it possible to write a Postgres function in something like pl/pgSQL (Link to manual) to take a set of results from an SQL query and constrain the results still further? My problem is this; I have a set of results roughly like this: id | firstname | secondname 1 BILL Bloggs 2 Frank Spencer 3 Eric Idle 4 ERIC IDLE 5 Bill Bloggs 6 eric I And I need to sort of "unique-ify" them on lowercase(firstname) lowercase(secondname) so that I end up with: id | firstname | secondname 1 bill bloggs 2 frank spencer 3 eric idle 6 eric i This doesnt seem to hard at first glance, but doing it in SQL alone is seemingly impossible. I can't use the "distinct" keyword, as I need to keep the id, but at the same time ignore it when I'm selecting distinct rows (Postgres has a "distinct on" keyword, but I can't use it as I have to port this to Oracle later, and Oracle has no equivilent). The other problem is the ordering, which is important - the first set of results is ordered correctly and I need to pick out the first instance of each name(lowercased). Anyway, I've read the manual, which doesn't really go into that much detail, and I haven't found much help googling. But I was wondering if I could write a function which took a set of results as an argument and removed (or retained) some of those results before returning them. So I could run it as so: SELECT unique_name(*) from foobar WHERE foo = 'bar'; And unique_name would take the results and sort of de-dupe them. Suggestions appreciated. Jon

Share this post


Link to post
Share on other sites
Advertisement
Bah, forget the function. My boss just told me we can't have any more plSQL functions in the product. And I can see his point, they do make things a bit messy.

Jon

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!