CODEWITHSUNDEEP
postgresqlpattern-matchingset-returning-functions
blue piranha
blue piranha

Reputation: 3874

Error: column does not exist in PostgreSQL

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||'...

Upvotes: 3

Views: 2108

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

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.

Upvotes: 2

Related Questions