SF.net SVN: ledger-smb:[4517] branches/1.3/sql/modules/Parts.sql

[..hidden..]

Revision: 4517
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=4517&view=rev
Author:   einhverfr
Date:     2012-03-21 12:21:55 +0000 (Wed, 21 Mar 2012)
Log Message:
-----------
Stored procedures for parts search.  Using full text search for parts lookup on description field

Added Paths:
-----------
    branches/1.3/sql/modules/Parts.sql

Added: branches/1.3/sql/modules/Parts.sql
===================================================================
--- branches/1.3/sql/modules/Parts.sql	                        (rev 0)
+++ branches/1.3/sql/modules/Parts.sql	2012-03-21 12:21:55 UTC (rev 4517)
@@ -0,0 +1,17 @@
+BEGIN;
+
+CREATE OR REPLACE FUNCTION parts__search_lite
+(in_partnumber text, in_description text)
+RETURNS SETOF parts AS
+$$
+SELECT * 
+  FROM parts 
+ WHERE ($1 IS NULL OR (partnumber like $1 || '%'))
+       AND ($2 IS NULL 
+            OR (to_tsvector(get_default_lang()::name, description) 
+                @@
+                plainto_tsquery(get_default_lang()::name, $2::tsvector)))
+ORDER BY partnumber;
+$$ LANGUAGE SQL;
+
+COMMIT;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.