Postgres function return set

Question

Could someone please let me know the right approach to this problem? I'm not sure if I should be using a function or a view in this case.

I have a temporary table containing some account numbers I need to search for in a query similar to this:

SELECT
    tb1.accountNo,
    tb2.name
FROM
    joinTable jt
    JOIN table1 tb1 ON jt.foreign-key1 = tb1.id
    JOIN table2 tb2 ON jt.foreign-key2 = tb2.id
WHERE tb1.accountNo LIKE '%sub-string';

The account numbers I have in the temporary table are not exact matches, but I need to pull the info from another table. The above query works on a per-account basis, but I want something that will work in a batch.

One approach I've tried is creating a view capturing the joins, but how could I use it with inexact account numbers across multiple accounts at once?

Another possibility is using a function to loop through the temporary table and capture the info from the query. In this regard, my question is: how can I build up a result set or table to return from the function? Will I have to create a temporary table as part of the function and insert records into it as the loop executes? Is there a more elegant way than using a TYPE? Here is an example function I've been experimenting with.

CREATE TYPE accName AS (accountNo VARCHAR(255), name VARCHAR(200));
CREATE OR REPLACE FUNCTION findMissingAccounts()
    RETURNS SETOF accName AS
    $$
    DECLARE
        tempacc RECORD;
    BEGIN
        FOR tempacc IN SELECT * from tempTable LOOP --non-complete account number
            RETURN QUERY -- RETURN QUERY or RETURN NEXT?
                SELECT
                    tb1.accountNo,
                    tb2.name
                FROM
                    joinTable jt
                    JOIN table1 tb1 ON jt.foreign-key1 = tb1.id
                    JOIN table2 tb2 ON jt.foreign-key2 = tb2.id
                WHERE tb1.accountNo LIKE '%tempacc';
         END LOOP;
    RETURN;
    END;
    $$ LANGUAGE 'plpgsql' STABLE

Any help would be greatly appreciated.

Answer 1

Have you tried joining the tables on LIKE condition?

SELECT
    tb1.accountNo,
    tb2.name,
    tt.tempacc
FROM
    joinTable jt
    JOIN table1 tb1 ON jt.foreign-key1 = tb2.id
    JOIN table2 tb2 ON jt.foreign-key2 = tb1.id
    JOIN tempTable tt ON jt.accountNo LIKE '%'||tt.tempacc;