[ckan-dev] Use view instead of table for datastore resource

Mon Apr 4 10:21:09 UTC 2016

Just to clarify, when using DROP TABLE resource_id and then manually
creating a table it still works.
So it's the fact that it's a view and not a table which seems to cast an
error.
But somehow I guess the datastore shouldn't are if it's a table or a view
as they have somewhat same behaviour?

On Mon, Apr 4, 2016 at 12:05 PM, Michael Nielsen <mic.niel84 at gmail.com>
wrote:

> I would like to use a view instead of a table as a DataStore resource.
>
> I'm creating my DataStore only resource via the API datastore_create:
>
>     {
>     "force": true,
>     "resource": {
>         "package_id": "3d2101e5-0166-46ba-b112-8b9da5882a42",
>         "name": "Test"
>         }
>     }
>
> The DataStore table is created successfully with 2 empty columns "_id" and
> "_full_text".
>
> I'm then trying to use:
>
>     DROP TABLE "resource_id";
>
> And create a similar view:
>
>     CREATE VIEW "resource_id" AS (
>         SELECT row_number() OVER (ORDER BY id) AS _id,
>         to_tsvector('') AS _full_text,
>         column1, column2
>         FROM schema-test.my-table
>     );
>
> When querying the resource in PSQL everything seems to match other normal
> DataStore resources. But CKAN fails to show the resource. I'm getting an
> internal error. The log shows:
>
>     ...SELECT * FROM "test" LIMIT 1'], 'orig': ['relation "test" does not
> exist\\nLINE 1: SELECT * FROM "test" LIMIT 1\\n
>  ^\\n']}, 'query': ['Invalid query']}...
>
> So it seems as if there is created some sort of alias to the table, which
> gets deleted when I'm running DROP TABLE.
> Can I recreate that correctly or is the error some where else?
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/ckan-dev/attachments/20160404/62420534/attachment-0003.html>