2

Trying to create a function that will return multiple rows from a table if a searchTerm exists anywhere inside one of the columns. (I am new to Postgres.)

 CREATE OR REPLACE FUNCTION dts_getProjects(searchTerm TEXT) RETURNS SETOF project
 AS $$
 SELECT credit_br AS Branch, status FROM job_project
 WHERE credit_br LIKE '%'||searchTerm||'%'
 $$
 language 'sql';

I get this error:

 ERROR:  column "searchTerm" does not exist
 LINE 3: ...status FROM job_project WHERE credit_br LIKE '%'||searchTerm||'...
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
blue piranha
  • 3,706
  • 13
  • 57
  • 98

1 Answers1

1

It should work like this:

CREATE OR REPLACE FUNCTION dts_get_projects(_search_term text)
  RETURNS SETOF job_project AS
$func$
SELECT j.*
FROM   job_project j
WHERE  j.credit_br ILIKE '%' || _search_term || '%'
$func$  LANGUAGE sql;

I am using the table type to return whole rows. That's the safe fallback since you did not disclose any data types or table definitions.

I also use ILIKE to make the search case-insensitive (just a guess, you decide).

This only searches the one column credit_br. Your description sounds like you'd want to search all columns (anywhere inside one of the columns). Again, most of the essential information is missing. A very quick and slightly dirty way would be to search the whole row expression converted to text:

...
WHERE  j::text ILIKE '%' || _search_term || '%';
...

Related:

Asides:
Don't use mixed-case identifiers in Postgres if you can avoid it.

Don't quote the language name of functions. It's an identifier.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hi Erwin, isnt begin end required? Since I am getting this error http://pasteboard.co/22ZD5izR.jpg – blue piranha Jun 29 '16 at 15:15
  • @bluepiranha: `BEGIN` and `END` are required for `LANGUAGE plpgsql`, but not for `LANGUAGE sql`. The error you get indicates you are on a very old version of Postgres, where SQL functions did not accept parameter names. You can use `$1` in the function body instead. – Erwin Brandstetter Jun 29 '16 at 15:31