CODEWITHSUNDEEP
postgresqlfor-loopplpgsql
Richard Hum
Richard Hum

Reputation: 651

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

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.

Upvotes: 3

Views: 18091

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658392

If you want the output from a PL/pgSQL function like the title says:

CREATE OR REPLACE FUNCTION randomnametest(_mystring text)
  RETURNS TABLE (l_dist int, name text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT levenshtein(_mystring, lower(t.name)), t.name
   FROM   my_table t
   ORDER  BY 1;
END
$func$;
  • 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.
  • To make this more useful I made the comparison string a parameter to the function.

There are other ways, but this is simplest for the task - while you can't simplify to a plain SQL function.

For one-time use, consider the nested query without any function wrapper.

Upvotes: 5

Anjan Biswas
Anjan Biswas

Reputation: 7932

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.

Upvotes: 6

Related Questions