[SOLVED] Using text in pl/pgsql brings empty set of results

Using text in pl/pgsql brings empty set of results

I am new to pl/pgsql and trying to create a dynamic query. What I have now is a basic combination of parameters for testing. When it works properly, I will gradually add more dynamic parts, to create a dynamic, all-in-one query.

The problem is that this should work, but instead I see an empty search_creator as Data Output in the pgadmin4. This is the code

CREATE  FUNCTION search_creator(creator text)
  RETURNS TABLE(place_id bigint, place_geom geometry, event_name character(200)) AS
$$
BEGIN
    RETURN QUERY EXECUTE 
    'SELECT place.id, place.geom, event.name
     FROM person
     JOIN event_creator ON event_creator.person_id = person.id
     JOIN event ON event.id = event_creator.event_id
     JOIN cep ON cep.event_id = event.id
     JOIN place ON place.id = cep.place_id
     WHERE person.name LIKE $1'
    USING creator;
END;
$$
LANGUAGE plpgsql;

This is how I call the function select search_creator('mike');.

If it helps, in the database, the person.id column is type character(200).

If it helps, when I modify the function to accept int as input and alter the WHERE part to WHERE person.id = $1 , then it works fine. I can see actual results in the pgadmin output.

What is wrong with my text variables? Is it the syntax?

Also, how can I do something like WHERE person.name LIKE '%$1%'?

Solution

If you run

SELECT search_creator('mike');

the function will execute

SELECT ... WHERE person.name LIKE 'mike'

which is identical to

SELECT ... WHERE person.name = 'mike'

Obviously there is no such record.

To prepend and append %, you could use

EXECUTE 'SELECT ...
         WHERE person.name LIKE ' || quote_nullable('%' || creator || '%');