I think you can do this in QGIS with a virtual layer, but I've not been successful with it.
My quick & dirty way is to create a spatial view in PostgreSQL (often a materialized one) and add that to QGIS.
CREATE MATERIALIZED VIEW pkde.svw_tmp7 AS
WITH cte AS (
SELECT st_difference(b.geom, m.geom) AS geom,
'muni_diff_bnd'::text AS rel_type
FROM xd1.table1 b,
WHERE st_intersects(b.geom, m.geom) = true
SELECT row_number() OVER () AS id,
A second option I use with remote systems when I RDP to servers with postgres databases in environments where I don't have access to a QGIS, is to generate a GeoJson feature collection. Obviously, you can't do this very well with large result sets but for the smaller ones, it works well.
WITH cte as (
SELECT row_id,geom,acres FROM public.table1 limit 4
-- Queries a list of features as a geojson feature collection
SELECT row_to_json(fc)FROM (
SELECT 'FeatureCollection' AS type
,array_to_json(array_agg(f)) AS features
SELECT 'Feature' AS type
/* Change this to name of the actual geometry column. Geom for native PostGIS data. Shape if it came from Esri.
You can also st_transform to a another coordinate system (e.g ST_AsGeoJSON(ST_Transform(lg.shape,4326))::json AS geometry)
,ST_AsGeoJSON(lg.geom)::json AS geometry
-- Put Attribute select here (but don't put the geometry field)
SELECT row_id, acres
) AS l
)) AS properties
FROM cte AS lg /* <-- You need to specify your source table (or view) */
-- limit 5 /* Put a where clause or limit here to filter the query */
) AS f
) AS fc
That'll give you a row with a GeoJSON Feature Collection:
And you can copy and paste that directly into a temporary scratch layer: