Solved

Search all columns in Postgresql table

Posted on 2012-04-04
2
1,157 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 110

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 40

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
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 …

730 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