User functions not displayed by \df

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