[Bucardo-general] selecting from view instead of underlying table for sync

[Ali Asad Lotia]

On 8 May 2012 17:48, Ali Asad Lotia <ali at anobii.com> wrote:
> On 8 May 2012 16:31, Greg Sabino Mullane <greg at endpoint.com> wrote:
>> On Tue, May 08, 2012 at 04:20:46PM +0100, Ali Asad Lotia wrote:
>>> Hi All,
>>> I'm wondering if it is possible to select from a view instead of the
>>> underlying table/s for a configured synced table.
>>
>> Not directly, but you can basically use the view definition as
>> the customcols entry.
>
> Hi Greg,
> Will customcols work with subselects in the select clause, and is
> there a flag that needs to be set for customcols to work. I ask since
> I have a customcols select clause defined that has a bunch of
> subselects in it. Also, are the customcols definitions sensitive to
> whitespace? I ask because I have customselects defined but they aren't
> really having any impact on the sync from PostgreSQL to Mongo.
>
> Do note that the select clause does have a section that selects the
> primary key "person_id" AS "id". Will this prevent customcols from
> working?
>
> Thanks,
> Ali

I defined a customcol for a table and discovered:

1. Subselects work.

2. Any columns selected AS (foo as bar) lose type information. This is
causing problems when syncing to mongo since they are simply inserted
as strings. I have a pretty good guess this is happening because type
information is retrieved from the pg_attributes table in the
PostgreSQL DB and any columns select AS aren't present in the table.
Are there any workarounds so I can retain type information.

Thanks,
Ali