Skip to content
Advertisement

How to handle cases where array is empty in postgresql?

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?

Advertisement

Answer

if-else statement

Pseudocode:

if t_x is empty
  x=null
else
  x=plpy.execute....

cast

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

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement