Thread: User functions not displayed by \df
Hi, I was working on a database, and then I decided to cut across to a new version and import what I wanted manually. I created my new database and am now bringing across what I want from my .sql text files. These files tend to contain a table definition and any triggers and associated functions. However, in my new database, if I copy a function definition from my editor and paste it into the terminal window, it succeeds in creating it, and it works, but when I do a \df it's not shown?! If I type a new function in by hand into the terminal window, it shows up?! However, once the "ghost" function exists, even retyping it by hand does make it show up in the \df. Even dropping it and recreating it by hand does not make it show up. I've also tried piping the function in via the CLI. Again, it gets created but I still can't see it. Also, I've logged in as the "postgres" user and they can't see the functions as well. But they work?! I'm a bit confused as to why this functionality has changed and what it is that I may be doing/done wrong? Thanks. -- Hadley Willan > Systems Development > Deeper Design Limited. hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463
Strange, looks like \df doesn't show functions that return the type 'Opaque'?! How do I view those type of function returns? Thank You. On Tue, 2002-10-29 at 14:31, Hadley Willan wrote: > Hi, > I was working on a database, and then I decided to cut across to a new > version and import what I wanted manually. I created my new database and > am now bringing across what I want from my .sql text files. These files > tend to contain a table definition and any triggers and associated > functions. > > However, in my new database, if I copy a function definition from my > editor and paste it into the terminal window, it succeeds in creating > it, and it works, but when I do a \df it's not shown?! > > If I type a new function in by hand into the terminal window, it shows > up?! > > However, once the "ghost" function exists, even retyping it by hand does > make it show up in the \df. Even dropping it and recreating it by hand > does not make it show up. > > I've also tried piping the function in via the CLI. Again, it gets > created but I still can't see it. > > Also, I've logged in as the "postgres" user and they can't see the > functions as well. But they work?! > > I'm a bit confused as to why this functionality has changed and what it > is that I may be doing/done wrong? > > Thanks. > -- > Hadley Willan > Systems Development > Deeper Design Limited. > hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463 > -- Hadley Willan > Systems Development > Deeper Design Limited. hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463
Okay, me again. I was readin the psql source and noticed that (df) describeFunction explicitly excludes return type 0 which just happens to be opaque. I did a select on pg_proc and the functions that had the return type 0 happened to be returns OPAQUE. Does anybody know why this would be the case? So problem solved as I now know why my OPAQUE return type functions are vanishing and I have a work around, sorry to bother. H On Tue, 2002-10-29 at 14:41, Hadley Willan wrote: > Strange, looks like \df doesn't show functions that return the type > 'Opaque'?! How do I view those type of function returns? > > Thank You. > > On Tue, 2002-10-29 at 14:31, Hadley Willan wrote: > > Hi, > > I was working on a database, and then I decided to cut across to a new > > version and import what I wanted manually. I created my new database and > > am now bringing across what I want from my .sql text files. These files > > tend to contain a table definition and any triggers and associated > > functions. > > > > However, in my new database, if I copy a function definition from my > > editor and paste it into the terminal window, it succeeds in creating > > it, and it works, but when I do a \df it's not shown?! > > > > If I type a new function in by hand into the terminal window, it shows > > up?! > > > > However, once the "ghost" function exists, even retyping it by hand does > > make it show up in the \df. Even dropping it and recreating it by hand > > does not make it show up. > > > > I've also tried piping the function in via the CLI. Again, it gets > > created but I still can't see it. > > > > Also, I've logged in as the "postgres" user and they can't see the > > functions as well. But they work?! > > > > I'm a bit confused as to why this functionality has changed and what it > > is that I may be doing/done wrong? > > > > Thanks. > > -- > > Hadley Willan > Systems Development > Deeper Design Limited. > > hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463 > > > -- > Hadley Willan > Systems Development > Deeper Design Limited. > hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463 > -- Hadley Willan > Systems Development > Deeper Design Limited. hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463
This is done because most opaque functions are for internal use and not of interest to general users. --------------------------------------------------------------------------- Hadley Willan wrote: > Okay, me again. I was readin the psql source and noticed that (df) > describeFunction explicitly excludes return type 0 which just happens to > be opaque. I did a select on pg_proc and the functions that had the > return type 0 happened to be returns OPAQUE. > > Does anybody know why this would be the case? > > So problem solved as I now know why my OPAQUE return type functions are > vanishing and I have a work around, sorry to bother. > > H > > On Tue, 2002-10-29 at 14:41, Hadley Willan wrote: > > Strange, looks like \df doesn't show functions that return the type > > 'Opaque'?! How do I view those type of function returns? > > > > Thank You. > > > > On Tue, 2002-10-29 at 14:31, Hadley Willan wrote: > > > Hi, > > > I was working on a database, and then I decided to cut across to a new > > > version and import what I wanted manually. I created my new database and > > > am now bringing across what I want from my .sql text files. These files > > > tend to contain a table definition and any triggers and associated > > > functions. > > > > > > However, in my new database, if I copy a function definition from my > > > editor and paste it into the terminal window, it succeeds in creating > > > it, and it works, but when I do a \df it's not shown?! > > > > > > If I type a new function in by hand into the terminal window, it shows > > > up?! > > > > > > However, once the "ghost" function exists, even retyping it by hand does > > > make it show up in the \df. Even dropping it and recreating it by hand > > > does not make it show up. > > > > > > I've also tried piping the function in via the CLI. Again, it gets > > > created but I still can't see it. > > > > > > Also, I've logged in as the "postgres" user and they can't see the > > > functions as well. But they work?! > > > > > > I'm a bit confused as to why this functionality has changed and what it > > > is that I may be doing/done wrong? > > > > > > Thanks. > > > -- > > > Hadley Willan > Systems Development > Deeper Design Limited. > > > hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463 > > > > > -- > > Hadley Willan > Systems Development > Deeper Design Limited. > > hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463 > > > -- > Hadley Willan > Systems Development > Deeper Design Limited. > hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Hi,
I have a query for select statement from a view,inside that query I am calling a function .
When I used EXPLAIN ANALYZE for the first time in the query I get
total time=541.00msec
second time
total time=10.00msec
third time
total time=0.00msec
I wanted to know why is this variation in time.DOse it means that for the first time my performance will be low.
Please help me in understanding this correctly.
--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------
Am Dienstag, 29. Oktober 2002 06:25 schrieb Savita:
> When I used EXPLAIN ANALYZE for the first time in the query I get
> total time=541.00msec
> second time
> total time=10.00msec
> third time
> total time=0.00msec
Explain analyze actually performs the query and times it, so what you see are buffering/caching effects. Thats ok.
Regards,
Mario Weilguni
Hadley Willan <hadley.willan@deeper.co.nz> writes:
> Okay, me again. I was readin the psql source and noticed that (df)
> describeFunction explicitly excludes return type 0 which just happens to
> be opaque. I did a select on pg_proc and the functions that had the
> return type 0 happened to be returns OPAQUE.
> Does anybody know why this would be the case?
I believe the original idea was to avoid cluttering the display with
datatype I/O functions. In 7.3 we've narrowed the scope of this check
so that only functions taking/returning type "cstring" are suppressed.
regards, tom lane