Warm tip: This article is reproduced from stackoverflow.com, please click
postgresql

How to give select query a name in postgres

发布于 2020-03-27 10:26:56

I have the following query:

select distinct encoder.encoder enc, src_ip, encoder.datacenter 
from logical_service, unnest(tags) enc, encoder, mpeg_out 
where enc like 'encoder:%' 
  and substring(enc,9) = encoder.encoder 
  and logical_service.lid=mpeg_out.lid 
  and encoder.datacenter=mpeg_out.datacenter 
order by encoder.encoder

I would like to give this query a name so that I can later use it. I was trying to do this, but this ultimately fails:

with encoder_alias as (
  select distinct encoder.encoder enc, src_ip, encoder.datacenter 
  from logical_service, unnest(tags) enc, encoder, mpeg_out 
  where enc like 'encoder:%' 
    and substring(enc,9) = encoder.encoder 
    and logical_service.lid=mpeg_out.lid 
    and encoder.datacenter=mpeg_out.datacenter 
    order by encoder.encoder
)

It does not give any output as to why this is not working. Any idea why I can't use the with as to give it the name?

Questioner
Jay266
Viewed
89
a_horse_with_no_name 2019-07-03 23:03

A common table expression needs to be followed by a final SELECT statement:

with encoder_alias as (
  select distinct encoder.encoder enc, src_ip, encoder.datacenter 
  from logical_service
     cross join unnest(tags) enc
     join encoder on substring(enc,9) = encoder.encoder 
     join mpeg_out on logical_service.lid = mpeg_out.lid 
                  and encoder.datacenter = mpeg_out.datacenter 
  where enc like 'encoder:%' 
  order by encoder.encoder
)
select *             ---<<< here
from encoder_alias;

(note that I replaced your ancient, outdated and fragile implicit joins with "modern" (30 years old) explicit JOIN operators)