View fields are cast as text and link to Access as a Memo field

Karen Springer <karen.springer@xxxxxxxxxxxxx> · Tue, 29 Jan 2008 17:02:56 -0700

Hi,

I have a table

CREATE TABLE "HR"."Participant_Names"
(
 "PIP" int4 NOT NULL,
 "LastNAME" varchar(32) NOT NULL,
 "FirstName" varchar(20) NOT NULL,
 "NameUsed" varchar(20),
 CONSTRAINT "pkey_PIP" PRIMARY KEY ("PIP"),
)
WITHOUT OIDS;

and a view

CREATE OR REPLACE VIEW "HR"."tvw_EmployeeNames_ActiveAndInactive" AS
SELECT "Participant_Names"."PIP" AS "employee_ID",
       CASE
           WHEN "Participant_Names"."NameUsed" IS NULL THEN 
("Participant_Names"."LastNAME" || ', ') || "Participant_Names"."FirstName"
           ELSE ("Participant_Names"."LastNAME" || ', ') || 
"Participant_Names"."NameUsed"
       END AS "employeeName"
  FROM "HR"."Participant_Names"
 ORDER BY
       CASE
           WHEN "Participant_Names"."NameUsed" IS NULL THEN 
("Participant_Names"."LastNAME" || ', ') || "Participant_Names"."FirstName"
           ELSE ("Participant_Names"."LastNAME" || ', '::text) || 
"Participant_Names"."NameUsed"
       END;

which when viewed in pgAdmin seems to be automatically cast of text

CREATE OR REPLACE VIEW "HR"."tvw_EmployeeNames_ActiveAndInactive" AS
SELECT "Participant_Names"."PIP" AS "employee_ID",
       CASE
           WHEN "Participant_Names"."NameUsed" IS NULL THEN 
("Participant_Names"."LastNAME"::text || ', '::text) || 
"Participant_Names"."FirstName"::text
           ELSE ("Participant_Names"."LastNAME"::text || ', '::text) || 
"Participant_Names"."NameUsed"::text
       END AS "employeeName"
  FROM "HR"."Participant_Names"
 ORDER BY
       CASE
           WHEN "Participant_Names"."NameUsed" IS NULL THEN 
("Participant_Names"."LastNAME"::text || ', '::text) || 
"Participant_Names"."FirstName"::text
           ELSE ("Participant_Names"."LastNAME"::text || ', '::text) || 
"Participant_Names"."NameUsed"::text
       END;

We are using Access as a front-end and when I link to the view I get

Field Name             Data Type
employee_ID          Number
employeeName       Memo

I need employeeName to be a text field in Access.  I have tried casting 
the fields in the view as varchar, but it seem to default back to ::text.

Any help would be greatly appreciated.

Thanks,
Karen

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match