CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
Deepak Bandela
Deepak Bandela

Reputation: 163

How to pass field values as arguement to a stored procedure

I have a DB table in which one column have an array of values. Lets assume

Student table
"Name" character(10),
"Subject_studying" text[],

I created a stored procedure as follows

CREATE OR REPLACE FUNCTION ArrayLike(vals text[], v text) RETURNS integer AS $$
DECLARE
  str text;
BEGIN
  v := replace(replace(v, '%', '.*'), '_', '.');
  FOREACH str IN ARRAY vals LOOP
    IF str ~* v THEN
      RETURN 1;
    END IF;
  END LOOP;
  RETURN 0;
END; $$ 
LANGUAGE PLPGSQL;

Which returns all the subjects that I'm looking for. How do I pass the parameter of the subjects to the stored procedure.

My query looks like

SELECT Name FROM Student WHERE ArrayLike('Subject_studying', 'english') = 1

The query is giving me an error 

 ERROR:  FOREACH expression must not be null
CONTEXT:  PL/pgSQL function Arraylike(text[],text) line 6 at FOREACH over array
********** Error **********

I guess the parameter 'Subject_studying' is not sent as a value but as a simple string. How do we pass the values in that field to the stored procedure?

Upvotes: 0

Views: 503

Answers (1)

Patrick
Patrick

Reputation: 32199

The error is caused by passing the column name subject_studying as a string, as you already noted. Unquote it to make the error go away.

But you actually do not have to create your own function for this. You can use the built-in ANY operator:

SELECT Name FROM Student WHERE 'english' = ANY(subject_studying);

Upvotes: 1

Related Questions