Re: IS IT POSSIBLE TO CREATE INDEX ON VIEW IN POSTGRE

Hi all,

I'm beginning in PosgreSQL and until now I may say that it is incredible ...

I have some sort asks ... and ... I hope be helped here.
so, first of all I should say that I am coming from Interbase, then:

1: There is any Postgres Language to InterBase like pgPL-SQL???

2: There is any tools to convert from Interbase to Postgres???

3: There is any Case tool like ER-Win that is compatible with Postgres??

4: The Interbase's DOMAIN is the same thing as Postgres's TYPES ?? And could
I create a TYPE without set an INPUT/OUTPUT function??
(ex. CREATE DOMAIN myDomain NUMERIC(12,2)

TIA

Roberto de Amorim - +55 48 346-2243
software engineer at SmartBit Software
Delphi and Interbase consultant

Roberto (SmartBit) wrote:
> Hi all,
>
> I'm beginning in PosgreSQL and until now I may say that it is incredible ...
>
> I have some sort asks ... and ... I hope be helped here.
> so, first of all I should say that I am coming from Interbase, then:
>
> 1: There is any Postgres Language to InterBase like pgPL-SQL???

Yes, PL/pgSQL.

>
> 2: There is any tools to convert from Interbase to Postgres???

Yes, see:

http://techdocs.postgresql.org

>
> 3: There is any Case tool like ER-Win that is compatible with Postgres??

No, but we do support ER-Win. Dbaccess is nice at dbaccess.org.

> 4: The Interbase's DOMAIN is the same thing as Postgres's TYPES ?? And could
> I create a TYPE without set an INPUT/OUTPUT function??
> (ex. CREATE DOMAIN myDomain NUMERIC(12,2)

We have DOMAIN in 7.3beta.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Hi all

Like I told before I'm beginning to work with Postgre... (for while living
in a paradise)
My next step is work with ASTA ... so, I'd like to know if anyone have an
AstaServer for Postgres? ... it may be using Zeos components connection or
any other...

TIA

Roberto de Amorim - +55 48 346-2243
software engineer at SmartBit Software
Delphi and Interbase consultant

hi all

How could I do to get de User Tables, System Tables, Stored Procedures,
Trigger, Views, etc???
I'd like to do the same as pgAdmin ... using a TreeView at left and at rigth
I'll show the items...

TIA

Roberto de Amorim

On 10 Oct 2002 at 5:31, Roberto (SmartBit) wrote:

> How could I do to get de User Tables, System Tables, Stored Procedures,
> Trigger, Views, etc???
> I'd like to do the same as pgAdmin ... using a TreeView at left and at rigth
> I'll show the items...

It's all there in pg meta data. Tora is one way I have discovered today to get
that information easily..

Bye
Shridhar

--
QOTD: Flash! Flash! I love you! ...but we only have fourteen hours to save
the earth!

>
> > How could I do to get de User Tables, System Tables, Stored Procedures,
> > Trigger, Views, etc???
> > I'd like to do the same as pgAdmin ... using a TreeView at left and at
rigth
> > I'll show the items...
>
> It's all there in pg meta data. Tora is one way I have discovered today to
get
> that information easily..
>

Sorry my ignorance, but, what is "Tora"???
How do I do to get the pg meta data using delphi??

I'm trying to do something like pgAdmim ....

Roberto de Amorim

Why not just grab the source code from pgAdmin and look at how it does
the sql calls. Actually you can probably just do psql -E and get the
source of the \d commands

Robert Treat

On Thu, 2002-10-10 at 10:57, Roberto (SmartBit) wrote:
> >
> > > How could I do to get de User Tables, System Tables, Stored Procedures,
> > > Trigger, Views, etc???
> > > I'd like to do the same as pgAdmin ... using a TreeView at left and at
> rigth
> > > I'll show the items...
> >
> > It's all there in pg meta data. Tora is one way I have discovered today to
> get
> > that information easily..
> >
>
> Sorry my ignorance, but, what is "Tora"???
> How do I do to get the pg meta data using delphi??
>
> I'm trying to do something like pgAdmim ....
>
> Roberto de Amorim
>

On 10 Oct 2002 at 7:57, Roberto (SmartBit) wrote:

> Sorry my ignorance, but, what is "Tora"???

It's an application which stands for Tool for Oracle developers

> How do I do to get the pg meta data using delphi??

I guess you have to fire direct queries against PG metadata.

> I'm trying to do something like pgAdmim ....

Well there are other applications which do similar things. I remember seeing
couple of apps. in Qt and on windows to dig in PG metadata. Google around for
pgaccess and windows.

HTH

Bye
Shridhar

--
Ralph's Observation: It is a mistake to let any mechanical object realise that
you are in a hurry.

Yep, I've got my own custom Asta server up and running using the Zeos
components. I'm not too thrilled with Zeos though, and I've been
contemplating creating my own set. *If* I ever find the time.

If you have any questions about it, feel free to send me private email.

Greg

----- Original Message -----
From: "Roberto (SmartBit)" <roberto(at)smartbit(dot)inf(dot)br>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, October 09, 2002 3:32 PM
Subject: [GENERAL] Asta with PostgreSQL

> Hi all
>
> Like I told before I'm beginning to work with Postgre... (for while living
> in a paradise)
> My next step is work with ASTA ... so, I'd like to know if anyone have an
> AstaServer for Postgres? ... it may be using Zeos components connection
or
> any other...
>
> TIA
>
> Roberto de Amorim - +55 48 346-2243
> software engineer at SmartBit Software
> Delphi and Interbase consultant
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

Gyorgy Molnar wrote:
> plase take a look to the following small sample
>
> CREATE FUNCTION test(TEXT) RETURNS TEXT AS '
> DECLARE
> cmd TEXT;
> BEGIN
> RETURN cmd || ''Hello'';
> END;
> ' LANGUAGE 'plpgsql';
>
> When I execute the function
>
> select test('');
> test
> ------
>
> (1 row)
>
> I got an empty string for result. I think I got this result because the
> string concatenation ("||") was created with "isstrict" flag. In this case
> it will give back NULL object if one of the arguments was NULL object.
>
> In most of the cases I think it is ok, but specially for the string
> concatenation is not really convinient.
>
> Is this feature will change in the future? Any "smart" solution for this
> problem? - I cannot use the IF..ENDIF statement before each string
> concatenation, I have too many in my code.

The function in incorrect. cmd is NULL because you haven't used $1.
Try:

test=> CREATE FUNCTION test2(TEXT) RETURNS TEXT AS '
test'> BEGIN
test'> RETURN $1 || ''Hello'';
test'> END;
test'> ' LANGUAGE 'plpgsql';
CREATE FUNCTION
test=> select test2('a');
test2
--------
aHello
(1 row)

test=> select test2('');
test2
-------
Hello
(1 row)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

"Gyorgy Molnar" <yuri(at)powercom(dot)com(dot)sg> writes:
> I got an empty string for result. I think I got this result because the
> string concatenation ("||") was created with "isstrict" flag. In this case
> it will give back NULL object if one of the arguments was NULL object.

Actually, you got back a NULL, not an empty string. There is a big
difference (even if Oracle confuses the two concepts).

> Is this feature will change in the future?

No. That's what the SQL standard says to do, and that's what we plan to
keep doing. You can make your own nonstandard concatenation function
if you feel like it, though.

regards, tom lane

plase take a look to the following small sample

CREATE FUNCTION test(TEXT) RETURNS TEXT AS '
DECLARE
cmd TEXT;
BEGIN
RETURN cmd || ''Hello'';
END;
' LANGUAGE 'plpgsql';

When I execute the function

select test('');
test
------

(1 row)

I got an empty string for result. I think I got this result because the
string concatenation ("||") was created with "isstrict" flag. In this case
it will give back NULL object if one of the arguments was NULL object.

In most of the cases I think it is ok, but specially for the string
concatenation is not really convinient.

Is this feature will change in the future? Any "smart" solution for this
problem? - I cannot use the IF..ENDIF statement before each string
concatenation, I have too many in my code.

Best Regards,
Yuri

> > I got an empty string for result. I think I got this result because the
> > string concatenation ("||") was created with "isstrict" flag. In this
case
> > it will give back NULL object if one of the arguments was NULL object.
>
> Actually, you got back a NULL, not an empty string. There is a big
> difference (even if Oracle confuses the two concepts).
>
> > Is this feature will change in the future?
>
> No. That's what the SQL standard says to do, and that's what we plan to
> keep doing. You can make your own nonstandard concatenation function
> if you feel like it, though.

I see. I think the best way if I make my own concatenation operator and use
it as a temporary fix. In the long-run the best way to avoid this kind of
problems by desing at the beginning of the projects.
Thank you for your help.

Best Regards,
Yuri

Hi All,

I just wanted to know is it possible to create indexes on view and update
statement in postgres.
--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Hi all,

I have created the indexs on some table but still I am not getting the
performance as required.Some sql statements are taking very long time to
execute.

Is there any way to analyse the performance in postgre,I am able to analyses
it in MSSQL.

>
> --
> Best Regards
> - Savita
> ----------------------------------------------------
> Hewlett Packard (India)
> +91 80 2051288 (Phone)
> 847 1288 (HP Telnet)
> ----------------------------------------------------
>

On 28 Oct 2002 at 11:52, Savita wrote:

> Hi all,
>
> I have created the indexs on some table but still I am not getting the
> performance as required.Some sql statements are taking very long time to
> execute.
>
> Is there any way to analyse the performance in postgre,I am able to analyses
> it in MSSQL.

What does explain tells about the query?

Bye
Shridhar

--
Robot, n.: University administrator.

Using explain I am not able to get the detailed information.

I want to know abotu optimizing the postgre.

Shridhar Daithankar wrote:

> On 28 Oct 2002 at 11:52, Savita wrote:
>
> > Hi all,
> >
> > I have created the indexs on some table but still I am not getting the
> > performance as required.Some sql statements are taking very long time to
> > execute.
> >
> > Is there any way to analyse the performance in postgre,I am able to analyses
> > it in MSSQL.
>
> What does explain tells about the query?
>
> Bye
> Shridhar
>
> --
> Robot, n.: University administrator.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

On Mon, 28 Oct 2002, Savita wrote:

> Using explain I am not able to get the detailed information.

What information are you looking to get?

Hi,

I was looking for something like this.

InMssql when I use a query like selece a,b from some table where
a.x=b.y,then if a is having say 30000 records and b is having 4 records then
it will compare all the 30000 records with the 4 records one by one,but if I
make it where b.y=a.x then it will be faster.

I know with index I could increase the performance by 50 %,but what other
possibilities are there to improve the performance

Stephan Szabo wrote:

> On Mon, 28 Oct 2002, Savita wrote:
>
> > Using explain I am not able to get the detailed information.
>
> What information are you looking to get?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Hi,
i try to insert some information on database but when i arrive near 60 000
insertion i lose some information
how i can know why i lose information?
Is there a limit on database?

thanks
Florian

On Mon, Oct 28, 2002 at 10:18:53AM +0100, Florian Litot wrote:
> Hi,
> i try to insert some information on database but when i arrive near 60 000
> insertion i lose some information
> how i can know why i lose information?
> Is there a limit on database?

There is no limit. You are obviously misunderstanding something. Please
supply the list with more details.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

no raply for this question is this question is not making any sense.

Savita wrote:

> Hi All,
>
> I just wanted to know is it possible to create indexes on view and update
> statement in postgres.
> --
> Best Regards
> - Savita
> ----------------------------------------------------
> Hewlett Packard (India)
> +91 80 2051288 (Phone)
> 847 1288 (HP Telnet)
> ----------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

On 28 Oct 2002 at 17:04, Savita wrote:

> no raply for this question is this question is not making any sense.

OK, I will try..

>
> Savita wrote:
>
> > Hi All,
> >
> > I just wanted to know is it possible to create indexes on view and update
> > statement in postgres.

In postgres, views are implemented using rules. So for updating views you might
want to right your own rule. So with little coding it should be possible to
have updatable views, though postgresql does not support them out of box.

About creating index on a view, my experiments tell me it's not possible. It
has to be relation. But given that indexes are usually used for making searches
faster, you might consider creating indexes on original table based on query
that forms the view. I underastand this is not a complete solution for your
problem, but should help a bit..

HTH

Bye
Shridhar

--
Ambiguity: Telling the truth when you don't mean to.

When i try to insert a lot of information the database doesn't recorder
when i create the database i don't configure anything i just tape createdb
i must configure something? the size of the database or the size of the buffer?

thanks

Florian Litot <flitot(at)besancon(dot)sema(dot)slb(dot)com> writes:

> When i try to insert a lot of information the database doesn't
> recorder when i create the database i don't configure anything i
> just tape createdb i must configure something? the size of the
> database or the size of the buffer?

This is a pretty uninformative message--we can't really help you
without more info. What exactly are you doing, and what error message
do you get?

-Doug

AFAICT this is exactly the type of output explain or explain analyze
will tell you. Please go back and reread the docs on these commands and
if you don't understand how to interpret the info post back and we'll
try to help more.

Robert Treat

On Mon, 2002-10-28 at 03:10, Savita wrote:
> Hi,
>
> I was looking for something like this.
>
> InMssql when I use a query like selece a,b from some table where
> a.x=b.y,then if a is having say 30000 records and b is having 4 records then
> it will compare all the 30000 records with the 4 records one by one,but if I
> make it where b.y=a.x then it will be faster.
>
> I know with index I could increase the performance by 50 %,but what other
> possibilities are there to improve the performance
>
> Stephan Szabo wrote:
>
> > On Mon, 28 Oct 2002, Savita wrote:
> >
> > > Using explain I am not able to get the detailed information.
> >
> > What information are you looking to get?
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>
> --
> Best Regards
> - Savita
> ----------------------------------------------------
> Hewlett Packard (India)
> +91 80 2051288 (Phone)
> 847 1288 (HP Telnet)
> ----------------------------------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Hi,

I'd like the ask the following questions:

1.. What is the reason to use the encode/decode rules shown in table 3-7 (PG docs) for the "bytea" datatype. I think the "hex string" SQL99 use, looks much more convinient. Is there any particular reason not to follow SQL99 in this case???
Advantages of the hex string:
- the leng of the buffer is known.
- easier, (faster?) conversion
- no extreme cases (all of the bytes are 0 -> '\\000')
2.. For a new new own defined type, is it possible to define the maximum length of the object just like TEXT(N), CHAR(N) do it?
Kind Regards,
Yuri