Postgres: regex and nested queries something like Unix pipes

Question

Command should do: Give 1 as output if the pattern "*@he.com" is on the row excluding the headings:

 user_id | username |   email   |           passhash_md5           | logged_in | has_been_sent_a_moderator_message | was_last_checked_by_moderator_at_time | a_moderator 
---------+----------+-----------+----------------------------------+-----------+-----------------------------------+---------------------------------------+-------------
       9 | he       | [email protected] | 6f96cfdfe5ccc627cadf24b41725caa4 |         0 |                                 1 | 2009-08-23 19:16:46.316272            | 

In short, I want to connect many SELECT-commands with Regex, rather like Unix pipes. The output above is from a SELECT-command. A new SELECT-command with matching the pattern should give me 1.

Related

Answer 1

Did you mean

SELECT regexp_matches( (SELECT whatevername FROM users WHERE username='masi'), 'masi');

you obviously can not feed the record (*) to regexp_matches, but I assume this is not what your problem is, since you mention the issue of nesting SQL queries in the subject.

Maybe you meant something like

SELECT regexp_matches( wn, 'masi' ) FROM (SELECT whatevername AS wn FROM users WHERE username LIKE '%masi%') AS sq;

for the case when your subquery yields multiple results.

Answer 2

It looks like you could use a regular expression query to match on the email address:

select * from table where email ~ '.*@he.com';

To return 1 from this query if there is a match:

select distinct 1 from table where email ~ '.*@he.com';

This will return a single row containing a column with 1 if there is a match, otherwise no rows at all. There are many other possible ways to construct such a query.

Answer 3

Let's say that your original query is:

select * from users where is_active = true;

And that you really want to match in any column (which is bad idea for a lot of reasons), and you want just to check if "*@he.com" matches any row (by the way - this is not correct regexp! correct would be .*@he.com, but since there are no anchors (^ or $) you can just write @he.com.

select 1 from (
    select * from users where is_active = true
) as x
where textin(record_out( x )) ~ '@he.com'
limit 1;

of course you can also select all columns:

select * from (
    select * from users where is_active = true
) as x
where textin(record_out( x )) ~ '@he.com'
limit 1;