2

I have this :

CREATE TABLE users
(
    first_name varchar(60) NOT NULL, 
    last_name varchar(60) NOT NULL
);

INSERT INTO users
    (first_name, last_name)
VALUES
    ('Sylvia ', 'Hoeks'),
    ('Harrison', 'Ford');

I get the list like this

SELECT first_name || ' ' || last_name AS user_full_name FROM users;

But I would like to be able to search on the column user_full_name. Is it possible without a subquerie or CTE (like below)?

WITH users_list AS (
    SELECT first_name || ' ' || last_name AS user_full_name
    FROM users
)

SELECT * FROM users_list WHERE user_full_name ILIKE '%lvi%';

I tried with the first column !?

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE 1 ILIKE '%lvi%';
/*
ERROR:  operator does not exist: integer ~~* unknown
LINE 1: ...|| last_name AS user_full_name FROM users WHERE 1 ILIKE '%lv...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
*/

I would like to be able to do something like this.

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE user_full_name ILIKE '%lvi%';

For the moment I do:

SELECT first_name || ' ' || last_name AS user_full_name FROM users WHERE first_name ILIKE '%lvi%' OR last_name ILIKE '%lvi%';

Fiddle if needed : https://www.db-fiddle.com/f/c3HANw8M9W7kSVeEwidpne/0

6
  • 1
    What's wrong with a subquery or a derived table?
    – user1822
    Oct 12, 2017 at 7:30
  • I'm between an ORM.. :/
    – Mio
    Oct 12, 2017 at 7:32
  • 1
    Shouldn't your obfuscation layer then handle that? And how does your "ORM" query look like? And which obfuscation layer and programming language is that?
    – user1822
    Oct 12, 2017 at 7:33
  • I'm using ActiveRecord on Rails and for the moment didn't find a way to do it. Thanks for the help.
    – Mio
    Oct 12, 2017 at 7:34
  • 1
    Then you probably need to put that calculated column into a view, or create an additional column that is populated by a trigger.
    – user1822
    Oct 12, 2017 at 7:35

1 Answer 1

4

The usual way to do this, is to use a derived table:

select * 
from (
 select first_name, 
        last_name, 
        first_name || ' ' || last_name AS user_full_name
 from users
) t
where user_full_name ilike '%lvi%';

That is extremely basic and generic SQL and any obfuscation layer (aka "ORM") should be able to deal with that.

Other options you have:

1. Use a view

Create a view with the calculated function and tell your obfuscation layer to use that view.

2. Create a "virtual" column

Postgres doesn't really have calculated columns (like e.g. Oracle or SQL Server) but you can simulate them with a function:

create function user_full_name(p_row users)
  returns text
as 
$$
  select concat_ws(' ', p_row.first_name, p_row.last_name);
$$
language sql;

This can be used like this:

select u.user_full_name
from users u
where u.user_full_name ilike %lvi%'

Note that the prefix for the column name is required otherwise it does not work. I don't know if you can teach your obfuscation layer to create SQL like that.

3. Populate an additional column with a trigger

CREATE TABLE users
(
    first_name varchar(60) NOT NULL, 
    last_name varchar(60) NOT NULL,
    user_full_name text
);

create or replace function update_full_name() 
  returns trigger
as
$$
begin
  new.user_full_name := concat_ws(' ', new.first_name, new.last_name);
  return new;
end;
$$
language plpgsql;

create trigger update_full_name_trigger
  before update or insert on users
  for each row
  execute procedure update_full_name();

Then you can simply write:

select user_full_name
from users u
where user_full_name ilike %lvi%';

That is probably the solution that is the easiest to be integrated into an obfuscation layer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.