Thread: Need Assistance: Command to display procedures does not work

On 2/28/24 07:53, Sasmit Utkarsh wrote:
> Hi Postgresql Team,
> 
> Getting error while executing the below \df command to list the 
> procedures/functions. whereas query gives the appropriate results Please 
> assist on how to troubleshoot this.
> 
> [sutkarsh-a@dxctravel.svcs.entsvcs.com@zuccmshcd2 data-exports]$ psql 
> "host=10.166.18.116 port=5432 dbname=shc_data user=shc_admin 
> password=abc123 sslmode=require"
> psql (10.23, server 14.9)
> WARNING: psql major version 10, server major version 14.
>           Some psql features might not work.

The above is not enough of a hint?

Use psql version 14 as:

https://www.postgresql.org/docs/11/release-11.html

Replace system catalog pg_proc's proisagg and proiswindow columns with 
prokind (Peter Eisentraut)

> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 
> 256, compression: off)
> Type "help" for help.
> 
> *shc_data=> \df*
> *ERROR:  column p.proisagg does not exist
> LINE 6:   WHEN p.proisagg THEN 'agg'
>                 ^
> HINT:  Perhaps you meant to reference the column "p.prolang".*
> *
> *
> shc_data=> S*ELECT proname AS function_name,proacl AS privileges FROM 
> pg_proc WHERE proname in 
> ('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');*
>         function_name       | privileges
> ---------------------------+------------
>   sql_select_size_procedure |
>   sql_select_data_procedure |
>   sql_insert_data_procedure |
>   sql_update_data_procedure |
> (4 rows)
> 
> 
> Regards,
> Sasmit Utkarsh
> +91-7674022625

-- 
Adrian Klaver
adrian.klaver@aklaver.com

> On Feb 28, 2024, at 8:53 AM, Sasmit Utkarsh <utkarshsasmit@gmail.com> wrote:
>
> Hi Postgresql Team,
>
> Getting error while executing the below \df command to list the procedures/functions. whereas query gives the
appropriateresults Please assist on how to troubleshoot this. 
>
> [sutkarsh-a@dxctravel.svcs.entsvcs.com@zuccmshcd2 data-exports]$ psql "host=10.166.18.116 port=5432 dbname=shc_data
user=shc_adminpassword=abc123 sslmode=require" 
> psql (10.23, server 14.9)
> WARNING: psql major version 10, server major version 14.
>          Some psql features might not work.

The version difference between your psql client installation and the server installation is the problem.

On 2/28/24 08:52, Sasmit Utkarsh wrote:
> Hi Adrian,
> 
> Thanks for the info, But I have another question: I could see the below 
> functions list with the help on query execution on psql command line
> 
>    shc_data=> S*ELECT proname AS function_name,proacl AS privileges FROM 
> pg_proc WHERE proname in 
> ('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');*
>         function_name       | privileges
> ---------------------------+------------
>   sql_select_size_procedure |
>   sql_select_data_procedure |
>   sql_insert_data_procedure |
>   sql_update_data_procedure |
> (4 rows)
> 
> but when I try to connect to the same db on PgAdmin4, I don't see the 
> list even after refresh. Why is that different?

1) Are you sure you are connecting to correct database.

2) If I followed your previous posts correctly those functions where not 
in the public schema you are looking at in pgAdmin4. They where in the 
shc(?) schema which I am pretty sure is the second schema in the 
Schemas(2) in the object tree.


>  Thet
> image.png
> 
> 
> 
> 
> 
> Regards,
> Sasmit Utkarsh
> +91-7674022625
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/28/24 09:08, Sasmit Utkarsh wrote:
> Thanks Rob
> 
> So which one could I rely on for better information? probably, results 
> shown by PgAdmin4 right? As  I get the below when I try to drop 
> procedure through psql cmd and i see the procedure getting fetched from 
> the query
> 
> shc_data=> SELECT proname AS function_name,proacl AS privileges FROM 
> pg_proc WHERE proname in 
> ('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');
>         function_name       | privileges
> ---------------------------+------------
>   sql_select_size_procedure |
>   sql_select_data_procedure |
> *sql_insert_data_procedure* |
>   sql_update_data_procedure |
> (4 rows)
> 
> shc_data=> DROP PROCEDURE *sql_insert_data_procedure*;
> ERROR:  could not find a procedure named "sql_insert_data_procedure"
> shc_data=>

We've been down this road before, it is a search_path issue.

In psql do:

SHOW search_path;

then do:

\df *.sql_insert_data_procedure

Then see if the schema shown in the second command is in the search_path 
returned by the first command.

> 
> Regards,
> Sasmit Utkarsh
> +91-7674022625
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/28/24 09:13, Sasmit Utkarsh wrote:

Reply to list also
Ccing list
> HI Adrian,
> 
> Related to shc shema, they were part of different host/machine. 
> Currently, I'm seeing the result for this discrepancy on another server 
> machine where we have only a public schema.

1) I doubt that if this is the database you are showing in your pgAdmin4 
screenshot.

2) The information I asked for in my previous post is still relevant and 
needs answering.

3) Add pronamespace::regnamespace to your SELECT query to get the schema 
names for the the functions.

> 
> 
> Regards,
> Sasmit Utkarsh
> +91-7674022625
> 
> 
> On Wed, Feb 28, 2024 at 10:35 PM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 2/28/24 08:52, Sasmit Utkarsh wrote:
>      > Hi Adrian,
>      >
>      > Thanks for the info, But I have another question: I could see the
>     below
>      > functions list with the help on query execution on psql command line
>      >
>      >    shc_data=> S*ELECT proname AS function_name,proacl AS
>     privileges FROM
>      > pg_proc WHERE proname in
>      >
>
('sql_insert_data_procedure','sql_select_data_procedure','sql_select_size_procedure','sql_update_data_procedure');*
>      >         function_name       | privileges
>      > ---------------------------+------------
>      >   sql_select_size_procedure |
>      >   sql_select_data_procedure |
>      >   sql_insert_data_procedure |
>      >   sql_update_data_procedure |
>      > (4 rows)
>      >
>      > but when I try to connect to the same db on PgAdmin4, I don't see
>     the
>      > list even after refresh. Why is that different?
> 
>     1) Are you sure you are connecting to correct database.
> 
>     2) If I followed your previous posts correctly those functions where
>     not
>     in the public schema you are looking at in pgAdmin4. They where in the
>     shc(?) schema which I am pretty sure is the second schema in the
>     Schemas(2) in the object tree.
> 
> 
>      >  Thet
>      > image.png
>      >
>      >
>      >
>      >
>      >
>      > Regards,
>      > Sasmit Utkarsh
>      > +91-7674022625
>      >
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/28/24 10:15, Sasmit Utkarsh wrote:
> Hi Adrian,
> 
> Sorry, Yes I did connect to the correct DB and it's just a test database 
> and there's no shc schema. After giving the pronamespace::regnamespace. 
> I do see it has another schema as test. Thanks a lot for your guidance, 

pgAdmin4 was telling you that with the Schemas(2) in the Object Explorer.

> will make next time i verify with this details first, switching between 
> different DBs and machines got my mind diverted
> 
> image.png
> 
> 
> 
> 
> 
> 
> Regards,
> Sasmit Utkarsh
> +91-7674022625
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com