Solved

Search all columns in Postgresql table

Posted on 2012-04-04
2
1,094 Views
Last Modified: 2012-08-13
I am looking for a way to allow a user from a web front end to query all columns in a postgresql table for multiple values. So if had a tabled named Orders that has columns "id, date, customer, product".

I would like allow the user to be able to search for all entries in the table where the customer Bill ordered lamps by passing a search value of "Bill + Lamps" or something similar to this.

I'm thinking I will need to setup full-text search, but I wasn't sure if this is the case and how to set it up.
0
Comment
Question by:abgtemp
2 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37809623
Suggest you create a script that uses SELECT * to get all of the information in all of the rows in the table, and that uses var_dump() to put all of this information on the screen.  Feed the resulting pages to Google "site search" and see if you get the results you want.  If that works, you can refine the var_dump() output to make something that is more eye-pleasing to the client.

Not sure what your table structure might look like, but I am guessing that you have more than one table?  If so, you will need to write a "view" script for every possible relationship between the tables.  This may be a big and time-consuming task.  Whether you choose to use a pre-existing service like Google or invent your own search with full-text searching will largely be decided by how much time and budget you have available.  Google will likely be faster and less expensive.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 300 total points
ID: 37812367
I suggest you use Postgres functions to do that where client app can pass in certain parameter values (like column and string to search for) then in the function you run pg sql like:

select * from table_name where column name like '%search_text%';

You don't necesarily need full text search especialy FULL_TEXT searches are different than LIKE and can show more records than exact match - please see more details at: http://www.postgresql.org/docs/9.0/static/textsearch.html

As far as installing full text - you don't need to install anything but create GIN/GIST indexes [ http://www.postgresql.org/docs/9.0/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX ] and use speciffic commands to do the full text search - http://www.postgresql.org/docs/9.0/static/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question