Обсуждение: How can I list the function.

I know the function is there.
What am I doing wrong?

galvdb=# 
galvdb=# \df+ delete_old
                                           List of functions
 Result data type | Schema | Name | Argument data types | Owner | Language | Source code | Description 
------------------+--------+------+---------------------+-------+----------+-------------+-------------
(0 rows)

galvdb=# select delete_old();
 delete_old 
------------
        482
(1 row)

galvdb=# select version();
                                       version                                       
-------------------------------------------------------------------------------------
 PostgreSQL 8.2.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux)
(1 row)

galvdb=# 


The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended
recipient,use, disclosure or copying of this information is prohibited. If you have received this document in error,
pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses
containedin this email or any attachments.
 

That actually depends on the nature of function as well. According to the PostgreSQL manual:

\df [ pattern ]
\df+ [ pattern ]

Lists available functions, together with their argument and return types. If pattern is specified, only functions whose names match the pattern are shown. If the form \df+ is used, additional information about each function, including language and description, is shown.

Note: To look up functions taking argument or returning values of a specific type, use your pager's search capability to scroll through the \df output.

To reduce clutter, \df does not show data type I/O functions. This is implemented by ignoring functions that accept or return type cstring .

I just tried the following at my end with 8.2:

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

and then doing a

\df+ add

did show me the correct output...

You can also try the following to get the same type of results:

select proargtypes, prorettype, prosrc from pg_proc where proname = 'delete_old';

---
Shoaib Mir
EnterpriseDB (www.enterprisedb.com )

On Wednesday 31 January 2007 8:46 pm, Harvey, Allan AC wrote:
> I know the function is there.
> What am I doing wrong?
>
> galvdb=#
> galvdb=# \df+ delete_old
>                                            List of functions
>  Result data type | Schema | Name | Argument data types | Owner | Language
> | Source code | Description
> ------------------+--------+------+---------------------+-------+----------
>+-------------+------------- (0 rows)
>
> galvdb=# select delete_old();
>  delete_old
> ------------
>         482
> (1 row)
>
> galvdb=# select version();
>                                        version
> ---------------------------------------------------------------------------
>---------- PostgreSQL 8.2.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 3.3.3 (SuSE Linux) (1 row)
>
> galvdb=#
>
>
> The material contained in this email may be confidential, privileged or
> copyrighted. If you are not the intended recipient, use, disclosure or
> copying of this information is prohibited. If you have received this
> document in error, please advise the sender and delete the document.
> Neither OneSteel nor the sender accept responsibility for any viruses
> contained in this email or any attachments.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

My guess is it is a permissions issue. The user you are logged in does not
have the privileges necessary to view the function. By default functions have
EXECUTE privileges granted to the ROLE PUBLIC which would explain you being
able to do select delete_old(). See below for more information.
http://www.postgresql.org/docs/8.2/interactive/sql-grant.html
--
Adrian Klaver
aklaver@comcast.net

"Shoaib Mir" <shoaibmir@gmail.com> writes:
> On 2/1/07, Harvey, Allan AC <HarveyA@onesteel.com> wrote:
>> I know the function is there.
>> What am I doing wrong?

> To reduce clutter, \df does not show data type I/O functions. This is
> implemented by ignoring functions that accept or return type cstring.

That could be Allan's problem right there.  The other likely possibility
I can think of is that delete_old() is in a schema that's not in his
search path. "\df+ delete_old" would only show visible functions --- to
see functions in all schemas try "\df+ *.delete_old".

            regards, tom lane

Thank you all,

> I know the function is there.
> What am I doing wrong?

Talking to a 8.2.0 server with a 7.4.5 client.
Sorry for the noise, I sometimes get lost on which versions I have where
of this wonderful software.

Allan


The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended
recipient,use, disclosure or copying of this information is prohibited. If you have received this document in error,
pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses
containedin this email or any attachments.