Received: from beagle.cs.appstate.edu (beagle.cs.appstate.edu [152.10.40.6]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA22097 for ; Sat, 24 Jul 1999 11:12:46 -0400 (EDT) (envelope-from khj@beagle.cs.appstate.edu) Received: (from khj@localhost) by beagle.cs.appstate.edu (8.9.1/8.9.1) id LAA17097; Sat, 24 Jul 1999 11:13:20 -0400 (EDT) Sender: khj@beagle.cs.appstate.edu To: pgsql-sql@postgresql.org Subject: Expr Abbreviations/Functions? Reply-To: khj@cs.appstate.edu From: Kenneth Jacker Date: 24 Jul 1999 11:13:19 -0400 Message-ID: Lines: 52 X-Mailer: Gnus v5.6.44/Emacs 20.2 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