Column "..." does not exist (view + union)

Поиск
Список
Период
Сортировка
От Stefan Weiss
Тема Column "..." does not exist (view + union)
Дата
Msg-id 4EEC0533.9050007@gmail.com
обсуждение исходный текст
Ответы Re: Column "..." does not exist (view + union)  (Andreas Kretschmer <akretschmer@spamfence.net>)
Список pgsql-sql
Assuming the following simple setup with two data tables, one mapping
table, and one view -

-- ----------------------------------------------------------------
CREATE TABLE dossier (   id          SERIAL   NOT NULL PRIMARY KEY
);
CREATE TABLE contact (   id          SERIAL   NOT NULL PRIMARY KEY,   name        TEXT     NOT NULL,   firstname   TEXT
   NULL
 
);
CREATE TABLE dossier_contact (   dossier_id  INTEGER  NOT NULL REFERENCES dossier(id),   contact_id  INTEGER  NOT NULL
REFERENCEScontact(id),   ctype       INTEGER  NOT NULL,   PRIMARY KEY (dossier_id, contact_id)
 
);
CREATE VIEW dossier_contact_v AS   SELECT  dc.dossier_id,           dc.contact_id,           dc.ctype,           (CASE
WHENc.firstname IS NOT NULL                 THEN c.name || ', ' || c.firstname                 ELSE c.name
  END) AS name     FROM  dossier_contact dc     JOIN  contact c ON c.id = dc.contact_id;
 
-- ----------------------------------------------------------------

- running this query -
   SELECT  name     FROM  dossier_contact_v    WHERE  dossier_id = 56993      AND  ctype = 234
UNION   SELECT  name     FROM  dossier_contact_v    WHERE  dossier_id = -1      AND  ctype = -1
ORDER BY ctype;

- fails with the following error message:

ERROR:  column "ctype" does not exist
LINE 10: ORDER BY ctype;                 ^

The same query works fine without the ORDER BY, without the UNION, or
when I select the "ctype" column in addition to "name".
Why?

Using an alias in the FROM clause gives a different error:
   SELECT  x.name     FROM  dossier_contact_v x    WHERE  x.dossier_id = 56993      AND  x.ctype = 234
UNION   SELECT  x.name     FROM  dossier_contact_v x    WHERE  x.dossier_id = -1      AND  x.ctype = -1
ORDER BY x.ctype;

ERROR:  missing FROM-clause entry for table "x"
LINE 10: ORDER BY x.ctype                 ^

I am using "PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC
gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit".


thanks,
stefan



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

Предыдущее
От: Misa Simic
Дата:
Сообщение: Re: using a generated series in function
Следующее
От: Andreas Kretschmer
Дата:
Сообщение: Re: Column "..." does not exist (view + union)