CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Joshua Robinson
Joshua Robinson

Reputation: 3563

Return default INSERT INTO message from function

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;

Upvotes: 1

Views: 63

Answers (1)

Patrick
Patrick

Reputation: 32199

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.

Upvotes: 2

Related Questions