CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Sam
Sam

Reputation: 1232

Return INSERT output from a function

When an INSERT is performed its output is a command tag, e.g. INSERT 0 1.

I'd like to capture that same command tag from inside a function and return it.

I thought the RETURNING clause would help me, nevertheless it works similar to a SELECT query over the inserted rows, returning a table-like set. I do not want that. What I want is to return the plain command tag (i.e., the text "INSERT 0 1") that you get when performing an INSERT.

Here is a pseudo-code example of what I want to achieve (of course, RETURN QUERY does not work in this case since INSERT is a in a non-SETOF function

CREATE FUNCTION insert_user_test (
    p_username text)
    RETURNS text
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY INSERT INTO users(username) values(p_username));
END $$;

How do I achieve what I need?

Upvotes: 0

Views: 1676

Answers (3)

Pavel Stehule
Pavel Stehule

Reputation: 45795

Command tag "INSERT 0 1" is not directly available for plpgsql environment. You can get same information in different format via GET DIAGNOSTICS command.

DO $$
  DECLARE _oid oid; _rc bigint;
BEGIN
  INSERT INTO foo VALUES(10);
  GET DIAGNOSTICS _oid = RESULT_OID; -- it is not supported on 12+
  GET DIAGNOSTICS _rc = ROW_COUNT;
  RAISE NOTICE 'INSERT % %', _oid, _rc;
END;
$$;

If you need more, then you should to use different language than PLpgSQL - special variant is C language, where all informations are available.

Upvotes: 2

user330315
user330315

Reputation:

You can not capture the message INSERT 0 1 literally because that's not returned by the INSERT statement, but generated by the psql command line tool based on the status of the INSERT statement.

So if you want to see that message, you need to generate that yourself in the function (PL/pgSQL is not needed for this):

CREATE FUNCTION insert_user_test (p_username text)
    RETURNS text
    LANGUAGE SQL
  AS $$
    with inserted as (
      INSERT INTO users(username) values(p_username))
      returning *
    )
    select concat('INSERT 0 ', count(*))
    from inserted;
  $$;

If you didn't mean to see the message INSERT 0 1 then you can just return the username instead:

CREATE FUNCTION insert_user_test (p_username text)
    RETURNS text
    LANGUAGE SQL
  AS $$
      INSERT INTO users(username) values(p_username))
      returning username;
  $$;

Or the complete row including default values that was inserted:

CREATE FUNCTION insert_user_test (p_username text)
    RETURNS setof users
    LANGUAGE SQL
  AS $$
      INSERT INTO users(username) values(p_username))
      returning *;
  $$;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use a CTE with the INSERT:

WITH i as (
      INSERT INTO users (username)
          VALUES (p_username)
      RETURNING *
     )
SELECT i.*
FROM i;

Upvotes: 2

Related Questions