Re: Alias field names in foreign data wrapper?

[Deven Phillips <deven.phillips@xxxxxxxxx>]

Yes. Here's the MySQL schema:

CREATE TABLE `liquorstore_backendipaddress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `backend_network_id` int(11) DEFAULT NULL,
  `backend_virtual_interface_id` int(11) DEFAULT NULL,
  `address` varchar(15) NOT NULL,
  `is_gateway` tinyint(1) NOT NULL,
  `is_reserved` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `backend_network_id` (`backend_network_id`,`address`),
  KEY `liquorstore_backendipaddress_4a648124` (`backend_network_id`),
  KEY `liquorstore_backendipaddress_27235e4d` (`backend_virtual_interface_id`),
  CONSTRAINT `backend_network_id_refs_id_1d869576` FOREIGN KEY (`backend_network_id`) REFERENCES `liquorstore_backendnetwork` (`id`),
  CONSTRAINT `backend_virtual_interface_id_refs_id_b058eaeb` FOREIGN KEY (`backend_virtual_interface_id`) REFERENCES `liquorstore_backendvirtualinterface` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=72184 DEFAULT CHARSET=latin1

On Fri, Mar 27, 2015 at 5:19 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 03/27/2015 01:55 PM, Deven Phillips wrote:

Better example of the problem... My FDW table schema is:

CREATE FOREIGN TABLE liquorstore_backendipaddress (
     id bigint NOT NULL,
     backend_network_id bigint,
     backend_virtual_interface_id bigint,
     address character varying(15) NOT NULL,
     is_gateway boolean NOT NULL,
     is_reserved boolean NOT NULL
)
SERVER edison
OPTIONS (
     dbname 'edison',
     table_name 'liquorstore_backendvirtualinterface'
);


But when I run the following query:

SELECT
     *
FROM liquorstore_backendipaddress

I get an error:

ccedison=# SELECT * FROM liquorstore_backendipaddress;
ERROR:  failed to prepare the MySQL query:
Unknown column 'backend_network_id' in 'field list'

So is 'backend_network_id' in the MySQL table?

I still cannot figure out what the problem might be so any help would be
GREATLY appreciated.

Thanks,

Deven

On Fri, Mar 27, 2015 at 4:15 PM, Deven Phillips
<deven.phillips@xxxxxxxxx <mailto:deven.phillips@gmail.com>> wrote:

    I am trying out some ideas using FDW, and I have added some FDW
    tables which access a backend MySQL DB... Unfortunately, I am
    getting some errors because of fields names with reserved words. I
    was wondering if there is a way to "alias" a field name when
    creating the foreign table?

    Thanks in advance!!!

    Deven

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx