Обсуждение: FDW question - how to identify columns to populate in response?

<div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif;color:#000000">Hi, I'm working on a FDW
forthe unix/linux user database - think /etc/passwd and /etc/group although I'm actually using system calls that could
bequietly redirected to LDAP or other backends. It's easy to create the FDW and a table associated with it, something
like</div><divclass="gmail_default" style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000">CREATE TABLE passwd (</div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000">   name text,</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">  passwd text,</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">  uid int,</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">  ...</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Theproblem is the user could decide to reorder or remove columns so
Ican't make the assumption that values[0] is always going to be the username.</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Ihave a solution that requires looking at the rel, extracting the
atts,and then doing a loop where I check the attname against all possible values for each column. Anything that doesn't
matchis set to null. This isn't too bad here but it would be a pain if there are many columns.</div><div
class="gmail_default"style="font-family:tahoma,sans-serif;color:#000000"><br /></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Isthere a cleaner way? I've looked at a number of other FDW
implementationsbut they are generally mapping columns to columns (so it's a short bit of lookup code inside the loop),
notcopying data provided by a system call.</div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Thanks,</div><divclass="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000"><br/></div><div class="gmail_default"
style="font-family:tahoma,sans-serif;color:#000000">Bear</div></div>

Bear Giles <bgiles@coyotesong.com> writes:
> Hi, I'm working on a FDW for the unix/linux user database - think
> /etc/passwd and /etc/group although I'm actually using system calls that
> could be quietly redirected to LDAP or other backends. It's easy to create
> the FDW and a table associated with it, something like

> CREATE TABLE passwd (
>    name text,
>    passwd text,
>    uid int,
>    ...

> The problem is the user could decide to reorder or remove columns so I
> can't make the assumption that values[0] is always going to be the username.

> I have a solution that requires looking at the rel, extracting the atts,
> and then doing a loop where I check the attname against all possible values
> for each column. Anything that doesn't match is set to null. This isn't too
> bad here but it would be a pain if there are many columns.

> Is there a cleaner way?

The only thing that comes to mind is that you could probably amortize the
figure-out-the-mapping overhead over multiple tuples.  There's surely no
reason to do it more often than once per query; and if that's not
good-enough performance you could think about caching it longer, with some
sort of invalidation logic.

Take a look at src/include/access/tupconvert.h and
src/backend/access/common/tupconvert.c for inspiration, or maybe even code
you can use directly.
        regards, tom lane