Function to select column values by comparing against comma separated list

Question

I want to compare my comma delimited string with column values.

I did the following, which is not returning the expected result. ll = '"Java,CPP"' is coming dynamically.

create or replace function testing(ll text)
returns void as
$body$
Declare

       foo text[];
       ko text[];
BEGIN
       select unique_code into foo from codings
       where  unique_code = ANY (regexp_split_to_array(ll, '\,'));
       raise info '%',foo;
END;
$body$
Language plpgsql;

I got below error

ERROR:  column "Java,CPP" does not exist
LINE 1: SELECT "Java,CPP"
               ^
QUERY:  SELECT "Java,CPP"
CONTEXT:  PL/pgSQL function testing() line 8 at assignment

The value of ll is coming dynamically like above and now it is return NULL also in the row I have 'Java' and 'Cpp', both values in matching case.

select unique_code from codings;

unique_code
  Java
  Python
  CPP
  C
  Haskell

I also tried trim but not work. The updated code is here:

create or replace function testing(ll text)
returns void as
$body$
Declare

       foo text[];
       --ll text;
       ko text[];
       oo text;
BEGIN
      --oo := replace(ll,'"','');
      raise info '%',regexp_split_to_array(trim(both '"' from ll), '\,');
      ko := regexp_split_to_array(trim(both '"' from ll), '\,');

       ---ll := "CH-PREP,CH-PRMB";
       --select(regexp_split_to_array(ll, '\|')) into ko;
        --foo := array(select unique_key from membership_map);
       select  unique_code into foo from codings where  unique_code = ANY(ko);
       raise info '%',foo;
       --raise info '%', ko;
END;
$body$
Language plpgsql;

Then:

select testing('"Java,CPP"');

ERROR: malformed array literal: "Java"
DETAIL: Array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function testing(text) line 16 at SQL statement

Nick · Accepted Answer · 2018-10-13T22:30:09.530

1

You need to use TRIM to strip the " from your ll value:

select unique_code 
from codings 
where unique_code = ANY (regexp_split_to_array(trim(both '"' from '"Java,CPP"'), '\,'));

Output for your sample data:

unique_code
Java
CPP

SQLFiddle demo

You also have an issue in that you are assigning multiple values in one statement, for which you need to use the ARRAY operator. Change

select unique_code into foo from codings where unique_code = ANY(ko);

to

select array(select unique_code from codings where unique_code = ANY(ko)) into foo;

edited Oct 13 '18 at 22:30

answered Oct 13 '18 at 11:12

Nick

138,499
22
57
95

Now I got the error ERROR: malformed array literal: "Java" DETAIL: Array value must start with "{" or dimension information. CONTEXT: PL/pgSQL function testing(text) line 16 at SQL statement – Rajarshi Das Oct 13 '18 at 12:04
raise info '%',regexp_split_to_array(trim(both '"' from ll), '\,'); ko := regexp_split_to_array(trim(both '"' from ll), '\,'); ---ll := "CH-PREP,CH-PRMB"; --select(regexp_split_to_array(ll, '\|')) into ko; --foo := array(select unique_key from membership_map); select unique_key into foo from membership_map where unique_key = ANY(ko); – Rajarshi Das Oct 13 '18 at 12:08
Please update your question with this updated code. It's too hard to read in a comment. – Nick Oct 13 '18 at 12:09
1

I think you need to change `select unique_code into foo from codings where unique_code = ANY(ko)` to `select array(select unique_code from codings where unique_code = ANY(ko)) into foo;` – Nick Oct 13 '18 at 12:43
Now it is working thank you Please make it updated post to accept it. – Rajarshi Das Oct 13 '18 at 13:03
@RJD I have updated my answer with this change. I was unable to do it earlier as it has been night time... – Nick Oct 13 '18 at 22:31

Erwin Brandstetter · Answer 2 · 2018-10-13T15:14:55.997

You got the first error:

ERROR:  column "Java,CPP" does not exist

... for missing single quotes around your string literal in the function call. Like

SELECT testing("Java,CPP");  -- missing ''!

You probably want to trim those double quotes from the input, so the call should really be:

SELECT testing('Java,CPP');

Are PostgreSQL column names case-sensitive?

With the call fixed, the lurking error from a data type mismatch in the function body strikes. You try to assign unique_code text to foo text[]:

ERROR: malformed array literal: "Java"

For example, this would work:

CREATE OR REPLACE FUNCTION testing(_ll text[])  -- taking array of text
  RETURNS text[] AS
$func$
DECLARE
   foo text[];
BEGIN
   foo := ARRAY(  -- array constructor
      SELECT unique_code 
      FROM   codings
      WHERE  unique_code = ANY(_ll)
      );

   RETURN foo;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT testing('{Java,CPP}');  -- note syntax of array literal

See:

Pass array literal to PostgreSQL function

Passing a valid array literal also avoids the expensive and unnecessary regexp_split_to_array() call. (Regexp functions are powerful but comparatively expensive.)

Or the same as VARIADIC function:

CREATE OR REPLACE FUNCTION testing(VARIADIC _ll text[])
...  -- rest as above

You can still pass array literals (or true array values) with the keyword VARIADIC in the call:

SELECT testing(VARIADIC '{Java,CPP}'); -- with array

Or you can pass a list of element values (up to 100):

SELECT testing('Java','CPP');          -- with list of values

CREATE OR REPLACE FUNCTION testing(_ll text)
  RETURNS text[] AS
$func$
DECLARE
   foo text[];
BEGIN
   foo := ARRAY(
      SELECT unique_code 
      FROM   codings
      WHERE  unique_code = ANY(string_to_array(trim(_ll, '"'), ','))
      );

   RETURN foo;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT testing('"Java,CPP"'); -- even with enclosing double quotes

Aside: rather don't use a cryptic ll as parameter name. Easily misread as 11 ...

Function to select column values by comparing against comma separated list

2 Answers2

Linked