sql layers requiring multiple sql statements don't work

[beerockxs]

When I have an SQL layer that required multiple statements, e.g. setting configuration like this:

SET my_vars.from_date TO '2019-01-01'; SET my_vars.to_date TO '2019-12-31'; select * from myschema.myview;

where myview needs the configuration to be set, the layer can't be added. Within the DB Manager, everything works fine, the table is populated after executing the query, but when I try to add the layer, I get this error message in the PostGIS log:

Unable to execute the query. The error message from the database was: ERROR: syntax error at or near "." LINE 1: SELECT FROM (SET my_vars.from_date TO '2019-01-01'; ^ . SQL: SELECT FROM (SET my_vars.from_date TO '2019-01-01'; SET my_vars.to_date TO '2019-12-31'; select * from myschema.myview ) AS "subQuery_0" LIMIT

[gioman]

SET my_vars.from_date TO '2019-01-01'; SET my_vars.to_date TO '2019-12-31'; select * from myschema.myview;

@beerockxs can you post a concrete example for table/view?

[github-actions[bot]]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale". If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue. In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue. If there is no further activity on this issue, it will be closed in a week.

[beerockxs]

Sure. Here's a sample function and view:

CREATE FUNCTION myschema.myfunction (IN from_date date, IN to_date date) RETURNS TABLE (id integer, return_from_date date, return_to_date date) LANGUAGE plpgsql STABLE CALLED ON NULL INPUT SECURITY INVOKER PARALLEL UNSAFE COST 1 AS $$ DECLARE BEGIN IF from_date IS NULL THEN from_date = '2000-01-01'; END IF; IF to_date IS NULL THEN to_date = '2100-01-01'; END IF; myfunction.id := 1; myfunction.return_from_date := from_date; myfunction.return_to_date := to_date; return next; END $$;

CREATE VIEW myschema.myview AS

SELECT id, return_from_date, return_to_date from myschema.myfunction(current_setting('my_vars.fromdate')::date, current_setting('my_vars.todate')::date);

And here's the three SQL staments to select my data:

SET my_vars.fromdate TO '2019-01-01'; SET my_vars.todate TO '2019-12-31'; Select * from myschema.myview

[github-actions[bot]]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale". If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue. In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue. If there is no further activity on this issue, it will be closed in a week.

[beerockxs]

I provided the necessary information.

[github-actions[bot]]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale". If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue. In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue. If there is no further activity on this issue, it will be closed in a week.

[beerockxs]

I provided the necessary information

[github-actions[bot]]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale". If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue. In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue. If there is no further activity on this issue, it will be closed in a week.

[beerockxs]

I provided the necessary information