[Qgis-user] create PostGIS view from QGis layer

[tim dunlevie]

Hi Gert-Jan,

yes - this is done via:
QGIS....Database menu dropdown --> DB Manager --> select your PostGIS
database --> database dropdown, SQL window --> either script your 'view'
here, or select the SQL query builder dialogue --> select 'Create View'

is this what you wanted?

Tim

On Thu, Apr 28, 2022 at 9:16 PM gisnederland via Qgis-user <
qgis-user at lists.osgeo.org> wrote:

> Hi Tim and Raymond,
>
> Perhaps I'd explain a little more:
>
> The QGis database tool (as Tim suggests) converts my QGis layer
> "myLayer" (= a postgis table "myTable" with a QGis filter ("myFilter"
> applied to it) to a new postgis *table*
> I was hoping to find an (automated!) way to convert the layer to a new
> postgis *view*, something like:
>    create or replace view "myView"
>    as select "myFilter"
>    from "myTable"
>
>
>
> Kind regards,
>
> Gert-Jan
>
>
>
> tim dunlevie via Qgis-user schreef op 28-04-2022 14:48:
> > You can also use the QGIS database tool from the menu...to get to the
> > SQL query gui...construct your query through that - add table, add a
> > condition (where blah = blah)...then you have the ability to create a
> > view in the postgis database.....rather than writing sql query using
> > pgadmin etc...
> >
> > On Thu, 28 Apr 2022, 8:38 pm Raymond Nijssen via Qgis-user,
> > <qgis-user at lists.osgeo.org> wrote:
> >
> >> Hi Gert-Jan,
> >>
> >> Not sure how exactly you are filtering your data, but to create
> >> views
> >> you can run these queries on your PostgreSQL database using the tool
> >> you
> >> like (for example DB-manager in QGIS, or DBeaver or PGAdmin).
> >>
> >> In case this is an SQL query filter:
> >>
> >> CREATE VIEW blabla1 AS
> >> [your sql select query goes here]
> >>
> >> In case it's a 'provider object filter' (in the Source tab of your
> >> layer
> >> properties):
> >>
> >> CREATE VIEW view_name AS
> >> SELECT * FROM table_name WHERE
> >> [your filter goes here]
> >>
> >> Hope this helps,
> >> Raymond
> >>
> >> On 28-04-2022 14:09, gisnederland via Qgis-user wrote:
> >>> Hi all,
> >>>
> >>> I've got a QGis (3.20) project with (amongst others) 15 layers
> >> that all
> >>> point to 1 postgis table, but all have a different filter applied
> >> (in
> >>> QGis).
> >>>
> >>> I'm looking for a convenient way to convert these
> >> layers-with-filter to
> >>> views in PostGIS, in which the SQL "select"-statement matches the
> >> QGis
> >>> filter expression.
> >>> Any way to do this in QGis, or with other tooling?
> >>>
> >>>
> >>> Kind regards,
> >>>
> >>> Gert-Jan
> >>> _______________________________________________
> >>> Qgis-user mailing list
> >>> Qgis-user at lists.osgeo.org
> >>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> >>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
> >> _______________________________________________
> >> Qgis-user mailing list
> >> Qgis-user at lists.osgeo.org
> >> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> >> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
> > _______________________________________________
> > Qgis-user mailing list
> > Qgis-user at lists.osgeo.org
> > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20220429/7de11afd/attachment.html>