3
Create table t1 ( xcheck varchar[], name text );

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
  RETURNS record AS
  DECLARE xrc as record;

  execute 'select name from t1 where xcheck @> ''' || xch :: varchar[] || ''';' into xrc; 
return xrc;
END;

In table t1 having array value and text. I am calling this(fn_acgroup) function with array as its parameter value. In that function, I am checking the array value with passed array if it matches then I will return the name. In that case I get error as operator does not exist: text || character varying[]. Am I doing anything wrong? How can I solve this problem?

3

You manage to get it wrong in just too many ways. To begin with, there is no need for dynamic SQL at all.

The best way to "solve this problem" is probably to start with the basics. Read the excellent manual about CREATE FUNCTION, PL/pgSQL and SQL functions.

A PL/pgSQL function could look like this (one of several variants):

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[], OUT xrc) AS
$func$
BEGIN
SELECT INTO xrc name FROM t1 WHERE xcheck @> xch; 
END
$func$ LANGUAGE plpgsql

Or, simpler as plain SQL function:

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
  RETURNS text AS
$func$
SELECT name FROM t1 WHERE xcheck @> $1; 
$func$ LANGUAGE sql;

Or study some of the plpgsql examples that have been posted here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.