Passing an Object (User Defined Type) to a Function in PostgreSQL

Question

Given the following example database, how do I invoke the function called account_upsert with an object of core.account_type? I'm currently getting 42P01: relation account doesn't exist. account in this context is the parameter of the function, not the table of the same name. I've done similar examples with passing an array of types, but for this I'm invoking it for just a single object.

create schema if not exists core;

create table if not exists core.account
(
    account_id         uuid                                 default gen_random_uuid(),
    user_name          character varying(50)       not null,
    email              character varying(256)      not null,
    is_active          boolean                     not null default true,
    create_date        timestamp(0) with time zone not null default now(),
    last_modified_date timestamp(0) with time zone,
    primary key (account_id)
);

create type core.account_type as
(
    account_id uuid,
    user_name  character varying(50),
    email      character varying(256),
    is_active  boolean
);

create or replace function core.account_upsert(
    in input core.account_type)
    returns table
            (
                account_id         uuid,
                user_name          character varying,
                email              character varying,
                is_active          boolean,
                create_date        timestamp with time zone,
                last_modified_date timestamp with time zone
            )
    language 'plpgsql'
    cost 100
    volatile
    rows 1000
as
$BODY$
declare
    account core.account_type := input;
begin
    return query
        insert into
            core.account (account_id,
                          user_name,
                          email,
                          is_active,
                          create_date,
                          last_modified_date)
            select a.account_id,
                   a.user_name,
                   a.email,
                   a.is_active,
                   current_timestamp(0),
                   current_timestamp(0)
            from account as a
            on conflict (account_id)
                do update set
                    user_name = coalesce(a.user_name, excluded.user_name),
                    email = coalesce(a.email, excluded.email),
                    is_active = coalesce(a.is_active, excluded.is_active),
                    last_modified_date = current_timestamp(0)
            returning
                account_id,
                user_name,
                email,
                is_active,
                create_date,
                last_modified_date;
end
$BODY$;

A couple of calls I've tried.

select * from core.account_upsert(cast(row(null, 'test.user', 'test.user@yahoo.com', true) as core.account_type));
select * from core.account_upsert((null, 'test.user', 'test.user@yahoo.com', true));

JGx714791 · Answer 1 · 2022-06-16T01:36:23.513

It was an issue with the syntax I was using. I was basing the from part off of the example where I was passing an array of types to the function, where I would call unnest on a supplied array. Since this was a single object, I just needed to reference the parameter directly.

There were a few other issues, namely the naming of account_id in the return type was conflicting with the table definition in the return statement of the function. I'm not sure how to resolve that better so I eliminated the underscore just to move forward.

create or replace function core.account_upsert(input core.account_type)
    returns table
            (
                accountid          uuid,
                user_name          character varying,
                email              character varying,
                is_active          boolean,
                create_date        timestamp with time zone,
                last_modified_date timestamp with time zone
            )
    language 'plpgsql'
    cost 100
    volatile
    rows 1000
as
$BODY$
begin

    raise notice 'Value From Input: %', input.account_id;

        return query
        insert into core.account as a (account_id, user_name, email, is_active, create_date, last_modified_date)
            values (coalesce(input.account_id, gen_random_uuid()), input.user_name, input.email, input.is_active, current_timestamp(0),
                    current_timestamp(0))
            on conflict (account_id)
                do update set
                    user_name = coalesce(input.user_name, excluded.user_name),
                    email = coalesce(input.email, excluded.email),
                    is_active = coalesce(input.is_active, excluded.is_active),
                    last_modified_date = current_timestamp(0)
            returning
                a.account_id as accountid,
                a.user_name,
                a.email,
                a.is_active,
                a.create_date,
                a.last_modified_date;
end
$BODY$;

Passing an Object (User Defined Type) to a Function in PostgreSQL

1 Answers1