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;
BEGINEXECUTE '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/