I have a function written in plpythonu.
CREATE OR REPLACE FUNCTION temp(t_x integer[])
RETURNS void AS
$BODY$
.
.
.
x=plpy.execute("""select array_to_string(select id from A where id=any(array%s) ), ',')"""%t_x)
On some cases when t_x is empty I get an error:
ERROR: cannot determine type of empty array
How do I fix it?
Pseudocode:
if t_x is empty
x=null
else
x=plpy.execute....
x=plpy.execute("""select array_to_string(select id from A where id=any(array%s::integer[]) ), ',')"""%t_x)
Why cast help?
postgres=# select pg_typeof(array[1,2]);
pg_typeof
-----------
integer[]
array[1,2] has type integer[]
postgres=# select array[];
ERROR: cannot determine type of empty array
LINE 1: select array[];
array[] has no type.
postgres=# select pg_typeof(array[]::integer[]);
pg_typeof
-----------
integer[]
array[]::integer[] has type integer[]
Maybe this can help someone in future. Table function may receive NULL or Empty Array in that case (equivalent to ALL):
CREATE OR REPLACE FUNCTION temp(t_x integer[])
RETURNS void AS
$BODY$
.
.
.
x=plpy.execute("""select array_to_string(select id from A where id=any(array%s) or cardinality(coalesce(array%s,array[]::integer[])) = 0), ',')"""%t_x)
I tried to create an empty array, then I got the same error as shown below:
postgres=# SELECT ARRAY[];
ERROR: cannot determine type of empty array
LINE 1: SELECT ARRAY[];
^
HINT: Explicitly cast to the desired type, for example ARRAY[]::integer[].
So, I set ::VARCHAR[] to ARRAY[], then I could create an empty array as shown below. *My answer explains how to create an empty array with other ways:
postgres=# SELECT ARRAY[]::VARCHAR[];
array
-------
{}
(1 row)
