postgresqlplpgsqlset-returning-functionssql-returning

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

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
Solution

  • 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 match what RETURN QUERY returns.

    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:

    I prepended function parameter names with underscore (_email) to rule out naming conflicts. This is totally optional, but you have to 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.

    Store results of query in a PL/pgSQL variable?

    Three distinct cases:

    1. Single value:
    1. Single row
    1. Set of rows (= table)

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