Received: from uno.tksoft.com (modem-10.copernicus.brandx.net [209.55.65.10]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA96575 for ; Sat, 24 Jul 1999 19:12:58 -0400 (EDT) (envelope-from tk@uno.tksoft.com) Received: (from tk@localhost) by uno.tksoft.com (8.8.8/8.8.8) id QAA27601; Sat, 24 Jul 1999 16:11:57 -0700 From: "tjk@tksoft.com" Message-Id: <199907242311.QAA27601@uno.tksoft.com> Subject: Re: [SQL] Expr Abbreviations/Functions? To: khj@cs.appstate.edu Date: Sat, 24 Jul 1999 16:11:57 -0700 (PDT) Cc: pgsql-sql@postgreSQL.org In-Reply-To: from "Kenneth Jacker" at Jul 24, 99 11:13:19 am Content-Type: text Kenneth, You could merge the fields and search with : select * from tablex where lower(f1||f2||f3) ~ 'foo'; Troy Troy Korjuslommi Tksoft OY, Inc. tjk@tksoft.com Software Development Open Source Solutions Hosting Services > > I find myself frequently typing (postgresql-6.5.1) the following: > > SELECT * FROM table > WHERE (f1 ~* 'foo' OR f2 ~* 'foo' OR f3 ~* 'foo'); > > Can "CREATE FUNCTION" be used? Something like > > CREATE FUNCTION f(string) > RETURNS bool > AS '(f1 ~* '$1' OR f2 ~* '$1' ...)'; > > and then type > > SELECT * FROM table WHERE f('foo'); > > > I tried to define such a function, but encountered two problems: > > o It didn't "accept" the function's definition > > o Syntax errors (apostrophes need escaping ... I tried mixing quotes > and apostrophes, also using '\' chars ... nothing seemed to work) > > > I know that I can define VIEWs (and FUNCTIONs) that contain a specific > SELECT statement, but I'd prefer to have definitions of just the > repeating expressions. > > > Another thing I'd like to "abbreviate" is the second phrase in > SELECTs. E.g, > > SELECT x FROM table; > > instead of > > SELECT x1,x2,x3 FROM table; > > where 'x' is somehow equivalent to 'x1,x2,x3'. > > > I read the online docs and searched a few SQL books, but can't seem to > find the correct approach. Ideas? > > Thanks for any help, > > -Prof Kenneth H Jacker khj@cs.appstate.edu > -Computer Science Dept www.cs.appstate.edu/~khj > -Appalachian State Univ > -Boone, NC 28608 USA > > > >