Thread: Postgres array cast

Postgres array cast

From
jco@cornelius-olsen.dk
Date:
Hi,

I'm using Zeos Database Objects to access the database. As it turns out,
it is not able to read array fields from the database.
I was wondering whether there is any way to convert an array to another
type (e.g. text).

I've tried using
        select my_array_field::text
and
        select text(my_array_field)

But it can't do that. Does anyone know of another compatible type or
whether it is possible to write a type casting operator?

 select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

Regards
/Jørn Cornelius Olsen

Re: Postgres array cast

From
jco@cornelius-olsen.dk
Date:
No matter.

I found a way. Not exactly a reguar typecast, but it works for single
dimensional integer arrays (which is what I need):

drop function to_text(integer[]);
create function to_text(integer[]) returns text as '
declare
  a alias for $1;
  r text=\'{\';
  i integer:=1;
begin
  while a[i] is not null loop
        if i>1 then
                r:=r||\',\';
        end if;
    r:=r||text(a[i]);
        i:=i+1;
  end loop;
  return r||\'}\';
end;
' language 'plpgsql'
with (iscachable);

Regards
/Jørn Cornelius Olsen





jco@cornelius-olsen.dk
Sent by: pgsql-general-owner@postgresql.org
27-12-2002 01:40


        To:     pgsql-general@postgresql.org
        cc:
        Subject:        [GENERAL] Postgres array cast


Hi,

I'm using Zeos Database Objects to access the database. As it turns out,
it is not able to read array fields from the database.
I was wondering whether there is any way to convert an array to another
type (e.g. text).

I've tried using
        select my_array_field::text
and
        select text(my_array_field)

But it can't do that. Does anyone know of another compatible type or
whether it is possible to write a type casting operator?

 select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

Regards
/Jørn Cornelius Olsen

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html