Date: Mon, 24 Feb 2014 10:42:20 +0200 (EET)
From: Pena Kupen <kupen@wippies.fi>
To: pgsql-sql@postgresql.org
Message-ID: <1750529416.3096311393231340773.JavaMail.kupen@wippies.fi>
Subject: array in function
MIME-Version: 1.0
Content-Type: text/plain; Charset=iso-8859-1; Format=Flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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 integ=
er
    LANGUAGE plpgsql
    AS $$

DECLARE hasValue integer;
BEGIN
	EXECUTE 'SELECT 1 FROM types WHERE type_id ANY('|| _list ||') ' INTO hasVa=
lue;
	IF hasValue IS NULL THEN
		RETURN 0;
	ELSE
		RETURN 1;
	END IF;=09=09=09=09=09=09=09
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 ex=
plicit 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


--=20
Wippies-vallankumous on t=E4=E4ll=E4! Varmista paikkasi vallankumouksen etu=
rintamassa ja liity Wippiesiin heti!
http://www.wippies.com/


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