pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Need Assistance: Command to display procedures does not work
11+ messages / 3 participants
[nested] [flat]

* Need Assistance: Command to display procedures does not work
@ 2024-02-28 15:53 Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:03 ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 16:55 ` Re: Need Assistance: Command to display procedures does not work Rob Sargent <robjsargent@gmail.com>
  0 siblings, 2 replies; 11+ messages in thread

From: Sasmit Utkarsh @ 2024-02-28 15:53 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.
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 existLINE 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

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
@ 2024-02-28 16:03 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 16:52   ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Adrian Klaver @ 2024-02-28 16:03 UTC (permalink / raw)
  To: Sasmit Utkarsh <utkarshsasmit@gmail.com>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:03 ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-28 16:52   ` Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 17:05     ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sasmit Utkarsh @ 2024-02-28 16:52 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org

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?

[image: image.png]





Regards,
Sasmit Utkarsh
+91-7674022625


On Wed, Feb 28, 2024 at 9:33 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> 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
>
>

Attachments:

image.png (image/png, 70.6K) download view image
^ permalink raw reply [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:03 ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 16:52   ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
@ 2024-02-28 17:05     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 17:20       ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Adrian Klaver @ 2024-02-28 17:05 UTC (permalink / raw)
  To: Sasmit Utkarsh <utkarshsasmit@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:03 ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 16:52   ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 17:05     ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-28 17:20       ` Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 18:15         ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Adrian Klaver @ 2024-02-28 17:20 UTC (permalink / raw)
  To: Sasmit Utkarsh <utkarshsasmit@gmail.com>; +Cc: pgsql-general

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






^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:03 ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 16:52   ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 17:05     ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 17:20       ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-28 18:15         ` Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 18:46           ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sasmit Utkarsh @ 2024-02-28 18:15 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general

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, will
make next time i verify with this details first, switching between
different DBs and machines got my mind diverted

[image: image.png]






Regards,
Sasmit Utkarsh
+91-7674022625


On Wed, Feb 28, 2024 at 10:50 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> 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
>
>

Attachments:

image.png (image/png, 98.0K) download view image
^ permalink raw reply [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:03 ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 16:52   ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 17:05     ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 17:20       ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 18:15         ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
@ 2024-02-28 18:46           ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Adrian Klaver @ 2024-02-28 18:46 UTC (permalink / raw)
  To: Sasmit Utkarsh <utkarshsasmit@gmail.com>; +Cc: pgsql-general

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






^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
@ 2024-02-28 16:55 ` Rob Sargent <robjsargent@gmail.com>
  2024-02-28 17:08   ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Rob Sargent @ 2024-02-28 16:55 UTC (permalink / raw)
  To: Sasmit Utkarsh <utkarshsasmit@gmail.com>; +Cc: pgsql-general@lists.postgresql.org



> 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 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 version difference between your psql client installation and the server installation is the problem.







^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:55 ` Re: Need Assistance: Command to display procedures does not work Rob Sargent <robjsargent@gmail.com>
@ 2024-02-28 17:08   ` Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 17:13     ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sasmit Utkarsh @ 2024-02-28 17:08 UTC (permalink / raw)
  To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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=>

Regards,
Sasmit Utkarsh
+91-7674022625


On Wed, Feb 28, 2024 at 10:25 PM Rob Sargent <robjsargent@gmail.com> wrote:

>
>
> > 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 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 version difference between your psql client installation and the
> server installation is the problem.
>
>
>

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:55 ` Re: Need Assistance: Command to display procedures does not work Rob Sargent <robjsargent@gmail.com>
  2024-02-28 17:08   ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
@ 2024-02-28 17:13     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-28 17:19       ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Adrian Klaver @ 2024-02-28 17:13 UTC (permalink / raw)
  To: Sasmit Utkarsh <utkarshsasmit@gmail.com>; Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Need Assistance: Command to display procedures does not work
  2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 16:55 ` Re: Need Assistance: Command to display procedures does not work Rob Sargent <robjsargent@gmail.com>
  2024-02-28 17:08   ` Re: Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
  2024-02-28 17:13     ` Re: Need Assistance: Command to display procedures does not work Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-28 17:19       ` Sasmit Utkarsh <utkarshsasmit@gmail.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Sasmit Utkarsh @ 2024-02-28 17:19 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-general@lists.postgresql.org

Hello Adria/Rob,

Please note, 'shc' schema was part of a different machine/host. For now, I
see issue on another host machine which i connect using psql and PgAdmin4
and get different result on both and we have only public schema here on
this machine

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=> SHOW search_path
shc_data->
shc_data-> ^C
shc_data=> SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

shc_data=>

Regards,
Sasmit Utkarsh
+91-7674022625


On Wed, Feb 28, 2024 at 10:43 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> 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
>
>

^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2024-02-28 18:46 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-28 15:53 Need Assistance: Command to display procedures does not work Sasmit Utkarsh <utkarshsasmit@gmail.com>
2024-02-28 16:03 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-28 16:52   ` Sasmit Utkarsh <utkarshsasmit@gmail.com>
2024-02-28 17:05     ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-28 17:20       ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-28 18:15         ` Sasmit Utkarsh <utkarshsasmit@gmail.com>
2024-02-28 18:46           ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-28 16:55 ` Rob Sargent <robjsargent@gmail.com>
2024-02-28 17:08   ` Sasmit Utkarsh <utkarshsasmit@gmail.com>
2024-02-28 17:13     ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-28 17:19       ` Sasmit Utkarsh <utkarshsasmit@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox