Обсуждение: Unable to read from a view

Hi all,

I just created a view and have been unable to read from it.  I am
Apache/1.3.1 (Unix) PHP/3.0.3 and Postgres 6.4.  When I tried to do a
select it gives the following error message:

real=> select * from pubsearch;
ERROR:  nodeRead: Bad type 0

I created the view with the following select statment:

select distinct users.firstname, users.lastname, publication.title,
publication.publisher, publication.year from
publication, authors, users
where users.id = authors.id and authors.pubrelease =
publication.pubrelease;

firstname is type text
lastname is type text
title is type text
year is type text

Thanks in advance for all your help,

-Greg

>
>
> Hi all,
>
> I just created a view and have been unable to read from it.  I am
> Apache/1.3.1 (Unix) PHP/3.0.3 and Postgres 6.4.  When I tried to do a
> select it gives the following error message:
>
> real=> select * from pubsearch;
> ERROR:  nodeRead: Bad type 0
>
> I created the view with the following select statment:
>
> select distinct users.firstname, users.lastname, publication.title,
         ^^^^^^^^

    DISTINCT isn't supported in view definitions. You can SELECT
    from view's distinct, but you cannot define them to be.  Nor
    could you define views with group by and order by.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

On Wed, 24 Feb 1999, Jan Wieck wrote:

> >
> >
> > Hi all,
> >
> > I just created a view and have been unable to read from it.  I am
> > Apache/1.3.1 (Unix) PHP/3.0.3 and Postgres 6.4.  When I tried to do a
> > select it gives the following error message:
> >
> > real=> select * from pubsearch;
> > ERROR:  nodeRead: Bad type 0
> >
> > I created the view with the following select statment:
> >
> > select distinct users.firstname, users.lastname, publication.title,
>          ^^^^^^^^
>
>     DISTINCT isn't supported in view definitions. You can SELECT
>     from view's distinct, but you cannot define them to be.  Nor
>     could you define views with group by and order by.

I have tried to drop the view and it gives the same error message.  Is
there any way to get rid of them (2).

Thanks for the help,

-Greg

>
>
>
> On Wed, 24 Feb 1999, Jan Wieck wrote:
>
> > >
> > >
> > > Hi all,
> > >
> > > I just created a view and have been unable to read from it.  I am
> > > Apache/1.3.1 (Unix) PHP/3.0.3 and Postgres 6.4.  When I tried to do a
> > > select it gives the following error message:
> > >
> > > real=> select * from pubsearch;
> > > ERROR:  nodeRead: Bad type 0
> > >
> > > I created the view with the following select statment:
> > >
> > > select distinct users.firstname, users.lastname, publication.title,
> >          ^^^^^^^^
> >
> >     DISTINCT isn't supported in view definitions. You can SELECT
> >  from view's distinct, but you cannot define them to be.  Nor
> >  could you define views with group by and order by.
>
> I have tried to drop the view and it gives the same error message.  Is
> there any way to get rid of them (2).

    Don't  know if it really works for you (I can't reproduce the
    problem shortly), but try this as postgres superuser:

        DELETE FROM pg_rewrite WHERE rulename = '_RETpubsearch';
        DROP TABLE pubsearch;


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

>
>     Don't  know if it really works for you (I can't reproduce the
>     problem shortly), but try this as postgres superuser:
>
>         DELETE FROM pg_rewrite WHERE rulename = '_RETpubsearch';
>         DROP TABLE pubsearch;

It worked thanks a lot!!!

-Greg