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.sp...@ascara.de]
Gesendet: Donnerstag, 8. November 2018 13:18
An: Murtuza Zabuawala <murtuza.zabuaw...@enterprisedb.com>
Cc: pgAdmin Support <pgadmin-supp...@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.zabuaw...@enterprisedb.com]
Gesendet: Donnerstag, 8. November 2018 13:08
An: ascara Software GmbH - Jürgen Spatz
<juergen.sp...@ascara.de<mailto:juergen.sp...@ascara.de>>
Cc: pgAdmin Support
<pgadmin-supp...@postgresql.org<mailto:pgadmin-supp...@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<http://www.enterprisedb.com/>
The Enterprise PostgreSQL Company
[https://drive.google.com/a/enterprisedb.com/uc?id=0B6jGeB3BfKRMV0t4MEp0YnZCTTA&export=download]
On Thu, Nov 8, 2018 at 5:05 PM ascara Software GmbH - Jürgen Spatz
<juergen.sp...@ascara.de<mailto:juergen.sp...@ascara.de>> wrote:
HI,
the Error comes with all PostgreSQL Versions since 9.6
Greetings
Jürgen Spatz
Von: Murtuza Zabuawala
[mailto:murtuza.zabuaw...@enterprisedb.com<mailto:murtuza.zabuaw...@enterprisedb.com>]
Gesendet: Donnerstag, 8. November 2018 11:35
An: ascara Software GmbH - Jürgen Spatz
<juergen.sp...@ascara.de<mailto:juergen.sp...@ascara.de>>
Cc: pgAdmin Support
<pgadmin-supp...@postgresql.org<mailto:pgadmin-supp...@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<http://www.enterprisedb.com/>
The Enterprise PostgreSQL Company
[https://drive.google.com/a/enterprisedb.com/uc?id=0B6jGeB3BfKRMV0t4MEp0YnZCTTA&export=download]
On Thu, Nov 8, 2018 at 2:40 PM ascara Software GmbH - Jürgen Spatz
<juergen.sp...@ascara.de<mailto:juergen.sp...@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
