Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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