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

Mon Apr 4 10:05:29 UTC 2016

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/eb2eb12d/attachment-0002.html>