Thread: Alias field names in foreign data wrapper?

Alias field names in foreign data wrapper?

From

Deven Phillips

Date:

27 March 2015, 23:15:32

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

Re: Alias field names in foreign data wrapper?

From

Deven Phillips

Date:

27 March 2015, 23:55:28

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'

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@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

Re: Alias field names in foreign data wrapper?

From

"David G. Johnston"

Date:

28 March 2015, 00:06:57

On Fri, Mar 27, 2015 at 1:55 PM, Deven Phillips <deven.phillips@gmail.com> 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'

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@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

I'm not seeing where you've provided sufficient information for someone to help you.

From what I can tell there is not aliasing capability native to the FDW implementation so anything would have to be defined in OPTIONS for the table which is a wrapper-controlled structure.

Depending on the overall needs you typically can use reserved label as identifiers if you surround them with double-quotes.

CREATE ... (

"troublesome column name here" bigint,

...

)

David J.

Re: Alias field names in foreign data wrapper?

From

Adrian Klaver

Date:

28 March 2015, 00:19:21

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@gmail.com <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@aklaver.com

Re: Alias field names in foreign data wrapper?

From

Deven Phillips

Date:

28 March 2015, 00:20:57

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@aklaver.com> 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@gmail.com <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@aklaver.com

Re: Alias field names in foreign data wrapper?

From

Adrian Klaver

Date:

28 March 2015, 00:31:45

On 03/27/2015 02:20 PM, Deven Phillips wrote:
> 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
>

I refer you to here:

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

When I have had problems with MySQL not seeing names I knew to be there
it usually had something to do with the settings above.

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Alias field names in foreign data wrapper?

From

Deven Phillips

Date:

28 March 2015, 01:04:53

OK, one of the devs on the GitHub page for that FDW helped me realize that I had accidentally mapped the wrong table! Doh!

Deven

On Fri, Mar 27, 2015 at 5:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 03/27/2015 02:20 PM, Deven Phillips wrote:
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

I refer you to here:

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

When I have had problems with MySQL not seeing names I knew to be there it usually had something to do with the settings above.

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Alias field names in foreign data wrapper?

From

Adrian Klaver

Date:

28 March 2015, 01:08:13

On 03/27/2015 03:04 PM, Deven Phillips wrote:
> OK, one of the devs on the GitHub page for that FDW helped me realize
> that I had accidentally mapped the wrong table! Doh!

Me looks back. Oops, missed that.

>
> Deven
>


--
Adrian Klaver
adrian.klaver@aklaver.com