PostgreSQL support for Faceted Search

Unfortunately PostgreSQL support has not been tested yet. There is no plan at the moment, but patches or other forms of incentives are welcome. ;)

Here are a few specific things that would have to be fixed:

1. The sequence faceted_search_env_id_seq and possibly other tables/sequences aren't set up during installation.
2. GROUP BY clauses use an order element, like GROUP BY n.nid ASC. This is not supported by Postgres and it's probably not ANSI compliant.
3. I tried to temporarily remove the order element, which only revealed the next problem with GROUP BY clauses: Query failed: ERROR: column "term_data.name" must appear in the GROUP BY clause or be used in an aggregate function. I'm quoting myself from another issue:

   The problem is that whenever you use a GROUP BY, all columns you refer to in the SELECT or ORDER BY must also be in the GROUP BY statement (or be used in an aggregate function rather than being referred to directly). The reason for that is that you would otherwise get arbitrary results from the query.

   More info: #150684: Postgres problem... and fix!

The last two are possibly kind of large issues to fix.

First of all... thanks for building this module David.

I can help you with PostgreSQL support for your module (for qualifications see MAINTAINERS.TXT in D5/D6 core).

I've done some small changes to the code and got it working on PostgreSQL (for some of the functionality). At this stage it's only the search results stuff and i'm using str_replace() at this point. Thus it isn't patch worthy. I also need to check a couple things for MySQL before rolling the patch.

The changes were what zoo33 wrote above and also revolving around your use of:

CREATE TEMPORARY TABLE <tablename> SELECT....

PostgreSQL supports:

SELECT <fields> INTO TEMPORARY TABLE <tablename> FROM...

Hopefully we can use the latter in MySQL.

I'll take a look into this later today and try to get a patch rolled for you. Are you planning to get PostgreSQL support in before you release version 1?

PostgreSQL support would be great and patches are very much welcome!

A 1.0 release could happen with or without PostgreSQL, but I'd certainly prefer with it. :-)

Here's the patch I put together before in case someone else has more time than I have. It was only tested with node type facets.

The patch just removes the pieces of the SQL that break on PostgreSQL. You can see what needs to be done in order for it to work.

This patch seems to replace MySQL-specific with PostgreSQL-specific code... We'll need code that works with both. :-)

Here's a patch I rolled for my own use. I think it should retain compatibility with MySQL as well.

In faceted_search.inc I've taken the index definition out of the "CREATE TEMPORARY TABLE" and added it in in another statement. This allows us to specify none of the columns and PostgreSQL will take them all from the query. It does not support specifying part of the table and getting the rest from the SELECT statement like MySQL.

Also in faceted_search.inc I've removed the order clause from the GROUP BY field. I really have no idea what this is supposed to do but I can't imagine it is necessary. Anyone?

In taxonomy_facets.module I fully qualify group by fields and add missing ones in two places. I also add an implicit cast where text and integer values are compared.

Problem with case sensitive search solved (partially).

To make faceted_search to search case insensitive when using PostgeSQL, it is necessary to change

in file "faceted_search.inc" this line (about 1121):

$words_where[] = '('. substr(str_repeat("{$type}_search_index.word = '%s' OR ", count($type_words)), 0, -4) .") AND {$type}_search_index.type = '%s'";

with:
$words_where[] = '('. substr(str_repeat("{$type}_search_index.word ILIKE '%s' OR ", count($type_words)), 0, -4) .") AND {$type}_search_index.type = '%s'";

add for searching parts of the words -

$words_where[] = '('. substr(str_repeat("{$type}_search_index.word ILIKE '%%%s%%' OR ", count($type_words)), 0, -4) .") AND {$type}_search_index.type = '%s'";

The user can of course to write own hook_form, for choosing the type of search.

The problem is in this case, the searched word (or part of word) is no highlighted.
I'll try to find how to solve this problem, but I will appreciate any ideas.

Marked #722726: Is this postgres support for this module? as duplicate.