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

Поиск
Список
Период
Сортировка
От Karen Springer
Тема View fields are cast as text and link to Access as a Memo field
Дата
Msg-id 479FBEB0.6010006@wulfsberg.com
обсуждение исходный текст
Ответы Re: View fields are cast as text and link to Access as a Memo field  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: PGCon vs Postgresql Conference
Следующее
От: Tom Lane
Дата:
Сообщение: Re: View fields are cast as text and link to Access as a Memo field