Error when importing tables with text(length) columns

[jxskiss]

When importing tables with "TEXT(LENGTH)" columns, the following errors occurs:

Error : ERROR: type modifier is not allowed for type "text"
LINE 2: guid text(32) NOT NULL,
^
QUERY: CREATE FOREIGN TABLE public.accounts (
guid text(32) NOT NULL,
name text(2048) NOT NULL,
account_type text(2048) NOT NULL,
commodity_guid text(32),
commodity_scu bigint NOT NULL,
non_std_scu bigint NOT NULL,
parent_guid text(32),
code text(2048),
description text(2048),
hidden bigint,
placeholder bigint
) SERVER sqlite_server
OPTIONS (table 'accounts')
CONTEXT: importing foreign table "accounts"

postgresql version: 9.6.2

Adding the following two lines below line 1487 fixed the error (but I'am very limit to C language and postgresql, so don't know wether it has any negative impact to some other things :-) ).

diff --git a/src/sqlite_fdw.c b/src/sqlite_fdw.c
index 1e0d7bc..30d38cf 100755
--- a/src/sqlite_fdw.c
+++ b/src/sqlite_fdw.c
@@ -1485,6 +1485,8 @@ sqliteTranslateType(StringInfo str, char *typname)
             appendStringInfoString(str, "bigint");

        /* XXX try harder handling sqlite datatype */
+       else if (strncmp(type, "text", strlen("text")) == 0)
+               appendStringInfoString(str, "text");

        /* if original type is compatible, return lowercase value */
        else

Thanks for your great work!

[jxskiss]

The original sqlite table is created by Gnucash, following is the DDL:

CREATE TABLE accounts(
	guid text(32) PRIMARY KEY NOT NULL,
	name text(2048) NOT NULL,
	account_type text(2048) NOT NULL,
	commodity_guid text(32),
	commodity_scu integer NOT NULL,
	non_std_scu integer NOT NULL,
	parent_guid text(32),
	code text(2048),
	description text(2048),
	hidden integer,
	placeholder integer
);

And the sql used to import tables:

IMPORT FOREIGN SCHEMA public FROM SERVER slserver INTO public;

[gleu]

I just commited a fix to handle the text datatype. Can you test it? Thanks. (It's almost what you've done)

[jxskiss]

Yes, it works, the table "accounts" is imported as:

CREATE FOREIGN TABLE "public"."accounts" (
	"guid" text NOT NULL COLLATE "default",
	"name" text NOT NULL COLLATE "default",
	"account_type" text NOT NULL COLLATE "default",
	"commodity_guid" text COLLATE "default",
	"commodity_scu" int8 NOT NULL,
	"non_std_scu" int8 NOT NULL,
	"parent_guid" text COLLATE "default",
	"code" text COLLATE "default",
	"description" text COLLATE "default",
	"hidden" int8,
	"placeholder" int8
)
SERVER "slserver"
OPTIONS ("table" 'accounts');

[gleu]

Great. Thank you very much for the report and the patch!