QGIS & PostGIS STRING_AGG in virtual layer definition returns "no such function: STRING_AGG"

Question

i've defined the following SQL statement in the query editor of qgis' db manager:

select sd.fk_polyg, count(sd.fk_polyg), STRING_AGG(DISTINCT sl.typ, ', ') as concat_typ
from schaden_stein_detail as sd 
join schaden_lookup as sl on  sd.s_id =  sl.id
group by sd.fk_polyg
order by STRING_AGG(DISTINCT sl.typ, ', ') ; 

there is one single polygon dataset (which is not part of that sql-statement). that dataset is referenced in the FROM part: "schaden_stein_detail"-table (i.e. damage details) by a foreign key

the code groups damage details from the table "schaden_stein_detail"by the foreign key field "fk_polyg" and concatenates any existing detail entry into a text field aliased as "concat_type" (where in case of double entries just one single value is returned by utilizing the DISTINCT parameter). remark: the join-part is just for accessing the text-values of the damage types that are stored in the lookup-table "schaden_lookup" / field "sl.typ"

the statement works fine in qgis db managers SQL editor. but it returns a "no such function: STRING_AGG" in "Data Source Manager / Virtual Layer" (of course the schema-names of the above SQL-statements are not used in the virtual layer definition!)

I thought STRING_AGG was a standard PostgreSQL function. any ideas about that?

Answer 1

Virtual layers don't use the underlying storage provider(s) but rather sqlite/spatialite, so postgres specific functions are not available.

You can instead create a query layer from the DB Manager, or you can look for an SQLite compatible function.

Answer 2

From @JGH’s answer, the function i was looking for following his SQLite-remark was GROUP_CONCAT(). that function is basically the same as STRING_AGG() in PostgreSQL. with one exception: I have used STRING_AGG(DISTINCT sl.typ, ', ') in my postgres-statement, which lets me reduce recurring sets of sl.typ-combinations to one single set within this short statement.

SQLite throws an error when using GROUP_CONCAT() in the same way and needes a workaround like this one with a sub-select & the DISTINCT within that sub-select :

SELECT sub.fk_polyg, group_concat(sub.typ, ', ') as concat_typ FROM 
  (select  sd.fk_polyg, sl.typ  
   from schaden_stein_detail as sd 
   join schaden_lookup as sl on  sd.s_id =  sl.id
   order BY sl.typ) sub
GROUP BY sub.fk_polyg;