I want to have my pl/pgsql script output to the screen

Question

I have the following script that I want output to the screen from.

CREATE OR REPLACE FUNCTION randomnametest() RETURNS integer AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM my_table LOOP
        SELECT levenshtein('mystring',lower('rec.Name')) ORDER BY levenshtein;
    END LOOP;

    RETURN 1;
END;
$$ LANGUAGE plpgsql;

I want to get the output of the levenshein() function in a table along with the rec.Name. How would I do that? Also, it is giving me an error about the line where I call levenshtein(), saying that I should use perform instead.

Solution 1

If you want the output from a plpgsql function like the title says:

CREATE OR REPLACE FUNCTION randomnametest(_mystring text)
  RETURNS TABLE (l_dist int, name text) AS
$BODY$
BEGIN
    RETURN QUERY
    SELECT levenshtein(_mystring, lower(t.name)), t.name
    FROM   my_table t
    ORDER  BY 1;
END;
$$ LANGUAGE plpgsql;

• Declare the table with RETURNS TABLE.
• Use RETURN QUERY to return records from the function.
• Avoid naming conflicts between column names and OUT parameters (from the RETURNS TABLE clause) by table-qualifying column names in queries. OUT parameters are visible everywhere in the function body.
• I made the string to compare to a parameter to the function to make this more useful.

There are other ways, but this is the most effective for the task. You need PostgreSQL 8.4 or later.

For a one-time use I would consider to just use a plain query (= function body without the RETURN QUERY above).

Solution 2

Assuming that you want to insert the function's return value and the rec.name into a different table. Here is what you can do (create the table new_tab first)-

SELECT levenshtein('mystring',lower(rec.Name)) AS L_val;
INSERT INTO new_tab (L_val, rec.name);

The usage above is demonstrated below.

I guess, you can use RAISE INFO 'This is %', rec.name; to view the values.

CREATE OR REPLACE FUNCTION randomnametest() RETURNS integer AS $$

DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM my_table LOOP
        SELECT levenshtein('mystring',lower(rec.Name))
         AS L_val;

       RAISE INFO '%   -   %', L_val, rec.name; 
    END LOOP;

    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Note- the FROM clause is optional in case you select from a function in a select like netxval(sequence_name) and don't have any actual table to select from i.e. like SELECT nextval(sequence_name) AS next_value;, in Oracle terms it would be SELECT sequence_name.nextval FROM dual; or SELECT function() FROM dual;. There is no dual in postgreSQL.

I also think that the ORDER BY is not necessary since my assumption would be that your function levenshtein() will most likely return only one value at any point of time, and hence wouldn't have enough data to ORDER.