From: | Pena Kupen <kupen(at)wippies(dot)fi> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | array in function |
Date: | 2014-02-24 08:42:20 |
Message-ID: | 1750529416.3096311393231340773.JavaMail.kupen@wippies.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2014-02-24 08:55:10 | Re: array in function |
Previous Message | amul sul | 2014-02-24 06:47:28 | SRF_RETURN_NEXT Error: rows returned by function are not all of the same row type |