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

[Michael Nielsen]

Investigating further, it seems as if my view gets assigned an alias
"test", which all the other real tables doesn't get assigned.
It's coming from a view called "_table_metadata" which gets the alias from
a table called pg_class (and it's column "relname".

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

> 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/80504ad5/attachment-0003.html>