Accessing and Returning Result from INSERT INTO in PL / pgSQL Function
I am currently learning a lot of PostgreSQL, especially PLPGSQL
and struggling with handling query results in functions. I want to create a wrapper around the user table and use the result later and then return it. In my case, user and account are two different tables and I want to create them in one go.
My first and naive approach was this:
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 doesn't work because it schema.user
has fields NOT NULL
(domain type with this constraint) and throws an exception for the declared statement:
domain schema."USER_ID" does not allow null values
It might work, but not in this limited 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 data.
How can I achieve the effect of returning the original user object as the correct user string, while having the data available inside the function?
I am using Postgres 9.6. Output of my version:
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
source to share
There are several problems.
1.
I also tried using
RETURNS SETOF schema.user
and directlyRETURN QUERY INSERT
.... but this does not return all columns, but instead one column with all data.
It returns all columns of course. You should call set-return functions like this:
SELECT * FROM schema.create_user_with_login;
You must declare it as RETURNS SETOF foo.users
for cooperation with RETURN QUERY
.
2.
It's absurd to declare your function as STRICT
(synonym for RETURNS NULL ON NULL INPUT
) and then declare NULL parameter values by default:
... firstname varchar DEFAULT NULL, IN surname varchar DEFAULT NULL)
You cannot pass NULL values into a function defined STRICT
, it will just return NULL and do nothing. While firstname
they surname
should be optional, don't define the strict function (or pass empty strings or whatever)
Additional suggestions
Don't call your schema "schema"
.
Don't use the reserved word user
as an identifier at all. Use legal, lowercase, unquoted identifiers whenever 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 AS
$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$ LANGUAGE plpgsql; -- do *not* define it STRICT
Yes, it's one SQL statement with multiple data modifying CTEs to do it all. Fastest and cleanest. The functional wrapper is optional for convenience. Connected:
I've added underscore ( _email
) function parameter names to avoid naming conventions. This is completely optional, but you need to know the range of parameters, variables, and columns if you don't.
TABLE u
not suitable for SELECT * FROM u
.
Store query results in plpgsql variable?
Three different cases:
-
Single meaning:
-
Single line
-
Rowset (= table)
There are no "table variables", but there are several other parameters:
source to share