Tools

with content as (
select 
    'entry' as type, 
    id, 
    created, 
    title, 
    body,
    is_draft
from 
    blog_entry

union

-- selecting from blog_blogmark
select 
    'blogmark' as type, 
    id, 
    created, 
    link_title as title,
    commentary as body,
    is_draft
from 
    blog_blogmark

union

-- selecting from blog_quotation
select 
    'quotation' as type, 
    id, 
    created, 
    source as title, 
    quotation as body,
    is_draft
from 
    blog_quotation
),
tools as (select distinct on (tool_url) 
    unnest(regexp_matches(body, '(https://tools\.simonwillison\.net/[^<"\s)]+)', 'g')) as tool_url,
    'https://simonwillison.net/' || left(type, 1) || '/' || id as blog_url,
    title,
    date(created) as created
 from content where is_draft = false
)
select * from tools order by created desc