Calling PostgreSQL procedures or functions from QGIS' Query Builder

Question

I am trying to call either a stored procedure or function defined in the PostgreSQL/PostGIS database and use it from the QGIS Query Builder. Neither work:

• the function returns a table that cannot be interpreted by the Query Builder
• the procedure, which returns a selection, fails when I call it using the call instruction

Both work well in DataGrip and query a tag from a tag text-array column in the waypoints table.

Here is the function:

create or replace function gwd(t text)
RETURNS TABLE (f_ogc_fid int, f_name text)
LANGUAGE plpgsql
AS $$
BEGIN
    return query
        SELECT ogc_fid,name::text
        FROM gis.gps.waypoints
        where tag @> STRING_TO_ARRAY(t, ',');
end;
$$

Here is the procedure which fails at the CALL in QGIS' Query Builder:

create or replace procedure grw(T text)
LANGUAGE plpgsql
AS $$
BEGIN
        SELECT *
        FROM gis.gps.waypoints
        where tag @> STRING_TO_ARRAY(T, ',');
end;
$$

Here is the error reported by QGis when I execute call grw('iron') in the Query Builder:

An error occurred when executing the query.
The data provider said:
ERROR:  syntax error at or near "grw"
LINE 1: SELECT * FROM "gps"."waypoints" WHERE call grw('iron') LIMIT...

When I call it without the CALL, it does not work either:

An error occurred when executing the query.
The data provider said:
ERROR:  grw(unknown) is a procedure
LINE 1: SELECT * FROM "gps"."waypoints" WHERE grw('iron') LIMIT 0
HINT:  To call a procedure, use CALL.

Answer 1

Three things:

• A PRODECURE is a transaction aware procedural statement, introduced in PG 11, to accomodate for the missing transaction control in a normal FUNCTION block.

  However, a PROCEDURE cannot return values, and anything else than directly calling it with (nothing else but)

  CALL <procedure>;
  

  cannot work. You want to use a PROCEDURE whenever you want to execute DDL statements on your relations with transaction control, while handling errors during execution more elegantly. Semantically, what you are looking for is a FUNCTION
  
  

• The Query builder dialog allows to quickly add filter expressions on the data source.

  However, QGIS translates these filters into provider specific syntax, meaning that, in case of a PostgreSQL/PostGIS source layer, it will add these statements to a base query in the form of

  SELECT * FROM "<schema>"."<table>" WHERE <FILTER>;
  

  where <FILTER> is the precise expression entered into the dialog field

• In any way, your attempts are pointless (no offense) for the way you intend to use them:

  • it doesn't make sense to filter a table for a tag to use the returned rows/values as the same filter for the same table
  • and if you wanted to do that, you'd need to again define a filter expression to use the returned rows/values

---

The simplest solution would be to just (and only) enter

'iron' = ANY(tag)

into the Query builder expression field; this translates into

SELECT * FROM "gps"."waypoints" WHERE 'iron' = ANY(tag);

which you can use in SQL directly if needed.