Re: array in function

Hi,

I have a problem with function, where I want to use execute and create sql for it.

My table is:
create table types (
id integer,
type_id character varying,
explain character varying
);

And function:
CREATE or REPLACE FUNCTION hasType(_list character varying[]) RETURNS integer
   LANGUAGE plpgsql
   AS $$

DECLARE hasValue integer;
BEGIN
        EXECUTE 'SELECT 1 FROM types WHERE type_id ANY('|| _list ||') ' INTO hasValue;
        IF hasValue IS NULL THEN
                RETURN 0;
        ELSE
                RETURN 1;
        END IF;                                                
END;
$$;

Executing function with array parameter:
select hasType(ARRAY['E','F','','']);

I got error:
SQL error:
ERROR:  operator is not unique: unknown || character varying[] at character 49
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
QUERY:  SELECT  'SELECT 1 FROM types WHERE type_id ANY('||  $1  ||') '
CONTEXT:  PL/pgSQL function "hastype" line 4 at EXECUTE statement
In statement:
select hasType(ARRAY['E','F','','']);

How to add array in parameter list to sql-sentence?

-kupen


--
Wippies-vallankumous on täällä! Varmista paikkasi vallankumouksen eturintamassa ja liity Wippiesiin heti!
http://www.wippies.com/




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql