Search code examples
postgresqlpattern-matchingplpgsql

How to use SIMILAR TO with variables

I have a function with a SELECT using a SIMILAR TO expression with a variable and I don't know how to do it:

DECLARE pckg_data cl_data;
DECLARE contacts contacts_reg%ROWTYPE;
DECLARE sim_name varchar;
 BEGIN
SELECT client_reg._name, 
       client_reg.last_name, 
       client_reg.id_card, 
       client_reg.address
INTO pckg_data
FROM client_reg WHERE(client_reg._name = (cl_name ||' '|| cl_lastname));
RETURN NEXT pckg_data;

SELECT ('%'||cl_name || ' ' || cl_lastname ||'%') INTO sim_name;

FOR contacts IN SELECT contacts_reg.id 
FROM contacts_reg, contactscli_asc, client_reg
WHERE(contacts_reg._name SIMILAR TO sim_name) LOOP
    SELECT client_reg._name, client_reg.last_name, client_reg.id_card,
                   client_reg.address, client_reg.id
    INTO pckg_data
    FROM client_reg, contactscli_asc WHERE(contactscli_asc.contact = contacts.id 
    AND client_reg.id = contactscli_asc.client);
END LOOP;

END;
Solution

  • Your query that feeds the loop has CROSS JOIN over three (!) tables. I removed the last two on the notion that they are not needed. One of them is repeated in the body of the loop. Also consider @kgrittn's note on CROSS JOIN.

    In the body of the loop you select data into a variable repeatedly, which does nothing. I assume you want to return those rows - that's what my edited version does, anyway.

    I rewrote the LOOP construct with a simple SELECT with RETURN QUERY, because that's much faster and simpler.

    Actually, I rewrote everything in a way that would make sense. What you presented is still incomplete (missing function header) and syntactically and logically a mess.

    This is an educated guess, no more:

    CREATE FUNCTION very_secret_function_name(cl_name varchar, cl_lastname varchar)
  RETURNS TABLE (name varchar, last_name varchar,
                 id_card int, address varchar, id int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sim_name varchar := (cl_name ||' '|| cl_lastname);
BEGIN
   RETURN QUERY
   SELECT c._name, c.last_name, c.id_card, c.address, NULL::int
           -- added NULL for an id to match the second call
   FROM   client_reg c
   WHERE  c._name = _sim_name;

   RETURN QUERY
   SELECT c._name, c.last_name, c.id_card, c.address, r.id
   FROM   client_reg      c
   JOIN   contactscli_asc a ON a.client = c.id
   JOIN   contacts_reg    r ON r.id = a.contact
   WHERE  r._name LIKE ('%' || _sim_name || '%');
END
$func$;

    Else, consider the features used.

    Some advise: