minor view creation weirdness

Neil Conway <neilc@samurai.com> writes:

Is this a bug?

(using CVS code from yesterday)

nconway=# create view baz (a,b) as select 'hello', 'world';
WARNING: column "a" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
WARNING: column "b" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
CREATE VIEW
nconway=#

7.3 does the same thing actually. I don't know what that means though.

--
greg

On Thu, 2003-10-02 at 08:40, Greg Stark wrote:

Neil Conway <neilc@samurai.com> writes:

Is this a bug?

(using CVS code from yesterday)

nconway=# create view baz (a,b) as select 'hello', 'world';
WARNING: column "a" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
WARNING: column "b" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
CREATE VIEW
nconway=#

7.3 does the same thing actually. I don't know what that means though.

junk=# \d baz
View "public.baz"
Column | Type | Modifiers
--------+-----------+-----------
a | "unknown" |
b | "unknown" |
View definition:
SELECT 'hello' AS a, 'world' AS b;

There is no table behind the view, so there is no way for PostgreSQL to
derive the column types of a and b. A quoted string (as supplied in the
view definition) could be one of text, varchar, char, date, time,
timestamp, cidr and so on.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Set your affection on things above, not on things on
the earth." Colossians 3:2

Neil Conway <neilc@samurai.com> writes:

Is this a bug?

nconway=# create view baz (a,b) as select 'hello', 'world';
WARNING: column "a" has type "unknown"
DETAIL: Proceeding with relation creation anyway.

It's always done that, although the spelling of the notice has
varied over the years.

These days we tend to force "unknown" to become "text" if a specific
data type is really required, and I suppose a case could be made that
CREATE VIEW should do that too. But the consequences of guessing wrong
are probably worse here than elsewhere, since you can't really change
the view column type short of dropping and recreating the view.

I'd almost argue that we should change this message to an error:

ERROR: column "a" has type "unknown"
HINT: Explicitly cast the string literal to some specific type.

regards, tom lane

On Thu, 2003-10-02 at 10:16, Tom Lane wrote:

Neil Conway <neilc@samurai.com> writes:

nconway=# create view baz (a,b) as select 'hello', 'world';
WARNING: column "a" has type "unknown"
DETAIL: Proceeding with relation creation anyway.

It's always done that, although the spelling of the notice has
varied over the years.

I'd almost argue that we should change this message to an error

I agree.

-Neil

Neil Conway <neilc@samurai.com> writes:

On Thu, 2003-10-02 at 10:16, Tom Lane wrote:

Neil Conway <neilc@samurai.com> writes:

nconway=# create view baz (a,b) as select 'hello', 'world';
WARNING: column "a" has type "unknown"
DETAIL: Proceeding with relation creation anyway.

I'd almost argue that we should change this message to an error

I agree.

Motion proposed and seconded; any objections out there?

regards, tom lane

nconway=# create view baz (a,b) as select 'hello', 'world';
WARNING: column "a" has type "unknown"
DETAIL: Proceeding with relation creation anyway.

It's always done that, although the spelling of the notice has
varied over the years.

I'd almost argue that we should change this message to an error

I agree.

Except that it would totally break backwards-compatibility? Or will
'unknown' fields in views be dumped with explicit casts?

Chris

Tom Lane <tgl@sss.pgh.pa.us> writes:

I'd almost argue that we should change this message to an error

I agree.

Motion proposed and seconded; any objections out there?

Uhm, doesn't the spec have anything to say about this?
I mean, the view sure looks like standard SQL on its face.

In any case, I would sure think there was something strange about a query
working fine as a select but not working in a view:

slo=> select 'foo' as a,'bar' as b;
a | b
-----+-----
foo | bar
(1 row)

slo=> create view x as select 'foo' as a,'bar' as b;
WARNING: Attribute "a" has an unknown type
Proceeding with relation creation anyway
WARNING: Attribute "b" has an unknown type
Proceeding with relation creation anyway
CREATE VIEW

slo=> select * from x;
a | b
-----+-----
foo | bar
(1 row)

--
greg

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

I'd almost argue that we should change this message to an error

I agree.

Except that it would totally break backwards-compatibility?

Well, that's a possible problem. How many such views do you think are
out there, given the existence of the warning?

regards, tom lane

On Fri, 2003-10-03 at 00:50, Greg Stark wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

I'd almost argue that we should change this message to an error

I agree.

Motion proposed and seconded; any objections out there?

Uhm, doesn't the spec have anything to say about this?
I mean, the view sure looks like standard SQL on its face.

In any case, I would sure think there was something strange about a query
working fine as a select but not working in a view:

slo=> select 'foo' as a,'bar' as b;
a | b
-----+-----
foo | bar
(1 row)

slo=> create view x as select 'foo' as a,'bar' as b;
WARNING: Attribute "a" has an unknown type
Proceeding with relation creation anyway
WARNING: Attribute "b" has an unknown type
Proceeding with relation creation anyway
CREATE VIEW

slo=> select * from x;
a | b
-----+-----
foo | bar
(1 row)

or the create table case:

rms=# create table x as select 'foo' as a, 'bar' as b;
WARNING: 42P16: attribute "a" has type UNKNOWN
DETAIL: Proceeding with relation creation anyway.
LOCATION: CheckAttributeType, heap.c:427
WARNING: 42P16: attribute "b" has type UNKNOWN
DETAIL: Proceeding with relation creation anyway.
LOCATION: CheckAttributeType, heap.c:427
WARNING: attribute "a" has type UNKNOWN
DETAIL: Proceeding with relation creation anyway.
WARNING: attribute "b" has type UNKNOWN
DETAIL: Proceeding with relation creation anyway.
SELECT

rms=# select * from x;
a | b
-----+-----
foo | bar
(1 row)

rms=# \d x
Table "public.x"
Column | Type | Modifiers
--------+-----------+-----------
a | "unknown" |
b | "unknown" |

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Greg Stark <gsstark@mit.edu> writes:

Uhm, doesn't the spec have anything to say about this?
I mean, the view sure looks like standard SQL on its face.

Well, you might read the spec as requiring the view column to have
datatype CHAR(n) where n is the length of the unknown literal.
I see in SQL92:

9) The data type of a <character string literal> is fixed-length
character string. The length of a <character string literal>
is the number of <character representation>s that it contains.
...
Note: <character string literal>s are allowed to be zero-length
strings (i.e., to contain no characters) even though it is
not permitted to declare a <data type> that is CHARACTER with
<length> zero.

The NOTE would appear to imply that

CREATE VIEW v AS SELECT '';

is illegal, which is not a conclusion I care to follow blindly. In any
case, in Postgres I would think we would want to take the type as "text"
not "CHAR(n)", spec or no spec.

regards, tom lane