AW: Error in pgAdmin4 when cast is created

Hi,

 

I tested the Statement and only a small correction is needed

SELECT

                    i.indexrelid,

                    CASE i.indoption[i.attnum - 1]

                    WHEN 0 THEN ARRAY['ASC', 'NULLS LAST']

                    WHEN 1 THEN ARRAY['DESC', 'NULLS FIRST']

                    WHEN 2 THEN ARRAY['ASC', 'NULLS FIRST']

                    WHEN 3 THEN ARRAY['DESC', 'NULLS  ']

                    ELSE ARRAY['UNKNOWN OPTION' || (i.indoption[i.attnum - 1])::text, '']        ç Cast to text then it runs

                    END::text[] AS options,

                    i.attnum,

                    pg_get_indexdef(i.indexrelid, i.attnum, true) as attdef,

                    CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,

                    op.oprname AS oprname,

                               CASE WHEN length(nspc.nspname) > 0 AND length(coll.collname) > 0  THEN

                                 concat(quote_ident(nspc.nspname), '.', quote_ident(coll.collname))

                               ELSE '' END AS collnspname

                FROM (

                      SELECT

                          indexrelid, i.indoption, i.indclass,

                          unnest(ARRAY(SELECT generate_series(1, i.indnatts) AS n)) AS attnum

                      FROM

                          pg_index i

                      WHERE i.indexrelid = 1293689::OID

                ) i

                    LEFT JOIN pg_opclass o ON (o.oid = i.indclass[i.attnum - 1])

                    LEFT OUTER JOIN pg_constraint c ON (c.conindid = i.indexrelid)

                    LEFT OUTER JOIN pg_operator op ON (op.oid = c.conexclop[i.attnum])

                    LEFT JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND a.attnum = i.attnum)

                    LEFT OUTER JOIN pg_collation coll ON a.attcollation=coll.oid

                    LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid

                ORDER BY i.attnum;

 

Greetings

Jürgen Spatz

 

Von: ascara Software GmbH - Jürgen Spatz [mailto:juergen.spatz@ascara.de]
Gesendet: Donnerstag, 8. November 2018 13:18
An: Murtuza Zabuawala <murtuza.zabuawala@enterprisedb.com>
Cc: pgAdmin Support <pgadmin-support@postgresql.org>
Betreff: AW: Error in pgAdmin4 when cast is created

 

HI,

 

I inserted the Bug in the Bug-Tracker also with the correct SQL code, which comes, when I delete the cast

 

Regards

 

Jürgen Spatz

 

Von: Murtuza Zabuawala [mailto:murtuza.zabuawala@enterprisedb.com]
Gesendet: Donnerstag, 8. November 2018 13:08
An: ascara Software GmbH - Jürgen Spatz <juergen.spatz@ascara.de>
Cc: pgAdmin Support <pgadmin-support@postgresql.org>
Betreff: Re: Error in pgAdmin4 when cast is created

 

Hi,

 

Could you please report a bug on https://redmine.postgresql.org/projects/pgadmin4

 

It would be very helpful if you include the sample SQL definition to reproduce the issue on the developer's machine.

 

--

Regards,

Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

 

 

On Thu, Nov 8, 2018 at 5:05 PM ascara Software GmbH - Jürgen Spatz <juergen.spatz@ascara.de> wrote:

HI,

 

the Error comes with all PostgreSQL Versions since 9.6

 

Greetings

Jürgen Spatz

 

Von: Murtuza Zabuawala [mailto:murtuza.zabuawala@enterprisedb.com]
Gesendet: Donnerstag, 8. November 2018 11:35
An: ascara Software GmbH - Jürgen Spatz <juergen.spatz@ascara.de>
Cc: pgAdmin Support <pgadmin-support@postgresql.org>
Betreff: Re: Error in pgAdmin4 when cast is created

 

Hi,

 

What version of PostgreSQL are you using?

 

--

Regards,

Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

 

 

On Thu, Nov 8, 2018 at 2:40 PM ascara Software GmbH - Jürgen Spatz <juergen.spatz@ascara.de> wrote:

Hi All,

 

I found this error in all versions of pgadmin4 on windows. When I create a cast like this:

 

CREATE FUNCTION public.text(smallint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int2out($1));';

CREATE CAST (smallint AS text) WITH FUNCTION public.text(smallint) AS IMPLICIT;

 

Which I need for backward compatibility in our programm then I get the following error when I click on a some tables with the „SQL“ TAB open.

 

 

Error retrieving the information - INTERNAL SERVER ERROR

 

ERROR: operator is not unique: unknown || smallint

LINE 8: ELSE ARRAY['UNKNOWN OPTION' || i.indoption[i.attnum - 1]...

^

HINT: Could not choose a best candidate operator. You might need to add explicit type casts.

 

Here the complete statement from the pglog-file

 

2018-11-08 09:33:14 CET [516]: [1-1] user=,db=,app=pgAdmin 4 - DB:ascarabmpg,client=::1 ERROR:  operator is not unique: unknown || smallint at character 266

2018-11-08 09:33:14 CET [516]: [2-1] user=,db=,app=pgAdmin 4 - DB:ascarabmpg,client=::1 HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

2018-11-08 09:33:14 CET [516]: [3-1] user=,db=,app=pgAdmin 4 - DB:ascarabmpg,client=::1 STATEMENT: 

SELECT

                    i.indexrelid,

                    CASE i.indoption[i.attnum - 1]

                    WHEN 0 THEN ARRAY['ASC', 'NULLS LAST']

                    WHEN 1 THEN ARRAY['DESC', 'NULLS FIRST']

                    WHEN 2 THEN ARRAY['ASC', 'NULLS FIRST']

                    WHEN 3 THEN ARRAY['DESC', 'NULLS  ']

                    ELSE ARRAY['UNKNOWN OPTION' || i.indoption[i.attnum - 1], '']

                    END::text[] AS options,

                    i.attnum,

                    pg_get_indexdef(i.indexrelid, i.attnum, true) as attdef,

                    CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,

                    op.oprname AS oprname,

                               CASE WHEN length(nspc.nspname) > 0 AND length(coll.collname) > 0  THEN

                                 concat(quote_ident(nspc.nspname), '.', quote_ident(coll.collname))

                               ELSE '' END AS collnspname

                FROM (

                      SELECT

                          indexrelid, i.indoption, i.indclass,

                          unnest(ARRAY(SELECT generate_series(1, i.indnatts) AS n)) AS attnum

                      FROM

                          pg_index i

                      WHERE i.indexrelid = 1293689::OID

                ) i

                    LEFT JOIN pg_opclass o ON (o.oid = i.indclass[i.attnum - 1])

                    LEFT OUTER JOIN pg_constraint c ON (c.conindid = i.indexrelid)

                    LEFT OUTER JOIN pg_operator op ON (op.oid = c.conexclop[i.attnum])

                    LEFT JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND a.attnum = i.attnum)

                    LEFT OUTER JOIN pg_collation coll ON a.attcollation=coll.oid

                    LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid

                ORDER BY i.attnum;

 

 

Greetings

 

Jürgen Spatz