Return default INSERT INTO message from function

Question

Looking to have the default INSERT INTO message where the count of records inserted is printed out. Know this has something to do with changing RETURNS from void to something else and possibly adding an OUT argument?

CREATE OR REPLACE FUNCTION etl(from_table regclass, to_table regclass) RETURNS void AS
$$
  BEGIN

  EXECUTE 'INSERT INTO ' || to_table || ' ('
      'title'
    ') '
    'SELECT '
      'data->>''title'' as title'
    'FROM ' || from_table || ' '
    USING from_table, to_table;

END
$$ LANGUAGE plpgsql;

Print out the SQL before executing it. The error will be obvious. Hint: think comma. — Gordon Linoff
– Gordon Linoff, Commented Mar 6, 2017 at 13:25
returning will return the actual values, not the count of rows affected — Vao Tsun
– Vao Tsun, Commented Mar 6, 2017 at 13:27
@GordonLinoff Thanks, deleted a bunch of select statements to simplify the example and created a buggy statement, fixed now. Good eyes! — Joshua Robinson
– Joshua Robinson, Commented Mar 6, 2017 at 13:28

Patrick · Accepted Answer · 2017-03-06 13:36:46Z

2

Use the GET DIAGNOSTICS command to populate, then return a variable:

CREATE OR REPLACE FUNCTION etl(from_table regclass, to_table regclass) RETURNS integer AS
$$
DECLARE
    rows integer;
BEGIN
    EXECUTE format('INSERT INTO %I (title) 
                        SELECT data->>''title'' as title
                        FROM %I', to_table, from_table);
    GET DIAGNOSTICS rows = ROW_COUNT;
    RETURN rows;
END;
$$ LANGUAGE plpgsql;

You should also really use the format() function to assemble your dynamic SQL command. Also, you can write literal strings over multiple lines without having to use ending/opening quotes at every line.

edited Mar 6, 2017 at 13:36

answered Mar 6, 2017 at 13:32

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Return default INSERT INTO message from function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related