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
I also tried to use
RETURNS SETOF schema.user
and directlyRETURN 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
.
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)
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.
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 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
.
Three distinct cases:
Single value:
Single row
Set of rows (= table)
There are no "table variables", but several other options:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With