Access and return result from INSERT INTO in PL/pgSQL function

Question

I am currently learning a lot of PostgreSQL, especially PLPGSQL and am struggling in handling query results in functions. I want to create a wrapper around a user table and use the result later on and then return it. In my case the user and account are two different tables and I want to create it in one go.

My first and naïve approach was to build the following:

CREATE OR REPLACE FUNCTION schema.create_user_with_login (IN email varchar, IN password varchar, IN firstname varchar DEFAULT NULL, IN surname varchar DEFAULT NULL)
    RETURNS schema.user
    LANGUAGE plpgsql
    VOLATILE 
    RETURNS NULL ON NULL INPUT
    AS 
$$
declare
  created_user schema."user";
begin

  INSERT INTO schema."user" ("firstname", "surname", "email")
    VALUES (firstname, surname, email)
    RETURNING * INTO created_user;

  // [...] create accounts and other data using e.g. created_user.id

  // the query should return the initially created user
  RETURN created_user
end;
$$;

This approach does not work, as schema.user has NOT NULL fields (a domain type with that constraint) and will throw an exception for the declared statement:

domain schema."USER_ID" does not allow null values

So maybe it could work, but not with in that constrained environment.

I also tried to use RETURNS SETOF schema.user and directly RETURN QUERY INSERT ...., but this does not return all columns, but instead one column with all the data.

How can I achieve the effect of returning the initial user object as a proper user row while having the data available inside the function?

I am using Postgres 9.6. My version output:

PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

Answer 1

Issue 1

I also tried to use RETURNS SETOF schema.user and directly RETURN QUERY INSERT ...., but this does not return all columns, but instead one column with all the data.

Sure it returns all columns. You have to call set-returning functions like this:

SELECT * FROM schema.create_user_with_login;

You have to declare it as RETURNS SETOF foo.users to cooperate with RETURN QUERY.

Issue 2

It's nonsense to declare your function as STRICT (synonym for RETURNS NULL ON NULL INPUT) and then declare NULL parameter default values:

... firstname varchar DEFAULT NULL, IN surname varchar DEFAULT NULL)

You cannot pass NULL values to a function defined STRICT, it would just return NULL and do nothing. While firstname and surname are meant to be optional, do not define the function strict (or pass empty strings instead or something)

More suggestions

Don't call your schema "schema".
Don't use the reserved word user as identifier at all. Use legal, lower-case, unquoted identifiers everywhere if possible.

Function

All things considered, your function might look like this:

CREATE OR REPLACE FUNCTION foo.create_user_with_login (_email text
                                                     , _password text
                                                     , _firstname text = NULL
                                                     , _surname text = NULL)

  RETURNS SETOF foo.users
  LANGUAGE plpgsql  AS  -- do *not* define it STRICT
$func$
BEGIN
   RETURN QUERY
   WITH u AS (
      INSERT INTO foo.users (firstname, surname, email)
      VALUES (_firstname, _surname, _email)
      RETURNING *
      )
    , a AS (       -- create account using created_user.id
      INSERT INTO accounts (user_id) 
      SELECT u.user_id FROM u
      )
      --  more chained CTEs with DML statements?
   TABLE u;        -- return the initially created user
END
$func$;

Yes, that's a single SQL statement with several data-modifying CTE to do it all. Fastest and cleanest. The function wrapper is optional for convenience. Might as well be LANGUAGE sql. Related:

• Insert data in 3 tables at a time using Postgres

I prepended function parameter names with underscore (_email) to rule out naming conventions. This is totally optional, but you have carefully keep track of the scope of conflicting parameters, variables, and column names if you don't.

TABLE u is short for SELECT * FROM u.

• Is there a shortcut for SELECT * FROM?

Store results of query in a plpgsql variable?

Three distinct cases:

1. Single value:

   • Store query result in a variable using in PL/pgSQL

2. Single row

   • Declare row type variable in PL/pgSQL

3. Set of rows (= table)

There are no "table variables", but several other options:

• PostgreSQL table variable
• How to use a record type variable in plpgsql?