PostgreSQL WITH query on already selected table

Question

I use PostgreSQL WITH https://www.postgresql.org/docs/current/static/queries-with.html. And I can run pretty complicated query, but can I use already sorted data? For instance:

SELECT id, f_name, l_name, email FROM users
WHERE f_name = 'BabyBoy'

WITH i_need_do_thomething AS (
# and than use that filtered data
)
SELECT * FROM i_need_do_thomething

Thanks

UPDATE

All problems that I use this query in Ruby on Rails. And I use association like:

@laptop = Laptop.find(1)
@laptop.user.do_custom_sql(HERE_WILL_BE_RAW_SQL)

where @laptop.user is equal to

SELECT id, f_name, l_name, email FROM users
WHERE f_name = 'BabyBoy'

Michel Milezzi · Accepted Answer · 2017-05-29 18:23:05Z

2

Each query on WITH QUERIES is treated like a temporary table. You can place as many queries as you need:

WITH filtered_something AS (
    SELECT id, f_name, l_name, email FROM users WHERE f_name = 'BabyBoy'
), i_need_do_something AS (
    SELECT * FROM filtered_something
)
SELECT * FROM i_need_do_something;

edited May 29, 2017 at 18:23

answered May 29, 2017 at 18:01

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oleh Sobchuk Over a year ago

so I can use WITH ONLY in a separate case? And I can't use it as I wrote in the example. Right?

Michel Milezzi Over a year ago

Yes, you can't do it this way. In this case I think you should use ActiveRecord::Base.connection.execute(entire_sql_here) and then iterate over result set.

Collectives™ on Stack Overflow

PostgreSQL WITH query on already selected table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related