Thread: BUG #5273: Unexpected function behavior/failure
The following bug has been logged online: Bug reference: 5273 Logged by: Vee Email address: sefer@hotmail.com PostgreSQL version: 8.4.2 Operating system: Linux Description: Unexpected function behavior/failure Details: Hi, I have an odd problem using certain regular expressions (as opposed to any other function) with PostgreSql 8.4.2, I was hoping you could enlighten me as to what am I doing wrong. -- Create the table and populate it. create table test ( data text ); insert into test values ('hello'), ('world'), ('bridge'); -- Test query. select data, upper(data) from test; > hello HELLO > world WORLD > bridge BRIDGE -- The problem query select data, regexp_matches(data, '(h..l)') from test; > hello {hell} Since I have no "where" clause, I would expect to see all the rows in the result of the second case, with possibly a NULL value for the non-matched rows. But I would not expect a "select" clause to effectively filter out results for me. I'd appreciate your input on the matter. Thanks, Vee.
"Vee" <sefer@hotmail.com> writes: > -- The problem query > select data, regexp_matches(data, '(h..l)') > from test; >> hello {hell} > Since I have no "where" clause, I would expect to see all the rows in the > result of the second case, with possibly a NULL value for the non-matched > rows. No. regexp_matches() returns setof something, meaning a row per match. When you have no match, you get no rows. And that in turn means that the calling select produces no rows --- just as it could also produce more than one row from a given table row. I think the behavior you are after is probably more like that of substring(). regards, tom lane
On Tue, Jan 12, 2010 at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Vee" <sefer@hotmail.com> writes: >> -- The problem query >> select data, regexp_matches(data, '(h..l)') >> from test; > >>> hello =A0 =A0 =A0 =A0{hell} > >> Since I have no "where" clause, I would expect to see all the rows in the >> result of the second case, with possibly a NULL value for the non-matched >> rows. > > No. =A0regexp_matches() returns setof something, meaning a row per match. > When you have no match, you get no rows. =A0And that in turn means that > the calling select produces no rows --- just as it could also produce > more than one row from a given table row. > > I think the behavior you are after is probably more like that of > substring(). Or maybe the ~ operator. ...Robert