Обсуждение: FW: tablename.columnname support

-----Original Message-----
From: Vijayendra M Agrawal [mailto:vijayendra.agrawal@wipro.com]
Sent: Sunday, September 17, 2000 11:24 AM
To: The Hermit Hacker
Subject: RE: [ADMIN] tablename.columnname support


Dear Hermit,

I didn't ask the question just for the sake of asking...

The design in my module is such that for any requests from the client side,
I have to do some DB operation. The operation could be either SELECT,
UPDATE, INSERT or DELETE in the database depending on the request id and the
data in the database. So, I am having a meta-data table in the database,
which will tell me what kind of operation to do on a particular request id,
what are the tables involved, what are the columns involved, what are the
keys for these tables, and what are the relations(join conditions) between
the tables involved. So, here in column names, I am storing columns in the
tablename.columnname format, becuase columns can be from many tables.

Here comes the problem specified in my query earliar...

If you have an idea, ORACLE supports tablename.columnname format in all
INSERT, SELECT, UPDATE and DELETE queries.

Anyway, thanks for your reply...

Thanks and regards,
Vijay

-----Original Message-----
From: The Hermit Hacker [mailto:scrappy@hub.org]
Sent: Saturday, September 16, 2000 8:50 PM
To: vijayendra mohan agrawal
Cc: PostGre SQL Mailing List
Subject: Re: [ADMIN] tablename.columnname support


On Sat, 16 Sep 2000, vijayendra mohan agrawal wrote:

> Hi all,
>
> say, we have a table created by :
> create table a ( a char(11), b char(22) );
>
> For adding data into table 'a', ORACLE supports column name format as
> tablename.columnname as follows :
> insert into a ( a.a, a.b ) values ( 'xyz', 'abc') ;
>
> But, PostGreSQL doesn't support... It gives parse error as follows :
> ERROR:  parser: parse error at or near "."
>
> Can this feature be added in PostGreSQL ???

stupid question, but why would you use that format instead of doing:

INSERT INTO a ( a, b ) VALUES ( 'xyz', 'abc' );

Why the tablename. in front?

vijayendra mohan agrawal wrote:
> The design in my module is such that for any requests from the client side,
> I have to do some DB operation. The operation could be either SELECT,
> UPDATE, INSERT or DELETE in the database depending on the request id and the
> data in the database. So, I am having a meta-data table in the database,
> which will tell me what kind of operation to do on a particular request id,
> what are the tables involved, what are the columns involved, what are the
> keys for these tables, and what are the relations(join conditions) between
> the tables involved. So, here in column names, I am storing columns in the
> tablename.columnname format, becuase columns can be from many tables.

I see your point. But couldn't you just strip out the dbname prefix when talking
to PostgreSQL?

Best Regards,
Haroldo.


--
----------------------+------------------------
 Haroldo Stenger      | hstenger@ieee.org
 Montevideo, Uruguay. | hstenger@adinet.com.uy
----------------------+------------------------
 Visit UYLUG Web Site: http://www.linux.org.uy
-----------------------------------------------

        Hie ,

    I've just install PostgreSQL 7.0.2 on i686-pc-linux-gnu      (Pentium 233 with
64 Mo RAM)
     But i've also PostgreSQL 6.5.2     "
"     (P              450  with 256 Mo RAM)
    I've dump one of my big database (500000 rows) on the new one .

    I've made some test and one of my querie take      7 sec on the old server

1 sec on the new
    The EXPLAIN seem to be different .
    Is it a improvement or a mistake ?

    With this good result  , Postgresql 7.0 equals Mysql  ! I'll be very happy , if
you confirm that PostgreSQL have such improve the speed ...



This is the request :

    SELECT DISTINCT n.username from newlog2000 n , realms r where ( (n.username
like 'loic@%' ) and (r.host = 'toto.fr' ) and (
substr(n.username,strpos(n.username,'@')+1) = r.domain ) ) ;


NOTICE:  QUERY PLAN: (on the new server PostgreSQL 7.0    200 Mhz  64 Mo RAM )

Unique  (cost=253.85..254.07 rows=9 width=24)
  ->  Sort  (cost=253.85..253.85 rows=91 width=24)
        ->  Nested Loop  (cost=0.00..250.90 rows=91 width=24)
              ->  Seq Scan on realms r  (cost=0.00..22.50 rows=10 width=12)
              ->  Index Scan using newuser2000 on newlog2000 n  (cost=0.00..4.74
rows=905 width=12)

NOTICE:  QUERY PLAN: (on the old server PostgreSQL 6.5   450 Mhz   256 Mo RAM)

Unique  (cost=14107.55 rows=7897 width=24)
  ->  Sort  (cost=14107.55 rows=7897 width=24)
        ->  Nested Loop  (cost=14107.55 rows=7897 width=24)
              ->  Index Scan using newuser2000 on newlog2000 n  (cost=9773.30
rows=658 width=12)
              ->  Seq Scan on realms r  (cost=6.59 rows=120 width=12)



                                        Thanks for all , and sorry for my english
....


loic