Using text in pl/pgsql brings empty set of results

Question

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%'?

score 1 · Accepted Answer · answered Jan 22 '18 at 18:10

1

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 || '%');

answered Jan 22 '18 at 18:10

Laurenz Albe

209,280
17
206
263

No, the problem is that is the record exists. I have a few data in the database and I can see a person with name `mike`. So its something else,I guess the formatting with `EXECUTE`? – slevin Jan 22 '18 at 18:12
I don't believe that. Perhaps a space somewhere, perhaps unprintable characters. You could cast the name to `bytea` and see which bytes are there. If it is `mike`, you should see `\x6d696b65`. – Laurenz Albe Jan 22 '18 at 18:14
Actually all persons have names like `mike johnson`. I dont know if this makes any difference. If I try `WHERE person.name = mike johnson'`, it works fine. Should I change the column type in the database, from `character` to `text` or `varchar` ? – slevin Jan 22 '18 at 18:17
Of course it makes a difference. `'mike' <> 'mike johnson'`. – Laurenz Albe Jan 22 '18 at 18:21
OK, I thought that `LIKE` will ignore this difference. I will try replacing everything with `varchar` then – slevin Jan 22 '18 at 18:22
That won't make a difference. You have to use `_` and `%` for wildcards. Please read the documentation. – Laurenz Albe Jan 22 '18 at 18:36
Yes, I tested with `varchar` and `character` and there is no difference. The problem is the wildcards. I tried a LIKE with `%` but not `quote_nullable`, but I was getting errors, so this is why I asked in the first place. Thanks for your help – slevin Jan 22 '18 at 22:09
Hi again Laurenz, sorry for bothering you, I have one last question. `plpgsql` is really hard and the learning curve is huge. Thanks for your overall help and patient. Do you have any tutorial or series of articles that explain `plpgsql` step by step, using examples? If so, please share so I can study from zero and understand, that would be better than me asking random stuff around and not making progress. The postgresql documentation is somewhat confusing. Thanks again. – slevin Jan 25 '18 at 12:25
1

I don't know any information source except the documentation. – Laurenz Albe Jan 25 '18 at 12:56

Erwin Brandstetter · Answer 2 · 2019-03-29T21:59:09.350

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

Since the function returns a set (SRF, set-returning function), call it with:

SELECT * FROM search_creator('mike');

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

It helps. And it hurts. Like I mentioned before, you do not want to use char(n) (character(n)). Ever. (Also fix your table.) Read this:

Any downsides of using data type "text" for storing strings?

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

Various techniques, but you can simplify with the regular expression match operator ~, which does the same without leading and trailing wildcard - almost; special characters need to be treated separately for either operator:

CREATE FUNCTION search_creator(_creator text)
  RETURNS TABLE(place_id bigint, place_geom geometry, event_name text) AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   'SELECT pl.id, pl.geom, e.name
    FROM   person        pe
    JOIN   event_creator ec ON ec.person_id = pe.id
    JOIN   event         e  ON e.id = ec.event_id
    JOIN   cep           c  ON c.event_id = e.id
    JOIN   place         pl ON pl.id = c.place_id
    WHERE  pe.name ~ $1'  -- note the operator: ~
   USING $1;
END
$func$  LANGUAGE plpgsql;

See:

Follow and read the linked answers and contained references to the manual.

score 0 · Answer 3 · answered Jan 22 '18 at 18:09

below is some working example (Iusednamed IN variables of instead numeric referencing, and format with concat('%',t,'%') to build LIKE query:

t=# create or replace function fa(t text) returns table (c text) as
$$
begin
return query execute format($q$select %L::text where 'test' like %L $q$,t,concat('%',t,'%'));
end;
$$ language plpgsql
;
CREATE FUNCTION
Time: 4.412 ms
t=# select * from fa('es');
 c
----
 es
(1 row)

Using text in pl/pgsql brings empty set of results

3 Answers3

Linked