4

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.
1
  • Why do you want to use it in the Query Builder? Or do you mean the "Execute and load SQL"? Oct 19, 2020 at 6:55

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.