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/